Count duplicate sets of 2 numbers
Hi Experts,
I have 7 numbers in columns A:G and would like to count how many duplicate sets of 2 numbers are there.
For instance, the result of (2 0 2 6 1 0 2) should be 2 because there are only 2 same set of duplicates (02 & 02).
Appreciate if someone could provide me a formula, I have attached the sample also for easy reference.
Thanks
Please try
=--TEXT(MAX(MMULT(COLUMN(A2:F2)^0,--(A2:F2&B2:G2=TRANSPOSE(A2:F2&B2:G2)))/(1+(A2:F2=B2:G2))),"[>1]0;\0")
Confirm with Ctrl+Shift+Enter
Bo_Ry... that looks awfully clever... even if I do not understand how it produces the correct answer with this string
8 8 3 7 3 7 3
which could potentially return 2x37s or 2x73s....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Bo_Ry,
Working perfectly thanks a lot.
what if I want to count different set of duplicate numbers like the result of (1 1 2 2 0 0 1) should be 3 because there are 3 different 2 set of numbers (11 & 22 & 00)
thanks in advance
Why 11 22 and 00 and not 12, 20 & 01, as your two digit numbers seem to be able to start either from column A or column B. In any event, isn't the answer to your last Q ALWAYS going to be 3, irrespective of the digits present, as there are 7 columns.
=INT(COLUMNS(A2:G2)/2)
Thanks, Glenn.
8 8 3 7 3 7 7
A2:F2&B2:G2=TRANSPOSE(A2:F2&B2:G2)
This one check all "88","83","37","73","37","77"
37 shows 2 times
@Gulya
count different set of duplicate numbers
=MIN(3,SUM(--(A2:F2=B2:G2)))
Thanks Bo_Ry, as expected both formulas are working really amazing
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks