1

I'm doing a script where i can count the data from a column if the data duplicate is >3 i will tag it..

My problem is that i need to put a conditional statement where i can count the data within a date range of 1 Month..

Sample Input File:: (mm/dd/yyyy)

Column A | Column B | Column C| Column D
023      | 1/2/2016 |         |   
023      | 1/3/2016 |         |    
023      | 1/4/2016 |         |    
024      | 2/1/2016 |         |
024      | 3/1/2016 |         |
024      | 4/1/2016 |         |

Sample Output File:

Column A | Column B | Column C| Column D
023      | 1/2/2016 |         |   
023      | 1/3/2016 |         |    
023      | 1/4/2016 |   1     |    3
024      | 2/1/2016 |         |
024      | 3/1/2016 |         |
024      | 4/1/2016 |         |

If the duplicate data is not within a month range it will not be tag..

What i expect the code to do is count the data from Column A if the data is >3 and the date of all that data from Column B is within a month tag it from Column D and E not all the row but the recent date from Column B

What my code do is count the data from Column A if the data is >3 it will be tag from Column C and D from most recent date from Column B

My code:

Dim i1 As Long, lastRow As Long, countRow As Long

    lastRow = Sheet2.Range("T" & Rows.Count).End(xlUp).Row
    'xDate = Sheet2.Range("C" & lastRow)

    For i1 = 1 To lastRow

    If countRow > 2 Then
        countRow = Application.CountIf(Sheet2.Columns(20), Sheet2.Cells(i1, 20))
        If countRow > 2 Then
            If Not CBool(Application.CountIfs(Sheet2.Columns(20), Sheet2.Cells(i1, 20), _
                                    Sheet2.Columns(85), ">" & Sheet2.Cells(i1, 85))) Then _
                Sheet2.Cells(i1, 86).Resize(1, 2) = Array("1", "3")
            End If
        End If
    Next i1

Note:

  • In my code i didn't use Column A B C D instead it's Column T CG CH CI

I don't know how to range it to a month, i tried collection but still new to VBA and I'm not familiar with it and i don't know if it's the right thing..

Improve this question

2 Answers 2

Reset to default
1

edited: left only code as per last OP's specs and with a formula correction

you could try this

Sub sbFindDuplicatesInColumn_C3ter()

With ThisWorkbook.Worksheets("duplicates")   '<~~ you should know what workbook and worksheet you are on!!
    With .Range("T1").Resize(.Range("T" & .Rows.Count).End(xlUp).Row) ' the "base" column is column "T"
        With .Offset(, 67) ' column "CI" is 67 columns away from column "T"
            .FormulaR1C1 = "=IF(COUNTIFS(C20, RC20, C72,""<="" & EOMONTH(RC72,0), C72,"">="" & EOMONTH(RC72,-1)+1 )>2,    IF(COUNTIFS(C20, RC20,C72,"">"" &RC72,C72,""<="" & EOMONTH(RC72,0))=0,                   3   , """")      , """")" ' substituted relative references with absolute ones : column "T" has index 20, column "BT" has index 72
            .Value = .Value '<== if you want to get rid of formulas
        End With
        With .Offset(, 66) ' column "CH" is 66 columns away from column "T"
            .FormulaR1C1 = "=IF(RC[1]>0, 1, """") " ' I left relative references since columnn "CH" is always one left of column "CG" as was for columns "A" and "B"
            .Value = .Value '<== if you want to get rid of formulas
         End With
   End With
End With

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

thank you for including the get rid of formulas your so kind!.. out of curiosity if im going to change the columns A B C D to T CG CG CH ?
see edited code to satisfy "curiosity". If I fulfilled your question, please mark my answer as accepted. thank you
well, that you can easily and quickly find it by yourself
read the comments I left to let you handle any possible change in columns reference, between them there is column "T" has index 20, column "CG" has index 85. so if you need to change "CG" into "BT" you have to substitute every occurrence of 85 with the index column of any new column you want to point to
i just noticed in column CI it returns how many value it count, e.g if there's 5 duplicate it tag as 5, how can i make it into a specific value like "3"
|
1

This input ...

enter image description here

generated this output ...

enter image description here

from this code ...

Option Explicit

Sub main()
Dim iLoop As Long, jLoop As Long
Dim lastRow As Long, countRow As Long
Dim myDate1 As Variant, myDate2 As Variant

    lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    For iLoop = 1 To lastRow
        countRow = Application.CountIf(Sheet1.Range(Sheet1.Cells(iLoop, 1), Sheet1.Cells(lastRow, 1)), Sheet1.Cells(iLoop, 1))
        If countRow > 2 Then
            For jLoop = lastRow To (iLoop + 1) Step -1
                If Sheet1.Cells(jLoop, 1).Value = Sheet1.Cells(iLoop, 1).Value Then
                    myDate1 = Application.EDate(Sheet1.Cells(iLoop, 2), 3)
                    myDate2 = Sheet1.Cells(jLoop, 2)
                    If myDate2 > myDate1 Then Sheet1.Cells(jLoop, 3).Resize(1, 2) = Array("1", "3")
                    Exit For
                End If
            Next jLoop
        End If
    Next iLoop
End Sub

Specifically, using the worksheet function EDate to add three months to the first date found for a given item number.

Also, shortening the size of the list the worksheet function CountIf uses as the loop counter iLoop progresses.

As an aside, in your code snippet you used i1 as a loop counter. It is easy to confuse this with il. 8)

Improve this answer

4 Comments

My Excel is interpreting the date as m/d/yyyy. It seems yours is interpreting as d/m/yyyy.
it reads the date "per month" , it should 1month only or 30days.. :(
If you don't want month, but 30 day periods, then instead of using EDate, just add 30*number of months. If you want 2 months, add 60. myDate1 = Sheet1.Cells(iLoop, 2) + 60
what is the purpose of mydate2? it's working but if i have date of jan1 jan2 jan3 feb4 the one that will be tag is feb4 instead of jan3 i wonder why??

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.