Comparing values
 

Hi,

I am a newbie with this so please forgive me if there is an easy
soloution for this.

I have two rows of data, I want to compare these two rows and count how
many numbers within these two rows match and return a count. Can anyone
possibly give me a bit of advice on how to do this?

Thanks
Owen

Comparing values
 

skrev i en meddelelse
ups.com...
Hi,

I am a newbie with this so please forgive me if there is an easy
soloution for this.

I have two rows of data, I want to compare these two rows and count how
many numbers within these two rows match and return a count. Can anyone
possibly give me a bit of advice on how to do this?

Thanks
Owen


Hi Owen

One way.

Takes into account duplicates in B1:K1 in itself, duplicates in C2:J2 in
itselft
and values in B1:K1 also found in C2:J2:

=SUMPRODUCT((FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2) )1)*(FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2))))


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Comparing values
 

Leo, Many thanks. It worked perfectly except it returned double the
count I was looking for as it counted in the numbers from the original
row as well so I just divided it by 2.

Cheers!

Leo Heuser wrote:

skrev i en meddelelse
ups.com...
Hi,

I am a newbie with this so please forgive me if there is an easy
soloution for this.

I have two rows of data, I want to compare these two rows and count how
many numbers within these two rows match and return a count. Can anyone
possibly give me a bit of advice on how to do this?

Thanks
Owen


Hi Owen

One way.

Takes into account duplicates in B1:K1 in itself, duplicates in C2:J2 in
itselft
and values in B1:K1 also found in C2:J2:

=SUMPRODUCT((FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2) )1)*(FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2))))


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Comparing values
 

"OBK" skrev i en meddelelse
ups.com...
Leo, Many thanks. It worked perfectly except it returned double the
count I was looking for as it counted in the numbers from the original
row as well so I just divided it by 2.

Cheers!


You're welcome Owen.
Thanks for the feedback.

Maybe this formula is all you need:

=SUMPRODUCT((FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2) )1)+0)

Leo Heuser



Leo Heuser wrote:

skrev i en meddelelse
ups.com...
Hi,

I am a newbie with this so please forgive me if there is an easy
soloution for this.

I have two rows of data, I want to compare these two rows and count how
many numbers within these two rows match and return a count. Can anyone
possibly give me a bit of advice on how to do this?

Thanks
Owen


Hi Owen

One way.

Takes into account duplicates in B1:K1 in itself, duplicates in C2:J2 in
itselft
and values in B1:K1 also found in C2:J2:

=SUMPRODUCT((FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2) )1)*(FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2))))


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Comparing values
 

Yep, thats exactly it.

Thanks
Owen

Leo Heuser wrote:

"OBK" skrev i en meddelelse
ups.com...
Leo, Many thanks. It worked perfectly except it returned double the
count I was looking for as it counted in the numbers from the original
row as well so I just divided it by 2.

Cheers!


You're welcome Owen.
Thanks for the feedback.

Maybe this formula is all you need:

=SUMPRODUCT((FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2) )1)+0)

Leo Heuser



Leo Heuser wrote:

skrev i en meddelelse
ups.com...
Hi,

I am a newbie with this so please forgive me if there is an easy
soloution for this.

I have two rows of data, I want to compare these two rows and count how
many numbers within these two rows match and return a count. Can anyone
possibly give me a bit of advice on how to do this?

Thanks
Owen


Hi Owen

One way.

Takes into account duplicates in B1:K1 in itself, duplicates in C2:J2 in
itselft
and values in B1:K1 also found in C2:J2:

=SUMPRODUCT((FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2) )1)*(FREQUENCY((B1:K1,C2:J2),(B1:K1,C2:J2))))


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Comparing values
 

"OBK" skrev i en meddelelse
ups.com...
Yep, thats exactly it.

Thanks
Owen


Glad to help :-)

Leo Heuser