Hello, See a test data file attached. Trying to figure out a workflow that will accomplish the following: * 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. * Transpose all of the data so that it is listed vertically - Column headers that would remain are:* Account * Acct Type * Entity number (from #1 above) * Then each column name currently in Column B2 - L2 Thanks in advance! text data.xlsx

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:

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.
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!

text data.xlsx

Workflow

Accepted answers

Luke_C

@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.

New Workflow10.yxmd

All comments

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

@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.

New Workflow10.yxmd

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.?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335