• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparison of the two lines

bines53

Active Member
Hello ,

I have two rows of data, each row also have similar data.
I need to find the number of matches that the two lines
For example :

7,7,K,A,8,7
K,6,7,7,A,9

The solution in this example ,4

Thank you !
 
Assuming the first two lines are in rows 1 and 2, in A3:
=1*(COUNTIF(1:1,A2)>=COUNTIF($A2:A2,A2))
Copy to the right as needed. Then total count is:
=SUM(3:3)
 
Hello,luke

I did not understand your solution, attached

Thank you !
 

Attachments

  • test 555.xlsx
    8.1 KB · Views: 16
One more thing i just notice.. name of the sheet.. :confused:

are you using any other language of excel version..
 
Hi Debraj,

I appreciate the desire to help
If easier to work only on numbers, there is no problem with it
Can I convert numbers to letters, even if there is a need to work with lots of rows.

Thank you !
 
Hi bines53,
Responding to your message, I'm not sure what you meant by "combine all 6 options". The orginal formula setup I posted should work with both numbers and text, so there is no issue there. Is there something else you need/wanted the formulas to do?
 
Hi luke M ,

As I mentioned at the beginning "Copy to the right as needed. Then total count is "
I attached file


Thank you !
 

Attachments

  • TEST 88888.xlsx
    8.3 KB · Views: 12
@Debraj
One problem I see with your formula is that is line 2 has more entries of a number than line 1, the formula counts all of them. From the example, if there were 2 7's in the top, and 3 7's in the bottom, the answer should only be 2.
I think the challenge is trying to figure out how to limit the matches. :(
 
@Luke M ,

Hi.. thanks for the catch.. :(

Let see this time.. :)

=SUM(IF(COUNTIF(A2:F2, A3:F3)>0, 1, 0))
 

Attachments

  • TEST 88888.xlsx
    8.5 KB · Views: 5
Change D3 from 7 to A. This should cause the count to go down to 3 (since we only have 1 A in the top row), but formula gives us 4.
Please don't think I'm picking on you Deb, I'm as stumped as you are. This is certainly an interesting challenge. Feels like it should be easy, but can't figure it out! :mad:
 
@Luke M ,

This time Sum of Match of Unique Data.. :(
I know this time i failed to check 2 nd occurance.. still just sharing.. :(

=SUM(IF(FREQUENCY(COUNTIF(list2,"<"&list2),COUNTIF(list2,"<"&list2))>0,1,0))
 
@bines53
I'm not sure it's possible to do this with a single formula (and not use an UDF). Is there a very strong need to not use helper cells?
 
I don't think, in this scneario, the numbers vs. text is a problem. I admit I don't know how to make XL only look at part of a range for an array. I tried doing:
INDEX(A2:F2,COLUMN(A2:F2))
hoping that the COLUMN function would generate an array, and then cause the INDEX to give me 6 differently sized arrays, but alas, it does not. Thinking back to the XL challenges forum, perhaps @Haseeb A or @jeffreyweir or @Hui can think up of something. I do not fully understand the "dark arts" or using MMULT and FREQUENCY, perhaps there is a way with those function? I'm sorry I can't be of more help. :(
 
Back
Top