| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparing values
"OBK" skrev i en meddelelse
ups.com... Yep, thats exactly it. Thanks Owen Glad to help :-) Leo Heuser |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with Comparing values and retrieving values in Excel!!!!!! | Excel Worksheet Functions | |||
| Comparing values between columns only when there are values in bot | Excel Worksheet Functions | |||
| Comparing values in two columns and displaying missing values in n | Excel Programming | |||
| comparing two values | Excel Worksheet Functions | |||
| Comparing Values | Excel Programming | |||
Linear Mode
