Matching multi-arrays

[Skippy]

I believe this is possible but am not sure how to go about doing it. I have one worksheet with 2000 rows and 7 columns. On another ws I have 40000 rows and 25 columns. In this 2nd ws, there are 6 columns that match the first 6 columns in the first ws and they are in the same order (but in different col locations). What I would like to do is go through the 2nd ws and if the row values in the 6 matching columns are the same as the 1st ws, then add the value of the 7th column from the first ws into the last column of the 2nd ws (on the same row). If this is not clear, please let me know.

 

[just_jon]

Create temporary columns in each spreadsheet in which you concatenate - WITH separaters - the 6 fields as

=Field1&char(1)&Field2&Char(1) ...

Then you can do a straight INDEX/MATCH, convert the results to values, delete the temporary columns and then filter out all the no matches.

 

[Skippy]

Thanks just-jon,

I was thinking of using VBA but maybe your approach is better. But I don't quite follow what you are suggesting. Let me provide you with an example of how my wb is set up.

Each row in the first ws is unique. In the 2nd ws, there are multiple rows that match a single row in the 1st worksheet.

a) Let's say the the first 2 rows in the 1st ws are as follows"

A1 = Animal
A2 = dog
B1 = Color
B2 = red
etc
etc
etc
F1= Temperment
F2 = friendly
G1 = Name
G2 = Rover

b) The 2nd ws has

G1 = Animal
G23 = dog
M1 = Color
M23 = red
etc
etc
etc
Y1 = Temperment
Y23 = friendly

So row 23 in the second ws matches row 1 in the 1st ws. Therefore I would like to add "Rover" to the last column on row 23. Then continue down the 2nd ws and look for another match with row 1 of the 1st ws. Etc, etc. From this can you explain what you are suggesting?

Thanks

 

[just_jon]

Book1.xls
	A	B	C	D	E
1	Animal	Color	Temperment	Name	TempCol
2	dog	red	friendly	Rover	dogredfriendly
3
4
Sheet2

Book1.xls
	A	B	C	D	E	F
1		Animal	Color	Temperment	name	TempCol
2		blah1	blah2	blah3	blah4	blah1blah2blah3
3		blah1	blah2	blah3	blah4	blah1blah2blah3
4		dog	red	friendly	Rover	dogredfriendly
5
Sheet3

 

[Skippy]

Thanks just_jon... now I see what you are suggesting. But I think it's backwards. I should be adding "Rover" to Sheet3 and using Sheet2 to set the index/match criteria.

Cheers

 

[tusharm]

I'm not sure how to interpret this comment. Is it just a casual statement of how you will implement the solution from JJ or that you want him to give you a revised solution with the references exchanged?

Thanks just_jon... now I see what you are suggesting. But I think it's backwards. I should be adding "Rover" to Sheet3 and using Sheet2 to set the index/match criteria.

Cheers

 

[Skippy]

I'm not sure how to interpret this comment. Is it just a casual statement of how you will implement the solution from JJ or that you want him to give you a revised solution with the references exchanged?

Sorry my reply wasn't clear (and this one's late). just_jon's solution was perfect. I was only pointing out how I was going to modify it. No need for further assistance on this one. Thanks a lot.