Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Excel table data formatting

bradaba33
5 - Atom

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
2 REPLIES 2
Kurohits
9 - Comet

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
8 - Asteroid

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. 

Labels