Count Repeated Numbers

[bdtran]

Hello All,

I need help with a formula to count the repeating for each number in multiple columns as shown the table in data range A1:X1.
The result that I am looking for is as in the range Z1:AE1 where the number with higher repeating count on the left. For example Z1=5-3 where #3 with higher repeating count of five times in the data range.

Thank you for all the helps.
BDT

	A	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
	1	25	1	2			2	2	3	45			3	3	3	3	25				45		45	9	Result >	5-3	3-2	3-45	2-1	2-25	1-9

<colgroup><col><col><col><col><col span="2"><col span="2"><col><col><col><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

A tweak to Aryatect's formula to make it slightly shorter:

=IFERROR(AGGREGATE(14,6,(COUNTIF($A$1:$X$1,$A$1:$X$1)&"."&$A$1:$X$1)/(MATCH($A$1:$X$1,$A$1:$X$1,0)=COLUMN($A$1:$X$1)-COLUMN($A$1)+1),COLUMNS($Z1:Z1)),"")

 

[bdtran]

Aryatect,

Yes, it worked. I got the exactly result that I am looking for.

Thanks so much, you saved me a lot of time with this formula. I really appreciate it.

BDT

 

[bdtran]

Eric,

That is awesome! I tested your version and it worked with the exactly result.

Thank you for your help! very much appreciated.

BDT

 

[Eric W]

Glad we could help!

This was a fun one. Multiple people working on multiple formulas to get one that works for you.

 

[Aryatect]

Glad we could help!

This was a fun one. Multiple people working on multiple formulas to get one that works for you.

Glad to help and yes this was fun