Address Labels from single column traspose to multiple column

[dingdang]

HI,

I have huge Address data in single column and i want to transpose to multiple column,

no of cell is not fixed for address but there is 1 blank cell between each address,

pls guide.

[pre]

COLUMN A
21215545845
ID :: 134000119
S GOGATI
SHIVAJI NAGAR
DHL
PIN : 400 002

21215545845
ID :: 134000119
R B MARITI
CHANDRAKAMAL',
BUDWAR PETH,
MUMBAI
PIN: 400002

21215545845
ID :: 134000119
S V DESHPANDE
HOUSING SOCIETY,
MARKET
MUMBAI
PIN: 400072
Required data as below in columne B

21215545845	ID :: 134000119	S GOGATI	SHIVAJI NAGAR	DHL	PIN : 400 002
21215545845	ID :: 134000119	R B MARITI	CHANDRAKAMAL',	BUDWAR PETH,	MUMBAI	PIN: 400002
21215545845	ID :: 134000119	S V DESHPANDE	HOUSING SOCIETY, 	MARKET 	MUMBAI	PIN: 400072

[/pre]

 

[Luke M]

I think this will do it for you:

Sub TransposeData()
Dim i As Integer
Dim ar As Range
'Starting output row
i = 1
Application.ScreenUpdating = False
 
'Since there is a blank cell between each address, we'll look
'at each area of cells
For Each ar In Range("A:A").SpecialCells(xlCellTypeConstants).Areas
    ar.Copy
    Cells(i, "B").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    i = i + 1
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
 
End Sub

 

[SirJB7]

Hi, dingdang!


An alternative with formulas using B as helper column maybe this:


B1: Any text


C1:M1 : Titles for 10 fields (maximum for each group, adjust if needed)


B2: =SI.ERROR(COINCIDIR("_";INDIRECTO("A"&B1+1&":A24";VERDADERO)&"_";0)+B1;1) -----> =IFERROR(MATCH("_",INDIRECT("A"&B1+1&":A24",TRUE)&"_",0)+B1,1)

This is an array formula, so...

Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


C2:M2 : =SI(ESNOD(COINCIDIR("";$B2:B2;0));SI(INDICE($A:$A;($B2+COLUMNA()-2))="";"";INDICE($A:$A;($B2+COLUMNA()-2)));"") -----> in english: =IF(ISNA(MATCH("",$B2:B2,0)),IF(INDEX($A:$A,($B2+COLUMN()-2))="","",INDEX($A:$A,($B2+COLUMN()-2))),"")


Copy down B2:M2 as required or until first blank in C column appears.


Just advise if any issue.


Regards!