How to Transpose Data and skip blank Cells

[ANAND KUMAR]

[FONT=&quot]Hi, [/FONT]

[FONT=&quot]I have a format of below data set around 1.7k rows like: [/FONT]

Customer Name	ABHISHEK YADAV
City	Gorakhpur
State	Uttar Pradesh
Customer Name	Gautam Arora
City	Rishikesh
State	Uttarakhand
Customer Name	Kaanchaansunchitt
City	Jhansi
State	Uttar Pradesh
Customer Name	Vishwaroop.saha
City	Gorakhpur
State	Uttar Pradesh

<tbody>
</tbody>

[FONT=&quot][/FONT][FONT=&quot][/FONT]






















Issue - I just wanted to compile above details in following format,

Customer	City	State
ABHISHEK YADAV	Gorakhpur	Uttar Pradesh
Gautam Arora	Rishikesh	Uttarakhand
kaanchaansunchitt	Jhansi	Uttar Pradesh
vishwaroop.saha	Gorakhpur	Uttar Pradesh

<tbody>
</tbody>

Is there any way to achieve this by avoiding VBA.

Thanks in Advance for Help.

 

[godsaaint]

Try this, change Sheet1 to the real sheet's name where you have the 1.7k rows and adjust $B$1:$B$15 and $A$1:$A$15 to the real range. Array formulas, confirm with CTRL-SHIFT-ENTER. Then just drag down.

Customer	City	State
=INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$A$1:$A$15="Customer Name",ROW(Sheet1!$A$1:$A$15)),ROW(A1)))	=INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$A$1:$A$15="City",ROW(Sheet1!$A$1:$A$15)),ROW(A1)))	=INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$A$1:$A$15="State",ROW(Sheet1!$A$1:$A$15)),ROW(A1)))

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[eduzs]

not a pro solution
But supose that the data is all in "A" column:

B1 formula: =A1
C1 formula: =A2
D1 formula: =A3
B2 formula: =INDIRECT("A"&ROW(A1)*3+1)
C2 formula: =INDIRECT("A"&ROW(A2)*3+1)
D2 formula: =INDIRECT("A"&ROW(A3)*3+1)

Copy down B2, C2 and D2 formula till the last line...

 

[ANAND KUMAR]

Got it..........It works beautifully.

Thanks ☺.

@ eduzs - Sorry! But I didn't get you as My data is in column A & B then how could I take only single column.