Hello everyone! I have the following table (1). KEYPERCCODEREFERENCEPOI89371150FIJ001POI89372150FIJ002POI89371203FIJ003POI89372203FIJ004 Table 1 I have to fill fields REF1 and REF2 in order to table 2 become table 3. KEYPERCREF1REF2POI89371 POI89372 Table 2 KEYPERCREF1REF2POI89371FIJ001FIJ003POI89372FIJ002FIJ004 Table 3 I've tried different approaches, but I still can't get how to do it. Any ideas? Thanks in advance!

Table filling

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!

Datasets

Tips and Tricks

Accepted answers

cjaneczko

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

RobertOdera

Hi, @jpscoralick

Kindly consider the below.

I hope you find it helpful - cheers!

For_jpscoralik.yxzp

DanielG

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. 😁

TableFill_SortAdded.yxmd

All comments

cjaneczko

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

RobertOdera

Hi, @jpscoralick

Kindly consider the below.

I hope you find it helpful - cheers!

For_jpscoralik.yxzp

DanielG

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

TableFill.yxmd

DanielG

TableFill_SortAdded.yxmd

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?

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335