Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through rows to consolidate data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through rows to consolidate data
On Jan 7, 7:26*am, glp_127 wrote:
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. ....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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through rows to consolidate data
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through rows to consolidate data
On Jan 7, 9:42*am, glp_127 wrote:
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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through rows to consolidate data
Fantastic! I used the code you provided to delete the whole row and it seems
to work perfectly. Thank-you very much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate data from multiple rows | Excel Discussion (Misc queries) | |||
Match consolidate multiple rows data into one row | Excel Worksheet Functions | |||
Consolidate data from worksheet (more then 65536 rows) | Excel Discussion (Misc queries) | |||
Create loop to insert two rows after every row of data | Excel Programming | |||
Loop cells - get all rows with matching data - paste into different wb | Excel Programming |