Is there way to split data into multiple rows?

What I have looks like this:

1 a b c
2 d e
3 f g h

and I want it to end up like this:

1 a
1 b
1 c
2 d
2 e
3 f
3 g
3 h

There is not a constant number of entries in each row at the moment. Does anyone have any ideas?

I'm just about to go out, but take a look at this thread that I've just responded to:

http://www.excelforum.com/excel-form...to-colums.html

That is similar to yours, but has a fixed number of columns to transpose. For yours, you will need to count the number of entries in each row and then use a table of cumulative entries to determine which row to get the data from.

I'll take a further look when I get back in this afternoon.

Hope this helps.

Pete

Thanks for this. I haven't been able to do anything clever to deal with the non fixed number of columns but the formula for a fixed number just returns a zero if the cell is blank so at the end of the process I just filtered those out and deleted them. A brute force approach, but it works and didn't take much time at all. Thanks again.

Hi Squibfish,

I've set this up in the attached file, along the lines that I outlined before. I've set up some test data in columns A to K, allowing up to 10 columns of data. I've used this formula in M2:

=COUNTA(B2:K2)

and this one in N2:

=IF(M2=0,"",M2+N1)

and I have copied these down to row 10 to set up the table that I mentioned. It is important to put zero in cell N1.

Then I have this formula in P1:

=IF(ROWS($1:1)>MAX(N:N),"",INDEX($A$2:$A$5,MATCH(ROWS($1:1)-1,N:N)))

which gets the data from column A, and this one in Q1:

=IF(ROWS($1:1)>MAX(N:N),"",INDEX($B$2:$K$5,MATCH(ROWS($1:1)-1,N:N),ROWS($1:1)-INDEX(N:N,MATCH(ROWS($1:1)-1,N:N))))

which brings the appropriate data from columns B to K. These two formulae are then copied down as far as you need them (I've copied to row 25, although they only need to go to row 18 - you just get blanks if there is no more data).

All the data is in consecutive rows, so there is no need to filter and remove blanks.

Hope this helps.

Pete

That's great. Thanks Pete

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete