1

I have this query->

        A     B
     1 |p1  | a:10;b:20;c:30 |
     2 |p2  | a:25;b:15;c:40 |

How to get p1 and p2 in column A until its next value are splitting without using macros?

        A     B   C

    4 |p1   |a  |10 |
    5 |p1   |b  |20 |
    6 |p1   |c  |30 |
    7 |p2   |a  |25 |
    8 |p2   |b  |15 |
    9 |p2   |c  |40 |

Using expression and values in column A are not fixed, they are dynamic.

3
  • Welcome to Super User. It is customary here to show what you have already attempted and where you have become stuck. Volunteers here will try to help you from there. Without effort on your part, you may not receive any response. For further help to improve your question, see How to Ask.
    – CharlieRB
    Oct 5, 2015 at 13:07
  • I assume cell B2 in your first example should read: a:25;b:15;c:40
    – Mike Honey
    Oct 5, 2015 at 23:50
  • 1
    Please don't post your questions twice
    – nixda
    Oct 6, 2015 at 9:19

1 Answer 1

1

I would use the Power Query Add-In for this. From Excel 2016, Power Query is built in to the Data ribbon under "Get and Transform" section.

Power Query can start from an existing Excel Table. I would start with a Split step on Column B using semi-colon. A crucial trick is to open the "Advanced" section on the "Split Column by Delimiter" page and enter a larger number for: "Number of columns to split into". It will default to the minimum needed for your current data e.g. 3. You can enter a larger number e.g. 100 to accommodate future data requirements.

Then I would choose Column A and use the "Unpivot Columns / Unpivot other columns" command to bring all the split columns back into one column, on multiple rows. Finally I would use the Split command again on the generated "Value" column, splitting using colon.

A Query result can be delivered as an Excel Table.

You would probably add a few steps to remove unwanted columns and rename columns. You can build this without writing any macro or function code - you just click around in the Power Query window.

I built a working solution which you can download from my OneDrive and try out:

http://1drv.ms/1AzPAZp

It's the file: Power Query demo - Dynamically splitting a column and unpivoting into rows

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .