Table filling

[jpscoralick]

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!

[cjaneczko · Accepted solution]

Cross tab gets the data in the format of Table 3. Do you have more than 2 Codes?

[RobertOdera · Accepted solution]

Hi, @jpscoralick 

Kindly consider the below.

I hope you find it helpful - cheers!

[DanielG]

@jpscoralick  see attached. This uses the cross-tab mentioned above, but also dynamically creates column names Ref1,Ref2, etc.  

[DanielG · Accepted solution]

Best practice to sort data before the multi-row tool as well.  I forgot it the first time.  Added it here with all columns sorted since it is just a four-row set.  Configure as needed for your full real data.  😁

[jpscoralick]

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?

[RobertOdera]

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 😎

[jpscoralick]

Thanks a lot @RobertOdera!
Really appreciate your kind and useful attention. It solved my needs.
Best regards

[RobertOdera]

You're most welcome @jpscoralick!