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

Question

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:

F1F2TOTALF4F5F6Entity1F8F9F10Entity2F12F13  ProvisionReturnChange ProvisionReturnChange ProvisionReturnChangeIncome 100020003000 400050000 600070000  5657655755765 67868861 86767678670

OUTPUT

F1F2TOTALF4F5F6Entity1F8F9F10Entity2F12F13  ProvisionReturnTOTAL ProvisionReturnEntity1 ProvisionReturnEntity2Income 100020003000 400050000 600070000

NicoleJohnson · Accepted Answer

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