Conditional Formatting - Duplicates, any order?

[glowtraq]

Hi, I'm a beginner to Excel 2010, I use the compare feature which is helpful, however it doesn't compare the same digits if they are in a different order within each cells. Is there a way to compare two columns and show if cells match in values regardless of their order and show the quantity matched?

For example, for column [labeled X] 120 values, compare this to column [labeled Y] 114 values. I searched as much as possible, however I'm a bit confused how to approach this, can someone help me?

I would also like to do this for 4 digits as well.

5-28-2016 2-36-59 AM.jpg

[zbor]

Need to separate numbers so if only 3 numbers this could be solution:

Please Login or Register  to view this content.

Edit: Confirmed with ctrl+shift+enter.

Then use CF in this Column

[glowtraq]

Thank you, when you say separate, do you mean to take column x or y and split it up using "Text to Columns"?

I'm sorry, I'm very new to this, what do you mean by Confirmed with ctrl+****+enter? For some reason I have to always format the cells as text when I copy them into Excel, will this affect the formulas?

For the formula you posted, I tried this on column B, however it doesn't seem to be yielding the correct results.

5-28-2016 12-07-02 PM.jpg

[glowtraq]

Ok, so I tried splitting up the first column and values did appear in the column with the formula, however they don't seem to be accurate.

5-28-2016 12-11-25 PM.jpg 5-28-2016 12-14-27 PM.jpg

[zbor]

LoL, sorry...
I made typo so instead of shift it was autocorrected.

It should be CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer, like now.

Here is attached workbook.

Also, 024 should give result 3 because you have 042 in cell C76.

[glowtraq]

zbor thank you, you're awesome! I'm going to test this out a bit more. If I wanted to compare 4 digits in column x against 4 digits in column y, would the formula have to be adjusted?

[zbor]

Just add one more criteria:

Please Login or Register  to view this content.

[newdoverman]

This appears to work. What I have done is to take each 3 digit number in column A and put them in all the different orders other than the starting order and search for them in column C.

Please Login or Register  to view this content.

The values in column C that match your example of 024 in column A in column C are: 204, 402, 042.

[glowtraq]

Thank you NDM and zbor, I noticed something strange in the formulas for both sheets with the 3 digit numbers - I tried to replace the values in column A with a new set of 3 digit numbers, which seems to break the count. The difference with the new 3 digit numbers is that they contain double digits.

Examples of digits with double numbers

001
002
003
004
005
006
007
008
009
011
022
033
044
055
066
077
088
099
112
113
114
115
116

[newdoverman]

This appears to work. It compares the 3 digits in column A cells with each other and if there is a match (double) then the count is divided by 2.

Please Login or Register  to view this content.

[zbor]

Another approach in attachment.

It's complicated but I found difference in case all 3 numbers are same.
I think mine should work so newdoverman please check.

[Phuocam]

Or try this ...

=IF(MOD(A2,111)=0,COUNTIF($C$2:$C$115,A2),SUMPRODUCT((ISNUMBER(SEARCH(MID(A2,1,1),$C$2:$C$115)+SEARCH(MID(A2,2,1),$C$2:$C$115)+SEARCH(MID(A2,3,1),$C$2:$C$115)))*(MID(A2,1,1)+MID(A2,2,1)+MID(A2,3,1)=MID($C$2:$C$115,1,1)+MID($C$2:$C$115,2,1)+MID($C$2:$C$115,3,1)))) .

[newdoverman]

For 3 identical digits forming a number, this should do it.

Please Login or Register  to view this content.

The check for contents in column A is necessary because the values in column C extending past the last value in column A have already been counted.

[newdoverman]

@zbor
I amended my formula to accommodate 3 identical digits in the number and the results are identical to your results. Here is your workbook with the side by side comparison.

[glowtraq]

zbor, I was testing the formula for 4 digit numbers, however when I pasted the static data into column A and the non static data into column c, the count results appeared off.

[newdoverman]

Seeing that the requirements for this are constantly changing, I'm out.

[glowtraq]

My apologies ndm, I should've updated the requirements, zbor did post a formula for 4 digits yesterday, I should've tested it earlier. In a nutshell, I'm looking for variations in 4 digits and 3 digits only. The amount of digits will not go past this.

[newdoverman]

No apologies necessary. I think zbor is probably onto the best solution technique.

[Phuocam]

Try again ...

=SUMPRODUCT((ISNUMBER(SEARCH(MID(A2,1,1),$C$2:$C$115)+SEARCH(MID(A2,2,1),$C$2:$C$115)+SEARCH(MID(A2,3,1),$C$2:$C$115)+SEARCH(MID(A2,4,1),$C$2:$C$115)))
*ISNUMBER(SEARCH(MID($C$2:$C$115,1,1),A2)+SEARCH(MID($C$2:$C$115,2,1),A2)+SEARCH(MID($C$2:$C$115,3,1),A2)+SEARCH(MID($C$2:$C$115,4,1),A2))
*(MID(A2,1,1)+MID(A2,2,1)+MID(A2,3,1)+MID(A2,4,1)=MID($C$2:$C$115,1,1)+MID($C$2:$C$115,2,1)+MID($C$2:$C$115,3,1)+MID($C$2:$C$115,4,1)))

[glowtraq]

Hi guys, thanks for helping me with this, sorry I have been away. I'm still trying to fix the workbook with 4 digits matching numbers to work, can't seem to. Phuocam, I tried your formula however it's not working, for example, matching digits 0148 shows a match of 2 times, however this is not the case in column c. I've attached the sheet with Phuocam's formula. Also each time I tried to extend the column search depth to 200 from 115, the formula breaks.

I've attached my workbook, can someone help me with this?