For Each/Next Do While Conumdrum

[Matt_O]

Hi Excel Wizards,

I am trying to figure out how to cycle through a column of dates whereby if there are duplicate dates the code runs some If/Then statements and adds data into offset cells. Then I would like to skip over the duplicate dates and start the compare and If/Then process again on the next date.

I capture the first date in Cell(x, 1) and compare it to the Cell below (x +1, 1) and all subsequent rows.

Currently I'm using a For Each cell In Range(Cells (x + 1, 1), Cells(endRow, 1)) to cycle through the column of dates. If there is a match (a duplicate date) to the date in the first row (Cell(x, 1)) then the If/Then statements come into play.

My data looks like this:

Date Client Item
7/3/17 ACME Hammer
7/3/17 ACME Nails
7/3/17 ACME Hammer
7/10/17 BOSS Hammer
7/10/17 BOSS Nails
7/17/17 ACME Nails

So I capture the first date in row 2 (where x = 2 for defining the Cells address) and then cycle through rows 3 (Cells(x + 1, 1)) to 6 looking to match date, company, and item.

Here's what my data looks like after the initial For Each cell loop has completed.

Date Client Item
7/3/17 ACME Hammer
7/3/17 ACME Nails
7/3/17 ACME Duplicate Item (was Hammer)
7/10/17 BOSS Hammer
7/10/17 BOSS Nails
7/17/17 ACME Nails

For Each cell In Range(Cells(x,  1), Cells(endRow, 1) 'this is the target date used for comparison

  d = cell.Value ' the date
  c = cell.Offset(0, 1).Value ' client name
  i = cell.Offset(0, 2).Value ' item name
 
   For Each cell_compare In Range(Cells(x + 1, 1), Cells(endRow, 1) ' this loops through the cells below the target date cell
      
     d_compare = cell_compare.Value ' date to compare
     c_compare = cell_compare.Offset(0, 1).Value  'company to compare
     d_compare = cell_compare.Offset(0, 2).Value 'item to compare

          If d_compare = d And c_compare = c And d_compare = d Then ' this means date, company, and item are a match
             cell_compare.Offset(0, 2).Value = "Duplicate Item" 'replaces the duplicate item with this note
         End If
   Next cell_compare

Next cell ' here is where I would like to skip to row 4 instead of row 2 since this is the next new date (7/10/17).

Does anyone have a suggestion on how to make the second instance of the For Each line of code jump to row 4 (next date) instead of row 2?

I though perhaps a Do While loop might work, as in loop through a range while the dates match, but again I couldn't figure out how to move to the next new date and not just the next row.

Thanks for reading. And special thanks to anyone with suggestions!

Cheers,
Matt

 

[steve the fish]

I think this is what you mean. Test it first on a copy workbook:

Dim fr As Long, lr As Long, i As Long

Sheets("Sheet1").Select

fr = 2
lr = Range("C" & Rows.Count).End(xlUp).Row

For i = fr To lr
    Range("C" & i) = Evaluate(Replace(Replace("=IF(COUNTIFS(A#:A@,A@,B#:B@,B@,C#:C@,C@)=1,C@,""Duplicate Item"")", "@", i), "#", fr))
Next

 

[Matt_O]

Thanks steve the fish.

This worked out perfect. Although I have no idea how to decipher that formula

Cheers,
Matt