VBA to count matches from 2 columns

[rhombus4]

Hi
Need some VBA help

I have a sheet with over 5000 entries Column C contains a Date and Column F contains an ID code

I need to check that when the first part (first 10 digits) of the ID codes are identical that the date in Column C matches for all IDS where the first 10 digits match. If it doesn't match then maybe highlight it

Some IDs have 1 so they are ok but some have 2 some 3 and some 4 etc

e.g. because 1st 10 characters are the same and the date is the same then its ok
20/11/20 1234567890555
20/11/20 1234567890666

But even though these 4 IDs are ok there dates are different
14/11/20 1234567890888
14/11/20 1234567890123
15/11/20 1234567890891
16/11/20 1234567890716

Hope that makes sense

Thanks

 

[Osvaldo Palmeiro]

Hi. Try:

Sub CountPartialMatches()
 Dim LR As Long
  LR = Cells(Rows.Count, 3).End(3).Row
  [G:G] = ""
  Range("G2:G" & LR) = "=SUMPRODUCT((C$2:C$" & LR & "=C2)*(LEFT(F$2:F$" & LR & ",10)=LEFT(F2,10)))"
  Range("G2:G" & LR).Value = Range("G2:G" & LR).Value
End Sub

The counting will be placed by the code in column G.
The results equal to 1 means that there is no matches for that record.
Then you could apply Auto Filter on column G.

 

[rhombus4]

It partially works, see below, but if I add a number that doesn't match the 1st 10 digits, that also shows a 1

14/11/20 1234567890888. 2
14/11/20 1234567890123. 2
15/11/20 1234567890891. 1
16/11/20 1234567890716. 1
16/11/20 8888888888888. 1, because this one doesn't have any matches in that column I don't want it to show a 1

I just need the ones that have a count of greater than 1 to check with the date
I also noticed that if the 3rd and 4th date matched that would also appear as 2

Wherever the first 10 characters match, all the dates should be the same. If they don't match then show those

Maybe I over complicated it by using 1st 10 characters, maybe I could have just used left function to get first 10 first characters.

another example

14/11/20 123456
14/11/20 123456
15/11/20 123456
16/11/20 123456
17/11/20 888888

Only ones I want to see when filtered are the ones on the 15th and 16th as they appear in that column more than once but dates don't match. Also if they both appeared on the 15th that woul also show a 2. I want it to be highlighted as all 4 of them should be same date.

 

[Osvaldo Palmeiro]

Try the two new versions below and see if we're closer. Otherwise, please show us some data with the desired results.

Sub CountPartialMatchesV2()
 Dim LR As Long
  LR = Cells(Rows.Count, 3).End(3).Row
  With Range("G2:G" & LR)
   .Value = ""
   .Interior.Color = xlNone
   .Value = "=SUMPRODUCT((C$2:C$" & LR & "=C2)*(LEFT(F$2:F$" & LR & ",10)=LEFT(F2,10)))"
   .Value = .Value
   .Replace 1, "", xlWhole
   .SpecialCells(xlBlanks).Interior.Color = vbYellow
  End With
End Sub

Sub CountPartialMatchesV3()
 Dim LR As Long
  LR = Cells(Rows.Count, 3).End(3).Row
  With Range("G2:G" & LR)
   .Value = ""
   .Interior.Color = xlNone
   .Value = "=SUMPRODUCT((C3:C$" & LR & "=C2)*(LEFT(F3:F$" & LR & ",10)=LEFT(F2,10)))"
   .Value = .Value
   .Replace 0, "", xlWhole
   .SpecialCells(xlBlanks).Interior.Color = vbYellow
  End With
End Sub

 

[Peter_SSs]

@rhombus4

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, do you really need vba? What if this could be done with Conditional Formatting like below?

20 11 28.xlsm
	C	D	E	F
1	Date			ID
2	19/06/2020			1234567890888
3	19/06/2020			1234567890777
4	19/06/2020			1234567890241
5	20/06/2020			9876543210235
6	18/06/2020			9876543210236
7	18/06/2020			5698563215487
8	19/06/2020			986523021547
9	19/06/2020			1234567890241
10	19/06/2020			555555555555
11	23/06/2020			9876543210237
12	20/06/2020			155555555555
13	20/06/2020			155555555550
14	20/06/2020			5555555555870
Check ID & Date

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C14	Expression	=SUMPRODUCT(--(LEFT(F$2:F$14,10)=LEFT(F2,10)),--(C$2:C$14<>C2))	text	NO

 

[rhombus4]

Thanks guys.

Peter your conditional format worked apart from one thing. If you changed C11 on yours to from 23/06/20 to 18/06/20 that shouldn't be highlighted as C6 will have same date and same first 10 digits. The only one highlighted of the 3 with same case ID is C5 as even though the ID matches that one is 20/06/20 and other two will be 18/06/20

20/06/2020

9876543210235

Osvaldo, unfortunately couldn't get yours to work
1st formula =SUMPRODUCT((C$2:C$14=C2)*(LEFT(F$2:F$14,10)=LEFT(F2,10)))
2nd formula =SUMPRODUCT((C3:C$14=C2)*(LEFT(F3:F$14,10)=LEFT(F2,10)))


Using Peters example above the results of 1st formula were
4
4
4
1
2
1
1
4
1
2
2
2
1

2nd formula

3
2
1
0
1
0
0
0
0
0
1
0
1

 

[Osvaldo Palmeiro]

Using Peters example above the results of 1st formula were
...
...

So, what is your desired result ?

 

[Peter_SSs]

Peter your conditional format worked apart from one thing. If you changed C11 on yours to from 23/06/20 to 18/06/20 that shouldn't be highlighted as C6 will have same date and same first 10 digits. The only one highlighted of the 3 with same case ID is C5 as even though the ID matches that one is 20/06/20 and other two will be 18/06/20

That seems to contradict your original statement...

when the first part (first 10 digits) of the ID codes are identical that the date in Column C matches for all IDS where the first 10 digits match.

 

[rhombus4]

S

That seems to contradict your original statement...

Sorry meant to say highlight the ones that don't match

 

[rhombus4]

So, what is your desired result ?

Only highlight ones that have more than one, where the date doesn't match.

Basically all IDs which are the same should have the same date. If 1 or more of them have a different date highlight these. If they all have a different date then you can highlight them all

If there are 2 with same ID and have same date then it's ok. If they have different dates, highlight both

If 3 have same ID. If 2 have same date and other one has different date, highlight that one. All 3 different dates highlight all 3

If 4 have same ID. If 3 have same date highlight other one. If they have 2 with one date and other 2 have different dates highlight those 2. If 2 have same date and other 2 have same date then can highlight all.