Replace non-header data in a column using header from another column

[bizimran]

Hello Everyone,

There is a problem I am kind of stuck on. There are two rows before the numeric data starts. Trying to rename items in the second row (named "Change") using the data in first row (the name/ number above "Provision"). There can be any number of entities and not just 2 as shown in example below. TIA

INPUT:

F1	F2	TOTAL	F4	F5	F6	Entity1	F8	F9	F10	Entity2	F12	F13
		Provision	Return	Change		Provision	Return	Change		Provision	Return	Change
Income		1000	2000	3000		4000	5000	0		6000	7000	0
		565765	575	5765		678	6886	1		86767	67867	0

OUTPUT

F1

F2

TOTAL

F4

F5

F6

Entity1

F8

F9

F10

Entity2

F12

F13

Provision

Return

TOTAL

Provision

Return

Entity1

Provision

Return

Entity2

Income

1000

2000

3000

4000

5000

0

6000

7000

0

[NicoleJohnson · Accepted solution]

This was a fun one :) 

Multi-Row tool is going to be your friend here - the cool part about this tool is that you can specify the number of rows you wish to check before or after, so in this case, once you pivot the data, you can have a formula that checks to see if the field value = "Change"... and if it does, then check the Name column two rows prior, and use that value instead... otherwise, leave the value as-is.

Another fun trick - to rename your columns the way they were originally, you can Sample 0 rows from your original data, which will just return the header names... then you can union that to your data, using the "Auto Config by Position" option and ordering your inputs for the Union tool to pick the just-the-headers input first, to rename your fields the way they were before. 

Hope this helps! 

Cheers,

NJ

[bizimran]

Thanks! Had till multirow tool but the idea of adding ColumnID and doing a join on position was missing. Very helpful.