Transpose the data

aladin347

New Member
Joined
Sep 11, 2014
Messages
9
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.



NOCityPO NoExample - Transpose
100AMERICA340643562559269
100AMERICA643
100AMERICA562
100AMERICA559
100AMERICA269
200London582775575705379
200London775
200London575
200London705
200London379
300JONARD776result with Formula
300JONARD430
300JONARD556
300JONARD409
300JONARD694

<tbody>
</tbody>

Thanks,
Aladdin
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Vidar

Well-known Member
Joined
Jul 19, 2012
Messages
1,254
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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
Code:
.ClearContents

you can remove it if unnecessary

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,583
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top