I've got a table that looks like this:
017-2032 24
017-2032 30
017-2032 49
017-2032 315
017-2095 30
017-2095 49
017-2095 24
017-2095 315
017-2096 24
017-2096 315
017-4246 315
017-4246 24
017-4246 30
017-4246 49
024-3308 305
024-3308 304

And I'd like to make it look like this:

017-2032 24,30,49,315
017-2095 30,49,24,315
017-2096 24,315
017-4246 315,24,30,49
024-3308 305,304


How can I do that? I'm really bad at this kind of stuff :(

Is the row "identifier" always of the format xxx-xxxx or could it contain more or fewer characters?

--
Tim Williams
Palo Alto, CA


wrote in message oups.com...
I've got a table that looks like this:
017-2032 24
017-2032 30
017-2032 49
017-2032 315
017-2095 30
017-2095 49
017-2095 24
017-2095 315
017-2096 24
017-2096 315
017-4246 315
017-4246 24
017-4246 30
017-4246 49
024-3308 305
024-3308 304

And I'd like to make it look like this:

017-2032 24,30,49,315
017-2095 30,49,24,315
017-2096 24,315
017-4246 315,24,30,49
024-3308 305,304


How can I do that? I'm really bad at this kind of stuff :(

Hi Kyle:

Try:

Sub condensetable()

'assume the cursor is in the first cell on the left
Dim r As Range 'firstcell
Set r = ActiveCell
Dim rOffset As Long ' current row offset
rOffset = 1 ' start on second row
Do While r.Offset(rOffset, 0) < ""
If (r.Offset(rOffset, 0) = r.Offset(rOffset - 1, 0)) Then
r.Offset(rOffset - 1, 1) = _
r.Offset(rOffset - 1, 1) & ", " & _
r.Offset(rOffset, 1)
r.Offset(rOffset, 0).EntireRow.Delete
Else
rOffset = rOffset + 1
End If
Loop
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

I've got a table that looks like this:
017-2032 24
017-2032 30
017-2032 49
017-2032 315
017-2095 30
017-2095 49
017-2095 24
017-2095 315
017-2096 24
017-2096 315
017-4246 315
017-4246 24
017-4246 30
017-4246 49
024-3308 305
024-3308 304

And I'd like to make it look like this:

017-2032 24,30,49,315
017-2095 30,49,24,315
017-2096 24,315
017-4246 315,24,30,49
024-3308 305,304


How can I do that? I'm really bad at this kind of stuff :(

One way:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "B").Value _
= "'" & .Cells(iRow - 1, "B").Value _
& "," & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

Be careful.

This routine destroys the original data--run it against a backup copy of your
worksheet--or close without saving if you don't get the results you want.

The "'" forces the stuff in column B to be treated as text. I didn't want excel
changing the underlying value to a plain old number.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




wrote:

I've got a table that looks like this:
017-2032 24
017-2032 30
017-2032 49
017-2032 315
017-2095 30
017-2095 49
017-2095 24
017-2095 315
017-2096 24
017-2096 315
017-4246 315
017-4246 24
017-4246 30
017-4246 49
024-3308 305
024-3308 304

And I'd like to make it look like this:

017-2032 24,30,49,315
017-2095 30,49,24,315
017-2096 24,315
017-4246 315,24,30,49
024-3308 305,304

How can I do that? I'm really bad at this kind of stuff :(

--

Dave Peterson