1

I have some table with a key and multiple values associated to it in a comma separated way. Would like to change it to one row per value, repeating the key multiple times as follow.

enter image description here

Would you know how to do this without any Excel macro?

pnuts
  • 58,317
  • 11
  • 87
  • 139
goul
  • 813
  • 1
  • 13
  • 32
  • Are you asking how to do this purely with formulas? – Shawn K Sep 09 '15 at 00:31
  • Yes, if it's possible – goul Sep 09 '15 at 00:40
  • You _might_ be able to find someone who can do this with formulas but it wouldn't make sense IMO. Excel just isn't good at some things. What you want to do is a good example of when it's time to learn a little scripting OR get clever with the excel menu (if you don't have a lot of data). 1) run text to columns on the csv column. 2) make a new sheet and **transpose** copy your first row of cells for 'abc' into column b 3) Copy the labels for that data in column a 4) repeat transpose copy for 'def' – Shawn K Sep 09 '15 at 00:49
  • You're right, it's a bit tedious to do it manually in excel and maybe better to do it with a macro. Thanks for your answer – goul Sep 09 '15 at 02:13

1 Answers1

2

Assuming your numbers are text format and abc is in A2, select ColumnB, DATA > Data Tools - Text to Columns, Delimited, Comma, Column data format Text (for all columns) then label the columns and create a Table via a PivotTable from your data with Multiple consolidation ranges as described here. Delete ColumnB and Filter the Value column to remove blank rows.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139