Transpose the data

[aladin347]

Hi Experts,

Could you please help me as how to transpose the data in the below format, by looking at the city or number and the below cells for the respective city should be blanks.

NO	City	PO No	Example - Transpose
100	AMERICA	340	643	562	559	269
100	AMERICA	643
100	AMERICA	562
100	AMERICA	559
100	AMERICA	269
200	London	582	775	575	705	379
200	London	775
200	London	575
200	London	705
200	London	379
300	JONARD	776	result with Formula
300	JONARD	430
300	JONARD	556
300	JONARD	409
300	JONARD	694

<tbody>
</tbody>

Thanks,
Aladdin

 

[Vidar]

Hi

If your city column is sorted as in your example, and your data is in column A, B and C then
try this formula in column D (cell D2). Confirm with Ctrl+Shift+Enter (it's an array formula). Adjust the ranges as needed.
Copy the formula to right as many columns as needed, and down all the rows.
=IF(COLUMNS($D$1:D1)>COUNTIF($B$2:$B$16,$B2),"",IF($B2<>$B1,LARGE(IF($B$2:$B$16=$B2,$C$2:$C$16),COLUMNS($D$1:D1)),""))

 

[VBA Geek]

this is one of the ways you could do it with vba as well

since i did not quite get what you meant by:

and the below cells for the respective city should be blanks.

i have added

.ClearContents

you can remove it if unnecessary

Sub Transpose_the_data()
Dim Arr, Ndx As Long, LRow As Long
Application.ScreenUpdating = False
LRow = Range("A" & Rows.Count).End(xlUp).Row
Arr = Filter(Evaluate(Replace(Replace("TRANSPOSE(IF(B2:B%<>B1:B@,ROW(B1:B@)+1,""°°°""))", "%", LRow + 1), "@", LRow)), "°°°", False)
For Ndx = LBound(Arr) To UBound(Arr) - 1
    With Range("C" & Arr(Ndx) + 1 & ":C" & Arr(Ndx + 1) - 1)
        .Copy
        Range("D" & Arr(Ndx)).PasteSpecial , , , True
        .ClearContents
    End With
Next
End Sub