Adding data in one column based on another column using VBA

Adding data in one column based on another column using VBA

• Ryan F

  To ADG - I just wanted to say thank you! I modified it and it looks like it will work great.

  It was hard to believe that it had to be so complex. Logically it seems so easy.

  Thanks to both of you for taking the time to answer. - roger

  
  

• Rene Alexander

  Hi

  Below is not elegant, but it might give you an idea. The below assumes that data starts in A1. The arrays store the summary data then the data range is deleted and the array data written back. Try with some test data in a new sheet.

  Public Sub TEST()
  Dim X, lngLastRow, Y As Long
  Dim strList() As String
  Dim dblCount() As Double

  X = 1
  With Worksheets("Sheet1")
  While Len(.Cells(X, 1).Value) > 0
  If X = 1 Then
  ReDim strList(1)
  ReDim dblCount(1)
  strList(1) = .Cells(X, 1).Value
  dblCount(1) = .Cells(X, 2).Value
  lngLastRow = 1
  Else
  Y = 0
  For z = 1 To lngLastRow
  If strList(z) = .Cells(X, 1).Value Then
  Y = z
  End If
  Next
  If Y = 0 Then
  lngLastRow = UBound(strList) + 1
  ReDim Preserve strList(lngLastRow)
  ReDim Preserve dblCount(lngLastRow)
  strList(lngLastRow) = .Cells(X, 1).Value
  dblCount(lngLastRow) = .Cells(X, 2).Value
  Else
  dblCount(Y) = dblCount(Y) + .Cells(X, 2).Value
  End If
  End If
  X = X + 1
  Wend
  .Range("A1:B" & Trim$(Str$(X))).Delete
  For z = 1 To lngLastRow
  .Cells(z, 1) = strList(z)
  .Cells(z, 2) = dblCount(z)
  Next
  End With
  End Sub

  Happy holidays

  ADG

  
  

• John Papa

  Yeah, that's pretty easy.

  1) One approach is to make a new sheet, and only copy the none duplicated key, and Add the value when duplication found.

  To find duplicate key, you can record Find proceedure. If found, activecell will stop at the cell. If not found (no key in the new sheet yet), it will report an error.

  2) Another approach is to do it on the same sheet, but it will be more complicated. You will need a pointer to tell up-to which row the keys are unique and find the duplicated entries below that.

  3) If re-order entries is ok, you can sort the key first. Since it is sorted, you know the same keys are grouped togather, thus, you don't need to search duplication anymore.

  Sorry, there won't be any code. Only high level concept. I tried to write one example, but I will use too much functions from my wrapper class. It will be more confusing for you.

  Some hints you should know.

  To get/set cell formula do Cells(2, 3).ForlumaR1C1 or Range("C2").FormulaR1C1 to get formula at "C2"

  To get/set value result from a formula do Cells(2, 3).Value or Range("C2").Value to get formula at "C2"

  Use record Macro to capture "Find", "Sort", and delete row functions.

  
  
• Adding data in one column based on another column using VBA