Create New Column and fill data from specific cell address

[tviz21]

I am bringing in multiple Excel sheets in which the header row is different for several of them and the rows above contain uneeded data except for 1 specifc cell range D4:E4

I've successfully formatted the needed header row but now need to create a new column "COMP#", go to the range D4:E4 (or just D4 as the two are merged), get that number from that cell and place it in the first row of "COMP#" and then fill down to the last row. 

Trying to use the multi-row formula tool but can't seem to get that to work for me. 

Thanks!

Toni

[RodL · Accepted solution]

Typically I would create a parallel data stream in the workflow to identify that specific cell (if it is consistent where it is in the spreadsheet, I'd use a Select Records and a Select to pin down the cell). 

Then I would just use Append Fields to bring it back into your main workflow.

The Multi-Row Formula tool is more about using values above/below each record as it goes through the data.

[Philip · Accepted solution]

Rod's solution is the way I do this. I also do this with the Summarize tool to get averages over all values and append as a new column, too.

[s_pichaipillai]

Here is my Thought. i assume the data looks like below

1. Split the Actual rows (filter tool , Assume the any one of Header is not null)

2.then Transpose the Header row then filter again for not null, so that we can Grab the New Header Column with Header Row

3. Use Dynamic Rename to Lift the First row to Header

4. Finally Append with Actual Rows and the New Header rows

The Output

[tviz21]

My apologies for not replying sooner! Thank you for the great suggestions, got it working!

Toni