Loop through rows to consolidate data

[glp_127]

I have a simple excel file: column A is list of words and column B shows a
number. (This will become an index of terms in a book -- column B is the
page number reference). The list can be easily sorted, but then I need to
remove rows with duplicate words and just concatenate the page number
references in column B. The result should look like:
word1: 1,35,160
word2: 55
word3: 3, 88

I understand the basic logic required: compare column A value to value from
previous row. If it's the same, concatenate the column B value, delete the
current row and move on.

I'm not experienced with VBA programming though. Does someone have some
code to start with or other ideas? Thank-you.

 

[tom_n_ape]

I have a simple excel file: column A is list of words and column B shows a
number.  (This will become an index of terms in a book -- column B is the
page number reference).  The list can be easily sorted, but then I needto
remove rows with duplicate words and just concatenate the page number
references in column B.  The result should look like:
word1: 1,35,160
word2: 55
word3: 3, 88

I understand the basic logic required: compare column A value to value from
previous row.  If it's the same, concatenate the column B value, deletethe
current row and move on.

I'm not experienced with VBA programming though.  Does someone have some
code to start with or other ideas?  Thank-you.

....but in your comparison, don't you want to compare the word in
column A/Row i to every other word in column A? Not just the previous
row? Or, maybe if you sort the words aphabetically first that will
allow you to run a smaller comparison than the whole list in column
A. Perhaps that's what you were thinking. If it is as easy as
comparing one word to another word in the previous cell then I think
an "IF" statement in an Excel function would work easier than VBA.
You would then copy the IF statement down all cells in column C and
generate a separate column of with the results.

 

[glp_127]

Thanks Tom. Yes, I was planning to sort the data first. And I can write a
simple IF formula to compare column A for 2 consecutive rows and when they
are equal then concatenate column B values. But that doesn't deal with when
there are 3 or more rows to combine … or deleting the duplicate rows. I've
written quite a few Excel formulas in my time, but am not seeing a solution
with formulas in this case. Please let me know if you think I'm missing
something.

 

[tom_n_ape]

Thanks Tom.  Yes, I was planning to sort the data first.  And I can write a
simple IF formula to compare column A for 2 consecutive rows and when they
are equal then concatenate column B values.  But that doesn't deal withwhen
there are 3 or more rows to combine … or deleting the duplicate rows.  I've
written quite a few Excel formulas in my time, but am not seeing a solution
with formulas in this case.  Please let me know if you think I'm missing
something.

Alright, first sort the data alphabetically, then this code should
work for you. Let me know if you have any problems with it.

Sub findDupes()

' findDupes Macro
' Macro recorded 1/7/2009 by Tom

Dim currentRow As Integer

currentRow = 3 'or whatever row you want to start on

Do While Cells(currentRow, 1) <> ""
If Cells(currentRow, 1) = Cells(currentRow + 1, 1) Then
Cells(currentRow + 1, 2).Copy
Cells(currentRow, 2) = Cells(currentRow, 2) & ", " & Cells
(currentRow + 1, 2)
'Rows((currentRow + 1) & ":" & (currentRow + 1)).Select
'use this only if you want to delete the whole row
Range(Cells(currentRow + 1, 1), Cells(currentRow + 1,
2)).Select 'use this to delete just the cells with data
Selection.Delete shift:=xlUp
Else
currentRow = currentRow + 1
End If
Loop
End Sub

 

[glp_127]

Fantastic! I used the code you provided to delete the whole row and it seems
to work perfectly. Thank-you very much.