Data Sorting

[Michael]

Hi all,

I have a something that I would have thought Excel could handle but have no
idea how to actually do it and wondered if it would be better in Access.

I have an excel sheet with 20,000 rows which is populated with a unique ID
number and a phone number however rather than each phone number being placed
on the same line that corresponds with each phone number it has been placed
on the next line down ie

Unique ID Phone Number
123 0161 555 2269
123 0161 555 1234
123 (e-mail address removed)

and I need it to be

Unique ID Phone Number
123 0161 555 2269 0161 555 1234 (e-mail address removed)

thanks in advance

Michael

 

[SmartbizAustralia]

Sounds like a simple vba loop to copy the data to another sheet

First sort the table ny unique id and then row by row copy the data

e.g.
private sub MakeNiceData
Dim wsData as worksheet
Dim wsNew as worksheet
Dim lngRow as long
Dim lngNewRow as long
Dim iCol as integer
set wsData = thisworkbook.Worksheets("Data")
set wsNew = thisworkbook.Worksheets("NiceData")
icol=2
lnNewRow = 1
for lngRow = 2 to 1000
if wsData.cells(lngRow,1) = wsData.cells(lngRow-1,1) then
iCol=icol+1
else
iCol=2
lngrow = lngrow+1
end if
wsNewData.cells(lngNewRow,icol)=wsdata.cells(lngrow,2)

next lngRow

end sub

Regards,
Tom Bizannes
Excel Development
Sydney, Australia
Industrial Strength Excel Development
http://www.macroview.com.au