Excel table data formatting

[bradaba33]

Hello -

Struggling with this one... I am working with the attached excel file RAW DATA tab. Here is what I am trying to accomplish but can't figure out: 

• Column A includes a data label name (example: cell A13 = Land, A29 = Land - FR FLWTHRU. 
• Column B includes an account number (example: cell B13 and Cell B29)

I'm trying to get the data label name and account number listed in it's own column. For example, looking at rows 14-26, what I'm trying to figure out is how to get "Land" to show in a new Col. A and Account number (7100090) to show in a new Column B. 

Once that is accomplished I would like to transpose data so that it is in a vertical format. 

• New Column A per above, would list the data label name (example: Land)
• New Column B per above, would list the account number (7100090)
• Column C would list the state name or Subtotal
• Column D would list the state abbreviation (with Subtotal added next to subtotal items so that these cells are not blank)
• New Column E = Row 9 identifier

[Kurohits]

Hi @bradaba33 

Can you please share the example output in excel? This is doable but I think batch macro would be feasible way of doing that. 

[MichelleMitchellLutz · Accepted solution]

Hi @bradaba33 !

I think you need to pull out the key columns and then transform the data. Please see attached workflow. I wasn't sure how to handle the subtotal/account numbers, so you may need to make minor modifications.