Hello everyone!
I have the following table (1).
KEY | PERC | CODE | REFERENCE |
POI8937 | 1 | 150 | FIJ001 |
POI8937 | 2 | 150 | FIJ002 |
POI8937 | 1 | 203 | FIJ003 |
POI8937 | 2 | 203 | FIJ004 |
Table 1
I have to fill fields REF1 and REF2 in order to table 2 become table 3.
KEY | PERC | REF1 | REF2 |
POI8937 | 1 | ||
POI8937 | 2 |
Table 2
KEY | PERC | REF1 | REF2 |
POI8937 | 1 | FIJ001 | FIJ003 |
POI8937 | 2 | FIJ002 | FIJ004 |
Table 3
I've tried different approaches, but I still can't get how to do it.
Any ideas?
Thanks in advance!
Solved! Go to Solution.
Cross tab gets the data in the format of Table 3. Do you have more than 2 Codes?
@jpscoralick see attached. This uses the cross-tab mentioned above, but also dynamically creates column names Ref1,Ref2, etc.
Thanks a lot @cjaneczko, @RobertOdera and @DanielG for the quick and useful answers!
If you all could, please, help me again...
Actually, table 1 and table 2 are two different Excel files. So, I need to fill table 2 with data from table 1, generating the results showed on table 3.
I understood that CrossTab it's the key of my solution, but how can I do it with dealing with data from 2 different Excel files?
Sure thing, @jpscoralick
If your files have the same schema:
1. Bring them in using the Input Tool(s)
2. Stack them (append) using the Union Tool
3. the rest should flow per @DanielG solution
If your files have different schemas, kindly provide a sample(s) so that we can offer treatments for you to consider - Cheers!
Btw, I'm signing off until tomorrow, but I'm sure you will get help before then 😎
Thanks a lot @RobertOdera!
Really appreciate your kind and useful attention. It solved my needs.
Best regards
You're most welcome @jpscoralick!