Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need to have data in one column
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..! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to have data in one column
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|