HI Excel Hero!
I am using this formula to count repetition of 2 numbers in a single cell at least 3 times, but some time it gives wrong result i want to know why it gives wrong result? What is best solution to avoid wrong result.
Formula that i am using is
=MAX(FREQUENCY(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2),0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2)))>=3
see attachment
It seems to me your question has been answered ...
Can you explain your latest message # 7 ...
that's work but i checked manually in some cells there are only two match value and it gives true. plz check the file row 102
Display MoreOr,
it can be shortened to :
=MAX(FREQUENCY(0+MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),0+MID(A2,ROW(INDIRECT("1:"&LEN(A2)))>3
hello there a problem in the formula. it gives true if there are 2 same values 2 times.
etc if in cell a1 are 758575668533 it should not give true bcz there are 2 same values not three 75 and 85 both are 2 times but i want only if one value are 3 times.
Display MoreOr,
it can be shortened to :
=MAX(FREQUENCY(0+MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),0+MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2)))>=3
Regards
wow. it works. thanks man you are genius. thanks again.
no sir its new question. now i need three same values in a cell. check the file.
Hi Friends,
I have a large data of employee i want to find out any repeated 3 times repeated values.
if cell A1 has 859587585151185. value should be true bcz it has 85 3 times. my file is attached.
There are total 8 digits in the cell
For example if in cell a1 11223344
And in cell a2 00112233 and in cell a3 44112277 and in cell a4 88001122
The answer should be combination 3
bcz combination of 112233 are in cell a1 and in cell a2 combination off 001122 are in cell a2 and a4 and combination of 114422 are in a1 and a3. It will be plus point if it tell which combination are there
I need at least combination of 3 numbers in a single cell but combination can be different. If a cell has 2244556677 it can be 225566 or 224477 or 772255 or all of these combination.
see attachment what i want to.
yes brother, Is it possible to find out which 3 numbers are matched and how many times its matched?
I want to count only 1 combination of 3 number etc if a1 has ( 11 22 33 44 55 66 ) and a5 has 33 44 55 it should bet 1 but if any other cell has 33 66 22 then this combination should not 2 bcz it has difference combination numbers.
i want to find how many times only 33 44 55 has in others cells and how many times 33 66 22 has in other cells.
First of all thanks for your quick reply
the solution you provided is working but
what if any other 3 number combination matched and its only counts a1 cell numbers, but i want to match all columns. see attachment.
I think there are at least more than 500 different numbers combination matched bcz i have 10k employee codes.
Hi Friends,
sheet is attached.
I'm new in excel and need help, i have 10k rows that have codes of employee i want to highlight or count in how many cells have at least 3 common numbers,
for example 89..19...08 are in 3 rows. I want a formula that tell how many matched cells are there.
