In column A, I have product ID that appears many times non-sequentially. I need a macro that finds matched ID-groups and pinpoints in those groups “ID-pre-last” and “ID-last” to insert in corresponding column user-defined-date (in column B). For “ID-pre-last” there will be one date, while for “ID-last” there will be another. The best I could think is to pinpoint the duplicates and paste the same date-values in corresponding cells. But it doesn’t help as the dates have to differ. Could anyone write a loop to solve the issue?

Sub DateManipulation() Dim lastRow As Long Dim matchFoundIndex As Long Dim iVar As Long Dim WsM As Worksheet Dim startDate As Variant Dim endDate As Variant ThisWorkbook.Sheets("test").Activate startDate = "31-12-2016" endDate = "31-12-2018" Application.ScreenUpdating = False lastRow = Range("A" & Rows.Count).End(xlUp).Row For iVar = 3 To lastRow If Cells(iVar, 1) <> "" Then matchFoundIndex = WorksheetFunction.Match(Cells(iVar, 1), _ Range("A1:A" & lastRow), 0) If iVar <> matchFoundIndex Then Cells(iVar, 2) = startDate Cells(iVar, 3) = endDate End If End If Next Application.ScreenUpdating = True End Sub

✔ Best Answer

Assuming that your data starts in G1 and that there are no empty rows in your data set, this should work for you. Well, it works for the data that you posted in your example. If your actual is not laid out the same way, it probably won't work. Sub ChangeLast2Matches() Dim lRow As Long Dim iVar As Long Dim myMatches As Long ThisWorkbook.Sheets("WsMaster").Activate Application.ScreenUpdating = False lRow = Range("G" & Rows.Count).End(xlUp).Row For iVar = 1 To lRow 'Count matches for current cell myMatches = _ WorksheetFunction.CountIf(Range("G1:G" & lRow), Range("G" & iVar)) 'If there is a match, change the last 2 entries in Column L If myMatches > 1 Then Cells(iVar + myMatches - 1, 12) = "2018-12-31" Cells(iVar + myMatches - 2, 12) = "2016-12-31" 'Increase the Row variable to move to next non-matching ID iVar = iVar + myMatches - 1 End If Next Application.ScreenUpdating = True End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

I have read your post a few times over and cannot understand what you are asking. Perhaps it is just me but i'll need some clarification. Can you use the pre tags (search the how to section for pre tags) and provide some example data, then provide the expected out come from that data.

This should give us some idea of what you are asking.

Hey again Thank you for helping. I agree it is not simple to understand. Since I wrote my post, I could not find a proper solution so I keept my macro which highlighted the rows to be changed and I made it manually.

Sub CellesHighl() Dim lRow As Long Dim matchFoundIndex As Long Dim iVar As Long Dim WsM As Worksheet Dim startDate As Variant Dim endDate As Variant Dim preDate As Variant Dim mStartRow As Range Dim mEndRow As Range, mPreRow As Integer ThisWorkbook.Sheets("WsMaster").Activate Application.ScreenUpdating = False lRow = Range("G" & Rows.count).End(xlUp).Row For iVar = 3 To lRow If Cells(iVar, 7) <> "" Then matchFoundIndex = WorksheetFunction.Match(Cells(iVar, 7), _ Range("G1:G" & lRow), 0) If iVar <> matchFoundIndex Then Range(Cells(iVar, 11), Cells(iVar, 12)).Interior.ColorIndex = 20 End If End If Next Application.ScreenUpdating = True End SubI have data set in the area of A:N, in column G - is item-id-number which I sorted by identical ids in a corresponding groups. (I sorted the table by using a marco but can be also sorted by applying conditional formating). In columns K is a date one and in colunm L - date two.

My original data set looks like:

Row G K L1 Y-210SC 2011-11-01 2016-10-312 Y-210SC 2011-11-01 2016-10-313 Y-210SC 2011-11-01 2016-10-314 Y-210SC 2011-11-01 2016-10-315 Y-220MU 2014-11-01 2016-10-316 Y-225SZ 2015-11-01 2016-10-317 Y-225SZ 2015-11-01 2016-10-318 Y-236XN 2016-01-01 2016-10-31After proceeding with macro, the outcome might look like:

Row G K L1 Y-210SC 2011-11-01 2016-10-312 Y-210SC 2011-11-01 2016-10-313 <b>Y-210SC </b> 2011-11-01 <b>2016-12-31</b>4 <b> Y-210SC </b> 2011-11-01 <b>2018-12-31</b>5 Y-220MU 2014-11-01 2016-10-316 <b>Y-225SZ </b> 2015-11-01 <b>2016-12-31</b>7 <b>Y-225SZ </b> 2015-11-01 <b>2018-12-31</b>8 Y-236XN 2016-01-01 2016-10-31

As it is seen from the above, I want my macro to proceed as follows:

if id-number is listed only once, do noting (see row 5 and 8).

if id-number is listed twice and more, find pre-last-id-number and last-id-number and make changes in kolunm L. In corresponding row for pre-last-id-number insert "2016-12-31" and in corresponding row for last-id-number insert "2018-12-31".Hope that now it is more understandable, and someone can poin into right directions. Honestly, I tried to google the topic on different web-forums, but eventually dropped it since couldn't find a relevant solution.

I understand what you are trying to do, but I'm confused about something else. Your data starts in Row 1, but you don't start checking for matches until Row 3.

For iVar = 3 To lRowWhy is that?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Assuming that your data starts in G1 and that there are no empty rows in your data set, this should work for you. Well, it works for the data that you posted in your example. If your actual is not laid out the same way, it probably won't work. Sub ChangeLast2Matches() Dim lRow As Long Dim iVar As Long Dim myMatches As Long ThisWorkbook.Sheets("WsMaster").Activate Application.ScreenUpdating = False lRow = Range("G" & Rows.Count).End(xlUp).Row For iVar = 1 To lRow 'Count matches for current cell myMatches = _ WorksheetFunction.CountIf(Range("G1:G" & lRow), Range("G" & iVar)) 'If there is a match, change the last 2 entries in Column L If myMatches > 1 Then Cells(iVar + myMatches - 1, 12) = "2018-12-31" Cells(iVar + myMatches - 2, 12) = "2016-12-31" 'Increase the Row variable to move to next non-matching ID iVar = iVar + myMatches - 1 End If Next Application.ScreenUpdating = True End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Hey,Yes, it is true - I posted the data from the row one, since I dropped the first-name-row and description-second-row from my ERP-system (when I retrieve the data in excel).That's why I had my loop from the third row.

Your macro works really well and I am happy with your help - it makes my work much easier (given that I had already tried it manually). Thank you a lot for helping.

Ask Your Question

Weekly Poll