If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
compare 2 columns, enter data from other column
I have been struggling with a formula - help!
I want to copy data from one sheet to another but only if there is an exact match of data. I need help with the structure of the formula - I can massage it later to fit the specifics. First, I need to find any exact match of the text in D1 of Doc1 to any row in column E of Doc2. If there is a match, then: from that match's row in Doc2, I want to enter the data from column A into column B of L1's row in Doc1. If there is no match, nothing happens. Can anyone help? Spent hours trying to set this up. I need the formula -- or if someone knows how to write a macro to do this, that would work too. Doc1 A B C D E F 1 AB 2 CD 3 ED 4 GH 5 IJ 6 KL Doc2 A B C D E 1 4 NO 2 6 ST 3 -4 ED 4 5 MO 5 5 IJ 6 -5 AB 7 2 CD 8 5 KL Doc1 A B C D 1 -5 AB 2 2 CD 3 -4 ED 4 GH 5 5 IJ 6 5 KL |
#2
|
|||
|
|||
compare 2 columns, enter data from other column
On Apr 19, 5:57*am, cap1816 wrote:
I have been struggling with a formula - help! I want to copy data from one sheet to another but only if there is an exact match of data. I need help with the structure of the formula - I can massage it later to fit the specifics. First, I need to find any exact match of the text in D1 of Doc1 to any row in column E of Doc2. If there is a match, then: from that match's row in Doc2, I want to enter the data from column A into column B of L1's row in Doc1. If there is no match, nothing happens. Can anyone help? Spent hours trying to set this up. I need the formula -- or if someone knows how to write a macro to do this, that would work too. Doc1 * * * * * * * * * * * * * * * * * * * * * * * * * * A * * * B * * * C * * * D * * * E * * * F 1 * * * * * * * * * * * * * * * AB * * * * * * * 2 * * * * * * * * * * * * * * * CD * * * * * * * 3 * * * * * * * * * * * * * * * ED * * * * * * * 4 * * * * * * * * * * * * * * * GH * * * * * * * 5 * * * * * * * * * * * * * * * IJ * * * * * * * 6 * * * * * * * * * * * * * * * KL * * * * * * * Doc2 * * * * * * * * * * * * * * * * * * * * * * * * * * A * * * B * * * C * * * D * * * E * * * 1 * * * 4 * * * * * * * * * * * * * * * NO * * * 2 * * * 6 * * * * * * * * * * * * * * * ST * * * 3 * * * -4 * * * * * * * * * * * * * * *ED * * * 4 * * * 5 * * * * * * * * * * * * * * * MO * * * 5 * * * 5 * * * * * * * * * * * * * * * IJ * * * 6 * * * -5 * * * * * * * * * * * * * * *AB * * * 7 * * * 2 * * * * * * * * * * * * * * * CD * * * 8 * * * 5 * * * * * * * * * * * * * * * KL * * * Doc1 * * * * * * * * * * * * * * * * * * * * * * * * * * A * * * B * * * C * * * D * * * * * * * 1 * * * * * * * -5 * * * * * * *AB * * * * * * * 2 * * * * * * * 2 * * * * * * * CD * * * * * * * 3 * * * * * * * -4 * * * * * * *ED * * * * * * * 4 * * * * * * * * * * * * * * * GH * * * * * * * 5 * * * * * * * 5 * * * * * * * IJ * * * * * * * 6 * * * * * * * 5 * * * * * * * KL * * * * * * * something like "=INDEX(A8:E15,MATCH(D1,E8:E15,0),1)" where doc1 is A16 doc2 is A8:E15 |
#3
|
|||
|
|||
compare 2 columns, enter data from other column
How about this in column B of Doc1?
=IF(ISNA(INDEX(Doc2!A$1:A$8,MATCH(D1,Doc2!E$1:E$8, 0),1)),"",INDEX(Doc2!A$1:A$8,MATCH(D1,Doc2!E$1:E$8 ,0),1)) Naturally, change the $8 values to the last row used on Doc2 sheet. The IF(ISNA()) portion suppresses the #NA that would appear for GH on Doc1. The catch here is that MATCH() is not case sensitive, so AB=ab=AB=Ab=Ab. You could also use LOOKUP() but the entries on Doc2 sheet, column E would have to be in ascending order to work properly. "cap1816" wrote: I have been struggling with a formula - help! I want to copy data from one sheet to another but only if there is an exact match of data. I need help with the structure of the formula - I can massage it later to fit the specifics. First, I need to find any exact match of the text in D1 of Doc1 to any row in column E of Doc2. If there is a match, then: from that match's row in Doc2, I want to enter the data from column A into column B of L1's row in Doc1. If there is no match, nothing happens. Can anyone help? Spent hours trying to set this up. I need the formula -- or if someone knows how to write a macro to do this, that would work too. Doc1 A B C D E F 1 AB 2 CD 3 ED 4 GH 5 IJ 6 KL Doc2 A B C D E 1 4 NO 2 6 ST 3 -4 ED 4 5 MO 5 5 IJ 6 -5 AB 7 2 CD 8 5 KL Doc1 A B C D 1 -5 AB 2 2 CD 3 -4 ED 4 GH 5 5 IJ 6 5 KL |
Thread Tools | |
Display Modes | |
|
|