Join multiple

[AnGi]

Hi guys

i have this situation

First file with a list of 50 names:

Name

Paul

John

Mark

Steve

Robert

Phil

Andrew

….

Second file a table with 200 column

Day 1	Day 2	Day 3	Day 4	Day ….
Paul	Mark	Steve	Mark	Paul
John	Paul	Robert	Steve	John
Camille	Frank	Lucy	Robert	Mark
Cecile	Simon		Phil	Simon
			Andrew
			Aldo

I would like for each column in second file to remove names dont listed in file 1

Any suggestions?

[atcodedog05 · Accepted solution]

Hi @AnGi 

Here is what you do.

Workflow:

1. Using transpose i am converting columns of file 2 to rows.

2. Using join tool i am joining by name so that i get only the names that are present.

3. Using multi-row formula grouby Name(day) i am generating row number.

4. Using crosstab with row number as key, Right_Name(Day) as Name and value as value i am converting it back to table.

5. Dynamic rename to fix the column names.

Hope this helps 🙂