Excel file edit headers and transpose

[bradaba]

Hello, 

See a test data file attached. Trying to figure out a workflow that will accomplish the following: 

1. Fill in blank column headers - cell C1 shows entity number "1000", cells D1 through L1 are blank - is it possible to get these to fill with the fist value that is listed in the row before the blank cells? So Cells D1 through L1 would show "1000", Cells M1 through V1 would show "1190", etc.
2. Transpose all of the data so that it is listed vertically - Column headers that would remain are:
   1. Account
   2. Acct Type
   3. Entity number (from #1 above)
   4. Then each column name currently in Column B2 - L2

Thanks in advance!

[ChrisTX]

1) use the Multi-row tool.  Formula would be something like 

IF IsEmpty([FieldName] THEN [FieldName:Row-1] ELSE [FieldName] ENDIF

2) Try the Transpose tool.  Should do exactly what you're asking for.

Chris

[Luke_C · Accepted solution]

@bradaba 

Here's an example, I'm sure there's probably a cleaner way to do this, but this gets the job done with the transpose/multirow tool that @ChrisTX mentioned plus a dynamic rename.

[bradaba]

Thank you - this is very helpful. 

One follow up question - what adjustment would be needed to the workflow if I wanted what is currently in the output "Field" column to be separate column headers? So additional columns for ConsCalc, Elim, Journals, ConsolAdj, GLLoad, etc.? 

[Luke_C]

@bradaba You could use a crosstab tool to set the 'field' column to be the header values.