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]

While I probably could come up with a formula that presents the results as requested, it's usually better and easier to separate out the 2 pieces of information into separate cells. Put this formula in Z1

=MODE(IF($A$1:$X$1<>"",IF(COUNTIF($Y$1:Y$1,$A$1:$X$1)=0,$A$1:$X$1*{1;1})))

confirm with Control+Shift+Enter, then drag to the right. Now put this formula in Z2:

=COUNTIF($A$1:$X$1,Z1)

and drag to the right. Let me know if this works, or if you really want a combined formula.

 

[bdtran]

Eric,

I tested your formulas and both formulas are working into separate cells as mentioned.

However, I really need in one formula because later, I will need to calculate another result from on this result.

Thanks much for your helps.
BDT

 

[Eric W]

OK, try this array formula in Z1:

=TEXT(LARGE(IF($A$1:$X$1<>"",IF(MATCH($A$1:$X$1,$A$1:$X$1,0)=COLUMN($A$1:$X$1)-COLUMN($A$1)+1,COUNTIF($A$1:$X$1,$A$1:$X$1)*[COLOR=#ff0000]1000[/COLOR]+$A$1:$X$1)),COLUMNS($Z1:Z1)),"0-[COLOR=#ff0000]000[/COLOR]")

Confirm with Control+Shift+Enter. This should work for positive integer values under 1000. If you have integer values over that, increase the number of 0s in red. If you have non-integer values, negative values, or if you want to get rid of the leading zeros, the formula will get even more complicated. Let me know how this works for you.

 

[bdtran]

Eric,

Thank you so much for the helps.

I got the expected result from your formula but the format of number could cause the problem for my next round of calculation.

The result format from your formula is as below:

5-003 3-045 3-002 2-025 2-001

Is there a way that I can get the result in this format instead as following:

5.3 3.45 3.2 2.25 2.1

Very much appreciated for your help in this problem!
BDT

 

[Rick Rothstein]

Is there a way that I can get the result in this format instead as following:

5.3 3.45 3.2 2.25 2.1

Try changing this...

"0-000"

at the end of Eric's formula in Message #4 to this...

"0\.0"

 

[bdtran]

Mr. Rothstein,

I changed the format to "0\.0" as mentioned and got the result in the format below:

500.3 304.5 300.2 202.5 200.1

Thanks much.
BDT

 

[Aryatect]

Hi,

Just a little change to @Eric W's awesome formula to enter without Ctrl+Shift+Enter:

=IFERROR(TEXT(AGGREGATE(14,6,COUNTIF($A$1:$X$1,$A$1:$X$1)*1000+$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)),"0\.000"),"")

 

[bdtran]

Aryatect,

Thank you, I tested your formula and got the result in the format as below:

5.003 3.045 3.002 2.025 2.001

I tried to change several custom number formats in "0\.000" but could not get the result in the format I am looking for as following

5.3 3.45 3.2 2.25 2.1

I really appreciate your help.
BDT

 

[Aryatect]

Hi, This should work:

=IFERROR(AGGREGATE(14,6,COUNTIF($A$1:$X$1,$A$1:$X$1)+$A$1:$X$1/10^LEN($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)),"")