Merge two spreadsheets (files) based on ID

[simplicityq88]

Hello there Excel fans and lovers.

I'm a long time excel user and I've come up with an issue, it can surely be solved in any other larger database language but excel is just my everyday friend and I was wondering if it's possible to do such a thing.

I've been googeling for a while and no good results.

This is the problem:

I have a list of "people" in my 1st ssheet and they have

ID | ProductName| Category | Year |

and I have this data on the 2nd ssheet

ID | Price | Description

---------------------------------------------------------

There is 600.000 records in the primary sheet but only 22.000 in the secondary because the data doesn't repeat (one description can fit to 10.000 products, no need to write it down that much times).

But now I need to compile one big/large file, so I need to have one file that contains all:

ID | ProductName| Category | Year | + | Price | Description


In MS SQL I would use the "Where" function and compare the ID here and there, but is there a possible way to do it in excel?

Thank you in advance!

 

[crook_101]

Hi,

A vlookup would do the job....assuming your layouts are pretty much the same as below, tweak the formulas to cover your range then drag them down to copy them:

Excel Workbook
	A	B	C	D	E	F
1	ID	Product Name	Cat	Year	Price	Year
2	1	Car	Blah	1999	$20	Red
3	1	Car	Blah	1999	$20	Red
4	1	Car	Blah	1999	$20	Red
5	2	Bus	Blah	1999	$40	Blue
6	2	Bus	Blah	1999	$40	Blue
7	2	Bus	Blah	1999	$40	Blue
Sheet1

And second file with the 22K entries

Excel Workbook
	A	B	C
1	ID	Price	Description
2	1	$20	Red
3	2	$40	Blue
Sheet1

HTH
Ian

 

[simplicityq88]

Ian, this is awesome, I believe it did the job.

Thank you very much for a "uber"fast response.



Kind regards,
J.

 

[aviavy]

Hi,

I am also trying to do something very simlar, and I believe what you suggested should work for me, but I was wondering if it could be elaborated a bit more in a 'excel for dummies' format.

Thanks

 

[simplicityq88]

Hi Aviavy,

Let me try to explain.

Vlookup goes through all rows in a given array and searches for your criteria.
In this case "A2" is the criteria.
Then after the comma goes the array where to look up.
After the array you specify what column are you adding (id) to the field where the formula is.

And at the end 0 or "false" to specify that is has to be an exact match.

Hope it's clear. If you test it around a little bit you'll have it working fine in any scenario.

 

[aviavy]

Hi Aviavy,

Let me try to explain.

Vlookup goes through all rows in a given array and searches for your criteria.
In this case "A2" is the criteria.
Then after the comma goes the array where to look up.
After the array you specify what column are you adding (id) to the field where the formula is.

And at the end 0 or "false" to specify that is has to be an exact match.

Hope it's clear. If you test it around a little bit you'll have it working fine in any scenario.

Pure awesomeness! I totally get it now! Played around with it like you suggested and everything totally works!
Thank you so much!

 

[aviavy]

Everything is still working fine, but I need to extend on this a bit now.

As it is right now, if the values match, then it copies the data from the second table. If it doesn't, it gives a value of #N/A. This alright with text columns, is creating havoc for a numerical column with the numbers that I need to sum.

Is there a way to either change VLOOKUP so that if there isn't a value it's blank or show 0?

Or is there a formula that I can use that can sum all numbers in a column filled with numbers and text?