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

 

[Peter_SSs]

What about this example, with explanation?

20 11 28.xlsm
	C	D	E	F
1	Date			ID
2	19/06/2020			1234567890888
3	19/06/2020			1234567890777
4	20/06/2020			1234567890241
5	20/06/2020			1234567890242
Sample

 

[Osvaldo Palmeiro]

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

Please, try the code below.
The cell in column G will remain with the word TRUE for the records that meet your criteria above.

Sub CountPartialMatchesV4()
 Dim LR As Long
  LR = Cells(Rows.Count, 3).End(3).Row
  With Range("G2:G" & LR)
   .Value = ""
   .Value = "=AND(SUMPRODUCT(N(LEFT(F$2:F$" & LR & ",10)=LEFT(F2,10)))>1,SUMPRODUCT((C$2:C$" & LR & "=C2)*(LEFT(F$2:F$" & LR & ",10)=LEFT(F2,10)))=1)"
   .Value = .Value
   .Replace "False", ""
  End With
End Sub

And for the Conditional Formatting you could use this formula: (maybe there is a shorter one)

=AND(SUMPRODUCT(N(LEFT($F$2:$F$14,10)=LEFT($F2,10)))>1,SUMPRODUCT(($C$2:$C$14=$C2)*(LEFT($F$2:$F$14,10)=LEFT($F2,10)))=1)

 

[rhombus4]

What about this example, with explanation?

20 11 28.xlsm
	C	D	E	F
1	Date			ID
2	19/06/2020			1234567890888
3	19/06/2020			1234567890777
4	20/06/2020			1234567890241
5	20/06/2020			1234567890242
Sample

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.

As all 4 don't match then highlight them all

 

[rhombus4]

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

Please, try the code below.
The cell in column G will remain with the word TRUE for the records that meet your criteria above.

Sub CountPartialMatchesV4()
Dim LR As Long
  LR = Cells(Rows.Count, 3).End(3).Row
  With Range("G2:G" & LR)
   .Value = ""
   .Value = "=AND(SUMPRODUCT(N(LEFT(F$2:F$" & LR & ",10)=LEFT(F2,10)))>1,SUMPRODUCT((C$2:C$" & LR & "=C2)*(LEFT(F$2:F$" & LR & ",10)=LEFT(F2,10)))=1)"
   .Value = .Value
   .Replace "False", ""
  End With
End Sub

And for the Conditional Formatting you could use this formula: (maybe there is a shorter one)

=AND(SUMPRODUCT(N(LEFT($F$2:$F$14,10)=LEFT($F2,10)))>1,SUMPRODUCT(($C$2:$C$14=$C2)*(LEFT($F$2:$F$14,10)=LEFT($F2,10)))=1)

Thanks osvaldo, your code and conditional format worked

Just a quick edit I tried to edit your conditional format for something similar where you don't have to get first 10 digits

=AND(SUMPRODUCT(N(LEFT($F$2:$F$14,10) = LEFT($F2,10)))>1, SUMPRODUCT(($C$2:$C$14 = $C2) * (LEFT($F$2:$F$14,10) = LEFT($F2,10))) = 1)

couldnt get it to work, maybe brackets in the wrong place
=AND(SUMPRODUCT($F$2:$F$14 = $F2)>1, SUMPRODUCT($C$2:$C$14 = $C2) * ($F$2:$F$14) = $F2)) = 1)



and also peters conditional format
=SUMPRODUCT(--(LEFT(F$2:F$14,10)=LEFT(F2,10)),--(C$2:C$14<>C2))
what do i need to edit above so I dont need the Left function

 

[Osvaldo Palmeiro]

new criteria ~~~> without getting the first 10 digits:

for the code ~~~> .Value = "=AND(COUNTIF(F$2:F$" & LR & ",F2)>1,COUNTIFS(C$2:C$" & LR & ",C2,F$2:F$" & LR & ",F2)=1)"
for Conditional Formatting ~~~> =AND(COUNTIF($F$2:$F$14,F2)>1,COUNTIFS(C$2:C$14,C2,F$2:F$14,F2)=1)

But ...
I'm a kind of confused because about Peter's example (post # 11) you commented that "As all 4 don't match then highlight them all", but yet using the criteria of taking the first 10 digits then all 4 IDs related are the same, and as the dates in C2 and C3 are the same and the dates in C4 and C5 also are the same, then my previous suggestion will not highlight any of the records.

What's the criteria? In that example, as the 4 IDs are the same (again, using left function) then all the 4 dates should be the same too, if not then highlight all ?

 

[rhombus4]

I initially wanted the first 10 digits which I still want. I just asked for the formula with the LEFT function for future reference.

Was also interested to get PEters formula without the LEFT function, aslo for future reference
=SUMPRODUCT(--(LEFT(F$2:F$14,10)=LEFT(F2,10)),--(C$2:C$14<>C2))


PS, not sure why you initially used a sumproduct, then a Count if, unless the count if is a better formula than the sumproduct

Your formula in Post 12 worked
=AND(SUMPRODUCT(N(LEFT($F$2:$F$14,10)=LEFT($F2,10)))>1,SUMPRODUCT(($C$2:$C$14=$C2)*(LEFT($F$2:$F$14,10)=LEFT($F2,10)))=1)

This one didnt
=AND(COUNTIF($F$2:$F$14,F2)>1,COUNTIFS(C$2:C$14,C2,F$2:F$14,F2)=1)

e.g.
The first True/False column is your original sumproduct which works
All 3 with 123 have different dates so are highlighted as true
Both with 555 have different dates so again both true
456 has 2 with same date and other 1 with different date and marked as true

The 2nd TRue/False column has your new formula but didnt work.
=AND(COUNTIF($F$2:$F$14,F2)>1,COUNTIFS(C$2:C$14,C2,F$2:F$14,F2)=1)
All 3 with 123 have different dates but only 2 are highlighted(should be all 3)

Both 555 have different dates. 1 is True and 1 if False(Both should be True)

456 one seemed to work ok. 2 with same and 1 with different it the True one

Top 3 have same ID but different dates so all are true

20/06/2020			123	TRUE​		TRUE​
18/06/2020			123	TRUE​		TRUE​
23/06/2020			123	TRUE​		FALSE​
19/06/2020			555	TRUE​		TRUE​
20/06/2020			555	TRUE​		FALSE​
18/06/2020			456	TRUE​		TRUE​
19/06/2020			456	FALSE​		FALSE​
19/06/2020			456	FALSE​		FALSE​

As All ID's should have the same Date, I want to highlight the one/s which are most likely an issue, therefore:
If 2 with Same ID but different dates, highlight both
If 3 with Same ID but all have different dates, highlight all 3
If 3 with Same ID but 2 have same date and 1 different, highlight the one with different date
If 4 with Same ID but all have different dates, highlight all 4
If 4 with Same ID but 3 have same date and 1 different, highlight the one with different date
If 4 with Same ID but 2 ID's have same date and other 2 ID's have a different but same date, highlight all 4. i.e. 2 on 18th and other 2 on 20th
Same applies for 5 and above.

 

[rhombus4]

How do I edit these 2 formulas to remove the left function

=AND(SUMPRODUCT(N(LEFT($F$2:$F$14,10)=LEFT($F2,10)))>1,SUMPRODUCT(($C$2:$C$14=$C2)*(LEFT($F$2:$F$14,10)=LEFT($F2,10)))=1)
=SUMPRODUCT(--(LEFT(F$2:F$14,10)=LEFT(F2,10)),--(C$2:C$14<>C2))

 

[Osvaldo Palmeiro]

The first True/False column is your original sumproduct which works

The 2nd TRue/False column has your new formula but didnt work.

Both, the formula and the code line I've posted in post #15, show for me exactly the same result your screen shot shows in first column.
Maybe you could upload your file to somewhere and then provide here the link to it.

 

[rhombus4]

Tried it again with different IDs and still get different results

Date			ID		=AND(SUMPRODUCT(N(LEFT($F$2:$F$14,10) = LEFT($F2,10)))>1, SUMPRODUCT(($C$2:$C$14 = $C2) * (LEFT($F$2:$F$14,10) = LEFT($F2,10))) = 1)	=AND(COUNTIF($F2:$F14,F2)>1, COUNTIFS($C$2:$C$14,C2, $F$2:$F$14,F2)=1)
20/06/2020​			123		TRUE​	TRUE​
18/06/2020​			123		TRUE​	TRUE​
23/06/2020​			123		TRUE​	FALSE​
19/06/2020​			555		TRUE​	TRUE​
20/06/2020​			555		TRUE​	FALSE​
18/06/2020​			456		TRUE​	TRUE​
19/06/2020​			456		FALSE​	FALSE​
19/06/2020​			456		FALSE​	FALSE​
19/06/2020​			456		FALSE​	FALSE​
23/06/2020​			987		FALSE​	FALSE​
20/06/2020​			155555555555		FALSE​	FALSE​
20/06/2020​			155555555550		FALSE​	FALSE​
20/06/2020​			456		TRUE​	FALSE​