merge multiple rows- is this possible

[mrxwanttobe]

hello all. someone sent me a comma delimited file. Each complete customer record ends with the line that starts with the word Tax. Is there a way to move that data to one row of comma separated values?

Example in first record would be 1,CANCUN AUTO REPAIR,7110 LINDEN AVE,,ROSEMONT,IL,773 981-8014,,MARTIN,,(fax),(Rep),AreaDES,,(lob),,(Crlim 0),,(Tax Cde2),, and then the next record.

Customer	Name and Address
1​	CANCUN AUTO REPAIR
7110 LINDEN AVE
ROSEMONT	IL"
773-981-8014
MARTIN
(Fax )"	(Rep )
(Area DES)
(Lob )
(CrLim 0)
(Tax Cde 2)"
501​	O'REILLY AUTO PART
5010 W. NORTH AVE
CHICAGO	IL 60639"
773 622 7068
(Fax )"	(Rep )
(Area BEL)
(Lob )
(CrLim 0)
(Tax Cde 3)"

 

[offthelip]

try this:

Sub test()
Dim outarr()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
indi = 2
maxcol = 1
colno = 1
ReDim outarr(1 To lastrow, 1 To 50) As Variant
For i = 2 To lastrow
  For j = 1 To 2
   If Left(inarr(i, j), 4) = "(Tax" Then
    outarr(indi, colno) = inarr(i, j)
    indi = indi + 1
    colno = 1
   Else
   outarr(indi, colno) = inarr(i, j)
   colno = colno + 1
   If colno > maxcol Then
    maxcol = colno
   End If
   End If
 Next j
Next i
With Worksheets("Sheet2")
 .Range(.Cells(1, 1), .Cells(indi, maxcol)) = outarr
 End With

End Sub

I have assumed that there are never more than 50 columns needed, this could be changed very easily
do you want to get rid of the blanks too, that is very easily done too

 

[mrxwanttobe]

amazing and thank you