Matching multi-arrays

Skippy

Board Regular
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.

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

just_jon

Legend
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

Board Regular
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"
Code:
``````A1 = Animal
A2 = dog
B1 = Color
B2 = red
etc
etc
etc
F1= Temperment
F2 = friendly
G1 = Name
G2 = Rover``````
b) The 2nd ws has
Code:
``````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

Skippy

Board Regular
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

MrExcel MVP
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?

Skippy said:
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

Board Regular
tusharm said:
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.

Replies
1
Views
614
Replies
4
Views
297
Replies
8
Views
406
Replies
16
Views
242
Replies
2
Views
114

1,195,619
Messages
6,010,736
Members
441,567
Latest member
Flitbee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back