Log In
Register
Home
Active Member
September 11, 2018
Offline
| A | B | C | D | E | F | G | H | I | J |
| Primary Database | Secondary Database | From "E" | |||||||
| A | B | C | D | abcd | M | N | O | P | |
| E | F | G | H | efgh | E | F | G | H | |
| I | J | K | L | ijkl | U | V | W | X | |
| M | N | O | P | mnop | A | B | C | D | |
| Q | R | S | T | qrst | Q | R | S | T | |
| U | V | W | X | uvwx | I | J | K | L | |
| Problem: Extracting information from one column of a database based on matching several other columns from another. | |||||||||
| Each database could contain hundreds or thousands of entries. I have included a test mock-up of both in one file as will be done in practice. | |||||||||
| All of the information in the secondary database came from the larger primary one and now Col. J needs to be updated from Col. E. | |||||||||
| The rows of the two databases are sorted differently, but not the columns. | |||||||||
| The need is to match each row in the secondary database to its parent in the primary one and copy the entry in column E into column J | |||||||||
| I have been trying to make "IF, INDEX + MATCH" work but can't find the way to do it. | |||||||||
See Answer
VIP
Trusted Members
June 25, 2016
Offline
It would be best if you can upload your file.
Answers Post
VIP
Trusted Members
June 25, 2016
Offline
Hi Donald
I would create a helper column in the Primary database and then do an INDEX/MATCH from the Secondary database.
Please refer example attached.
Hope this helps.
Sunny
Active Member
September 11, 2018
Offline
Hi Sunny,
#1 - The actual data is privileged and I should not make it public.
#2 - You may be on to something here. I could concatenate columns A-D and F-I to produce a single column on each side for comparison. The only variable left to explore would be which rows match. I'll pursue this strategy to see if I can figure it out.
Thanks for your comments!
Donald
Active Member
September 11, 2018
Offline
I'm getting closer!
Based on a suggestion, I concatenated columns A-D and G-J to produce a single column on each side for comparison, labeled as E and K.
| Row | A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
| Primary Database | Secondary Database | From "F" | |||||||||||||
| 20 | A | B | C | D | ABCD | abcd | M | N | O | P | MNOP | 4 | 23 | mnop | |
| 21 | E | F | G | H | EFGH | efgh | E | F | G | H | EFGH | 2 | 21 | ||
| 22 | I | J | K | L | IJKL | ijkl | U | V | W | X | UVWX | 6 | 25 | ||
| 23 | M | N | O | P | MNOP | mnop | A | B | C | D | ABCD | 1 | 20 | ||
| 24 | Q | R | S | T | QRST | qrst | Q | R | S | T | QRST | 5 | 24 | ||
| 25 | U | V | W | X | UVWX | uvwx | I | J | K | L | IJKL | 3 | 22 | ||
In column L, row 20 I entered =(MATCH(K20,$E$20:$E$25,0)) which correctly identified the only match in column E as being the 4th entry, shown in column L. From this, I calculated the actual row number as being 23 (column M). (All of the other rows were correctly matched, too.)
Here's where I ran into trouble. Manually entering =E23 in column N, row 20 placed the correct value (mnop) in that cell. Any other way I tried to do the same thing computationally rather than manually either yielded an error code or =E23 itself that would not retrieve mnop. This included various attempts to use CONCATENATE and CELL. I suspect there is a format problem (text vs. something else) that I have not uncovered.
How do I computationally perform the manual equivalent of placing =E23 in N20?
VIP
Trusted Members
June 25, 2016
Offline
Hi Donald
In cell N20 your formula should be =INDEX($F$20:$F$25,MATCH(K20,$E$20:$E$25,0))
You can refer here for more details on INDEX/MATCH
https://www.myonlinetraininghu.....-functions
It is always best to attach a sample file (with test data of course) as it will be easier to see actually what is wrong, especially when dealing with cell formats/extra space etc.
Sunny
1 Guest(s)
