CountUnique in large range but per row?

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
534
Office Version
  1. 365
Platform
  1. Windows
hi,
any way to implment this formula =CountUnique(A2:D2,A3:D3)
(right now the formula check how many repeats there is but only against one row)
but in a larger range like A2:D2, A3:D1000
but to check each 4 cells individual, like a4:d4 if the result is 4 (and only 4) repeating numbers, in any order,
and then a5:d5 and so on,
and returns only the number of times when the result is 4?

in simpler words.... looking for is how many full combinations (meaning 4 numbers repeats) of 2-7-7-8 (example only) there's in a range of 1000 rows, but checking it per row?

maybe using the column helper by perterSS ?
=SMALL(MID(B2,{1,2,3,4},1)+0,1)&SMALL(MID(B2,{1,2,3,4},1)+0,2)&SMALL(MID(B2,{1,2,3,4},1)+0,3)&SMALL(MID(B2,{1,2,3,4},1)+0,4)
even try it myself when q2 is the helper, but for some reason it didn't worked
=IF(COUNTIF(q$1:q1,B2)=0,IF(COUNTIF($q$2:$q$10000,b2)=1,0,COUNTIF($q$2:$q$10000,b2)),"-")

example:

q.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1-1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20how many time 4's
2877214133
3846411011
477821341
532241111
675273011
772871123
886441101
931671111
1083821221
1157721300
1258811000
1377820000
1443460000
test
Cell Formulas
RangeFormula
E2:E14E2=CountUnique(A2:D2,A3:D3)
F2:F14F2=CountUnique(A2:D2,A4:D4)
G2:G14G2=CountUnique(A2:D2,A5:D5)
H2:H14H2=CountUnique(A2:D2,A6:D6)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Y1:Y3Cell Value=4textNO



side question:
when pressing ctrl+shift+down for selecting entire column, for instance in column b,
while col a has 1000 rows,
sometimes it goes down and select up to a million rows!
and i don't have any blank rows
why is that?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top