Excel:Need to compare serial numbers in different columns
- StarPhoenix
- B.Soc.Sci, M.SocSci [UPCF]
- Posts: 17634
- Joined: 11 Dec 2003, 02:00
- Processor: Core i5 3470
- Motherboard: Gigabyte G1 Sniper Z77
- Graphics card: nVidia GeForce GTX 1060
- Memory: 8Gb DDR3 1600
- Location: East London
- Contact:
Excel:Need to compare serial numbers in different columns
Hi
I have two spreadsheets. One has a master list of asset numbers while the other has hardware audit information that includes asset numbers.
What would be the clever way of comparing the asset numbers in the first spreadsheet to those in the second
and inserting rows in the second spreadsheet for those that appear in the first but not the second.
I could probably do this all manually, and it would give me something to do today [and probably Monday as well],
but I would like to know how to get Excel to do it automagically in case I am in a hurry one day.
I am at a loss as to how to do something this complicated.
How would you do it?
Thank you
Maybe I should print out the lists and try to match these things on paper using highlighters.
Edit: Problem solved by one of my colleagues, who had it all sorted out in a matter of minutes.
I should outsource my work more regularly. :-p
I have two spreadsheets. One has a master list of asset numbers while the other has hardware audit information that includes asset numbers.
What would be the clever way of comparing the asset numbers in the first spreadsheet to those in the second
and inserting rows in the second spreadsheet for those that appear in the first but not the second.
I could probably do this all manually, and it would give me something to do today [and probably Monday as well],
but I would like to know how to get Excel to do it automagically in case I am in a hurry one day.
I am at a loss as to how to do something this complicated.
How would you do it?
Thank you
Maybe I should print out the lists and try to match these things on paper using highlighters.
Edit: Problem solved by one of my colleagues, who had it all sorted out in a matter of minutes.
I should outsource my work more regularly. :-p
"Humankind cannot bear very much reality." T.S. Elliot
-
- Forum Administrator
- Posts: 22136
- Joined: 14 Jun 2004, 02:00
- Processor: Ryzen 1700K
- Motherboard: Asus X370
- Graphics card: Asus 1060 Strix
- Memory: 16GB RAM
- Location: Where Google says
Re: Excel:Need to compare serial numbers in different column
Well dont leave us hanging how did your colleague do it? Someone else might find this interesting...
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
- StarPhoenix
- B.Soc.Sci, M.SocSci [UPCF]
- Posts: 17634
- Joined: 11 Dec 2003, 02:00
- Processor: Core i5 3470
- Motherboard: Gigabyte G1 Sniper Z77
- Graphics card: nVidia GeForce GTX 1060
- Memory: 8Gb DDR3 1600
- Location: East London
- Contact:
Re: Excel:Need to compare serial numbers in different column
She copied and pasted the relevant columns into a new worksheet and used "Ctrl+F" repeatedly
while attempting to explain to me what she was doing. It took some time to sink in...
Then she tidied it up and emailed it to me.
I see myself being nominated for....
while attempting to explain to me what she was doing. It took some time to sink in...
Then she tidied it up and emailed it to me.
I see myself being nominated for....
Last edited by StarPhoenix on 18 Feb 2011, 20:42, edited 1 time in total.
"Humankind cannot bear very much reality." T.S. Elliot
-
- Forum Administrator
- Posts: 22136
- Joined: 14 Jun 2004, 02:00
- Processor: Ryzen 1700K
- Motherboard: Asus X370
- Graphics card: Asus 1060 Strix
- Memory: 16GB RAM
- Location: Where Google says
Re: Excel:Need to compare serial numbers in different column
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
-
- Registered User
- Posts: 14338
- Joined: 04 Sep 2004, 02:00
- Processor: i386DX Sooper
- Motherboard: A blue one
- Graphics card: A red one
- Memory: Hard drive
- Location: On a Möbius strip
- Contact:
Re: Excel:Need to compare serial numbers in different column
Some knowledge of SAS would also have made this very easy. Just my 2c to add on to this post.
EDIT: I could actually double check your results if you send me the excel files.
EDIT: I could actually double check your results if you send me the excel files.
If I weren't insane: I couldn't be so brilliant! - The Joker
- StarPhoenix
- B.Soc.Sci, M.SocSci [UPCF]
- Posts: 17634
- Joined: 11 Dec 2003, 02:00
- Processor: Core i5 3470
- Motherboard: Gigabyte G1 Sniper Z77
- Graphics card: nVidia GeForce GTX 1060
- Memory: 8Gb DDR3 1600
- Location: East London
- Contact:
Re: Excel:Need to compare serial numbers in different column
What is SAS?
"Humankind cannot bear very much reality." T.S. Elliot
- KatrynKat
- Insane in the Membrane
- Posts: 24490
- Joined: 18 Jul 2010, 17:42
- Location: In my BDSM dungeon - aka Lockdown
Re: Excel:Need to compare serial numbers in different column
"This eBook is displayed using 100% recycled electrons."
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: Excel:Need to compare serial numbers in different column
You could've used the 'MATCH' formula.
Remember it is an array formula and must be entered by pressing CTRL-SHIFT-ENTER.
Remember it is an array formula and must be entered by pressing CTRL-SHIFT-ENTER.
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
-
- Registered User
- Posts: 14338
- Joined: 04 Sep 2004, 02:00
- Processor: i386DX Sooper
- Motherboard: A blue one
- Graphics card: A red one
- Memory: Hard drive
- Location: On a Möbius strip
- Contact:
Re: Excel:Need to compare serial numbers in different column
On campus you use some enterprise version which is basically point and click, according to the math stats students.
Where I work we code SAS by hand.
If I weren't insane: I couldn't be so brilliant! - The Joker
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Excel:Need to compare serial numbers in different column
Vloopup would have been the easiest to get the two sets of data together - an then you use the Exact() function to compare the two sets of vaules. Where you get a false you know they are different.
- KatrynKat
- Insane in the Membrane
- Posts: 24490
- Joined: 18 Jul 2010, 17:42
- Location: In my BDSM dungeon - aka Lockdown
Re: Excel:Need to compare serial numbers in different column
@Blade: i never learnt how to use the SAS Enterprise, i learnt by typing out the codes and programs...
"This eBook is displayed using 100% recycled electrons."
-
- Registered User
- Posts: 14338
- Joined: 04 Sep 2004, 02:00
- Processor: i386DX Sooper
- Motherboard: A blue one
- Graphics card: A red one
- Memory: Hard drive
- Location: On a Möbius strip
- Contact:
Re: Excel:Need to compare serial numbers in different column
Well that's good; SAS programmers are few and far between.KatrynKat wrote:@Blade: i never learnt how to use the SAS Enterprise, i learnt by typing out the codes and programs...
Just out of interest, can you give me a scenario where you had to use SAS?
If I weren't insane: I couldn't be so brilliant! - The Joker