I need to loop through all rows of my data, and consolidate all of the like rows into one row, and include a count of how many versions of that row there where. As you can see below I am trying to consolidate these 5 rows of data into 2, and my code does nothing of the sort, Can someone point me in the right direction?
Consolidate this data:
[TABLE="width: 402"]
<tbody>[TR]
[TD]District Name[/TD]
[TD]Center Num[/TD]
[TD]Center Name[/TD]
[TD]Acct Facility Name[/TD]
[TD]Acct Facility Loc Num[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]855[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]855[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854 [/TD]
[/TR]
</tbody>[/TABLE]
Into:
[TABLE="width: 402"]
<tbody>[TR="bgcolor: transparent"]
[TD]District Name[/TD]
[TD]Center Num[/TD]
[TD]Center Name[/TD]
[TD]Acct Facility Name[/TD]
[TD]Acct Facilit Loc Num[/TD]
[TD]Count[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854[/TD]
[TD]3[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]855[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Consolidate this data:
[TABLE="width: 402"]
<tbody>[TR]
[TD]District Name[/TD]
[TD]Center Num[/TD]
[TD]Center Name[/TD]
[TD]Acct Facility Name[/TD]
[TD]Acct Facility Loc Num[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]855[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]855[/TD]
[/TR]
[TR]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854 [/TD]
[/TR]
</tbody>[/TABLE]
Into:
[TABLE="width: 402"]
<tbody>[TR="bgcolor: transparent"]
[TD]District Name[/TD]
[TD]Center Num[/TD]
[TD]Center Name[/TD]
[TD]Acct Facility Name[/TD]
[TD]Acct Facilit Loc Num[/TD]
[TD]Count[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]854[/TD]
[TD]3[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]RED RIVER[/TD]
[TD]7738[/TD]
[TD]WBK - WOODLANDS[/TD]
[TD]WILLIAMS-SONOMA[/TD]
[TD]855[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Code:
Sub DelCount()
Dim xlBook As Workbook
Dim xlSheet As Worksheet
'Setup references to workbook and sheet
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Sheets("Deliveries")
Dim RowNum As Long
Dim LastRow As Long
Application.ScreenUpdating = False
'start below titles and make full selection of data
RowNum = 2
LastRow = xlSheet.Cells(Rows.Count, "A").End(xlUp).Row
xlSheet.Range("A2", Cells(LastRow, 10)).Select
'For loop for all rows in selection with cells
For Each Row In Selection
With Cells
'if account number matches
If Cells(RowNum, 9).Value = Cells(RowNum + 1, 9).Value Then
'Increase count of deliveries by 1 for each matching row
Cells(RowNum + 1, 10).Value = Cells(RowNum + 1, 10).Value + 1
'Copy the increased value upto top line
Cells(RowNum + 1, 10).Copy Destination:=Cells(RowNum, 10)
Rows(RowNum + 1).EntireRow.Delete
End If
End With
'increase rownum for next test
RowNum = RowNum + 1
Next Row
'turn on screen updating
Application.ScreenUpdating = True
End Sub
Last edited: