CODEWITHSUNDEEP
excel
user761065
user761065

Reputation: 123

Counting the number of matches between two ranges

I am seeking a formula to count the number of matching numbers between two range of cells. I want to explain with examples. It can be confusing.

A1:E1={1,2,3,4,5} and A2:E2={2,1,3,5,4}: here two groups have the same numbers, so there are 5 matches. (Order is not important)

A1:E1={1,1,1,1,1} and A2:E2={1,2,1,1,1}: here two groups have four 1s, so it is 4 matches.

A1:E1={2,2,1,1,1} and A2:E2={2,2,2,1,1}: here two groups have two 2s, and two 1s matching, so it is 4 matches in total.

A1:E1= {1,4,4,3,7} and A2:E2={4,3,5,6,1}, in this case both groups have one 4, one 1, one 3. So it is 3 matches in total.

Please let me know if anything is unclear. The challenging part here is repetitions.

Upvotes: 1

Views: 1026

Answers (4)

Tom Sharpe
Tom Sharpe

Reputation: 34370

Well I just know someone will do this far more concisely but this does work

=SUM(IF(FREQUENCY(A1:E1,N(OFFSET(A1,ROW(A1:E2)-ROW(A1),COLUMN(A1:E2)-COLUMN(A1),1,1)))<
FREQUENCY(A2:E2,N(OFFSET(A1,ROW(A1:E2)-ROW(A1),COLUMN(A1:E2)-COLUMN(A1),1,1))),
FREQUENCY(A1:E1,N(OFFSET(A1,ROW(A1:E2)-ROW(A1),COLUMN(A1:E2)-COLUMN(A1),1,1))),
FREQUENCY(A2:E2,N(OFFSET(A1,ROW(A1:E2)-ROW(A1),COLUMN(A1:E2)-COLUMN(A1),1,1)))))

The idea being that you combine the two lists using offset, then compare the frequencies. The lower frequency is the one that will be taken.

enter image description here

Have added an extra case - the first four give the correct answer if you just sort the two lists and compare them but the last one doesn't.

Upvotes: 3

ian0411
ian0411

Reputation: 4275

Not sure if this will solve your problem, but this array formula (click Ctrl + Shift + Enter together) will get the same results as you are stating here:

=SUMPRODUCT(--(SMALL($A2:$E2,COLUMN($A2:$E2))=SMALL($A1:$E1,COLUMN($A1:$E1))))

What I did is to rank both rows and count how many are matching. But let me know if this will cover all the scenarios you have or not.

Upvotes: 0

Kresimir L.
Kresimir L.

Reputation: 2441

This can be done, but it requires few steps. It will only work if matching values are from 0-9. first, enter this formula =CONCATENATE(A2,B2,C2,D2,E2) in cell I2 and drag it to the bottom. enter image description here

then enter this formula =SUBSTITUTE(I2,A1,"",1) in cell J2 and drag it to column N and to the bottom.

enter image description here

Lastly, enter this formula =5-LEN(N2) in cell G2 and copy/paste it beside searching values.

enter image description here

Upvotes: 0

PeterH
PeterH

Reputation: 1040

try the two attempts below:

{=SUM(COUNTIF(A1:A5,B1:B5))}

Or

{=SUM(IF(COUNTIF(A1:A5,B1:B5)=1,1,0))+SUM(IF(COUNTIF(A1:A5,B1:B5)>1,1,0))}

Both need entering as an array using Ctrl + Shift + Enter

Upvotes: 0

Related Questions