Need to have data in one column

[Prashantb]

I have below data in my excel sheet:--

user1 a1 a2 a3
user2 b1 b2 b3
user3 c1 c2 c3


and i need it this way:--

user1 a1
user1 a2
user1 a3
user2 b1
user2 b2
user2 b3
user3 c1
user3 c2
user3 c3


Can anyone please help me in this...

Thanks in advance..

 

[zvkmpw]

I have below data in my excel sheet:--

user1 a1 a2 a3
user2 b1 b2 b3
user3 c1 c2 c3


and i need it this way:--

user1 a1
user1 a2
user1 a3
user2 b1
user2 b2
user2 b3
user3 c1
user3 c2
user3 c3

Here's one way, assuming there are always three rows for each user, and that the data starts at Sheet1!A1.

On another sheet, put this in A1:
=INDEX(Sheet1!$A$1:$A$20,INT((ROW()+2)/3),1)
and this in B1:
=INDEX(Sheet1!$B$1:$D$20,INT((ROW()+2)/3),MOD(ROW()-1,3)+1)
and copy down as far as needed. I used 20 for the number of rows in Sheet1; change the 20's in the formulas as needed for the maximum number of rows.

When a cell is empty, the two formulas produce zero. If you need to avoid this, wrap the formulas this way:
=IF(above_formula="", "", above_formula)

Hope this helps getting started.