How to find repeated or most common numbers from rows?

Good afternoon,
would you kindly assist with formula to find most common or repeated numbers in rows from "A:F"?
here is an example;

Please Login or Register  to view this content.

Thanks
Sem

Is each group of 5 numbers contained in the same cell?

can you post sample file

I used text to columns to get each value in its own cell. This gave an array A2:AJ4. Next select AN2:AM2 and enter this array formula: (enter with Ctrl + Shift + Enter)

Formula:

Please Login or Register  to view this content.

Result:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	A						B						C						D						E						F							EXPECTED	RESULT
2	1	4	5	13	16		2	6	13	16	20		3	7	9	13	16		8	10	12	13	16		11	13	15	16	17		13	14	16	18	19			13	16
3	1	2	3	12	15		1	4	9	12	19		1	5	12	18	20		1	6	12	16	17		1	7	8	12	14		1	10	11	12	13			1	12
4	1	5	9	14	17		2	3	5	9	18		4	5	6	9	12		5	7	9	16	19		5	8	9	10	11		5	9	13	15	20			5	9

I don't see how this can be done keeping the groupings in one cell for each group unless it can be done with VBA.

Originally Posted by newdoverman

I don't see how this can be done keeping the groupings in one cell for each group unless it can be done with VBA.

I concur with this, VBA programming would be needed for this. If you're OK with VBA, I could create a User-Defined-Function that would do this and you could install it into your workbook(s) as needed. Not everyone wants/allows macros in their projects. Let me know.

see the attached file

see the attached file
it is an array formula

Hello Sem,

What version of excel are you using 2007 or above?

So, you only want to return MULTIPLE most common numbers, if they are occurring equal time?

eg: 13 & 16 occur 6 times in row 1, so if they occur 6 & 5 time, do you only want to return 13?


Edit: If you are on Excel 2010 or above,

G1, then copy across until get error.

=INDEX(MODE.MULT(TRIM(MID(SUBSTITUTE($A1:$F1," ",REPT(" ",20)),{0;1;2;3;4}*20+1,20))+0),COLUMNS($G1:G1))

OK, try this

G1,

=MODE(TRIM(MID(SUBSTITUTE(A1:F1," ",REPT(" ",20)),{0;1;2;3;4}*20+1,20))+0)

H1,

=MODE(IFERROR(TEXT(TRIM(MID(SUBSTITUTE(A1:F1," ",REPT(" ",20)),{0;1;2;3;4}*20+1,20))+0,"[<>"&G1&"]0;-")+0,""))

Both are Array Formulas, must be conformed with CTRL+SHIFT+ENTER

Formula for getting 1st number and 2nd number are not same

Originally Posted by sem

..thanks very very much...

You are welcome. Glad it works for you :-)

...what is the "20" and "0,1,2,3,4" stand for...

0;1;2;3;4 means you have data across in 5 columns. A:F. If you have more columns change it accordingly.

20 is just used to separate these values by 20 spaces to get values in array.

....suppose my data's numser over 20 is that mean i need to change the "20" in the formula accordingly?...

No. Only if you have large numbers >19 character.

Sem,

My bad.

{0;1;2;3;4} means you have 5 group of numbers in a cell. So just keep it as it is. No need {0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}

Sorry for the confusion.