Need to combine data programatically.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HearSay - 31 May 2006 21:17 GMT

Here is my spreadsheet

369 Medical Authorization/Utilization
370 Apportionment
370 Settlement
371 Apportionment
371 Settlement
372 Apportionment

For my final results I would like to see

369 Medical Authorization/Utilization
370 Apportionment, Settlement
371 Apportionment, Settlement
372 Apportionment

So I a column has a duplicate value, I would like to combine those into one
row and delete the row after combining. There could be more than two
matches.

Any ideas on how to do this?

Reply to this Message

PaulD - 31 May 2006 22:49 GMT

: Here is my spreadsheet
:
[quoted text clipped - 17 lines]
:
: Any ideas on how to do this?

Assuming your first column is in the order you want something like this is a
start

Sub CombineCells()
Dim TotalRows As Integer, iCount As Integer

With ActiveSheet
TotalRows = .UsedRange.Rows.Count
For iCount = TotalRows To 2 Step -1
If .Cells(iCount, 1).Value = .Cells(iCount - 1, 1).Value Then
.Cells(iCount - 1, 2).Value = .Cells(iCount - 1, 2).Value & ", " &
.Cells(iCount, 2).Value
.Rows(iCount).Delete
End If
Next iCount
End With
End Sub

The main thing to note is if you are deleting rows, then you have to work
from the bottom up. This is why the routine finds the usedrange, gets the
number of rows, then counts up (step -1). You may want to modify this
routine if you have a predefined range you want to check or if your starting
row is not 1.

Paul D

Reply to this Message