Repeat Digits Formula

[ststern45]

Hi Everyone,

I'm looking for a formula that will calculate how many digits 0,1,2,3 repeated from the previous range.

For example,
Range Y1:AC1 contains the following:
Y1= 2
Z1= 3
AA1=1
AB1=3
AC1=1

We have the values 2,3,1,3,1 in cell range Y1:AC1

In cell range Y2:AC2 contains the following:
Y2= 0
Z2= 2
AA2=3
AB2=2
AC2=2

We have the values 0,2,3,2,2 in cell range Y2:AC2

I want to calculate total digits from cell range Y2:AC2 that appear in Y1:AC1.

Note: if a digit appears more that once only count that digit once.


The 2 appears in cell range Y1:AC1 but appears in cell range Y2:AC2 3 times but we would only cont this as 1
The digit 3 appears in cell range Y1:AC1 twice and once in cell range Y2:AC2 so we would count this as 1

The final calculation would equal 2.

Thank you in advance!!

 

[kweaver]

Just tested a few other options and I don't think the info below actually does work! SIGH.

I used helper columns in AD1 and put this array formula (CTRL+SHIFT+Enter) and filled across to AH1: =IFERROR(INDEX(hlist,MATCH(0,COUNTIF($X$1:X1,hlist),0)),"")
The range name hlist is Y2:AC2 and this assumes there is nothing in column X.
When this is filled across, you'll get the unique values from Y1:AC1 (i.e., 2, 3, 1)

Then, in AD2 and filled across to AH2: =IF(ISNA(HLOOKUP(AD1,$Y$2:$AC$2,1,0)),0,1)

The result you're looking for is the sum of AD2:AH2

There might be an easier way, possibly using FREQUENCY, but I haven't figured out what that might be. At least this seems to work.

 

[kweaver]

The issue is finding the UNIQUE values from column Y to AC. I found another way. Microsoft is supposed to be releasing a UNIQUE function for 365 users but I don't think they have as yet.
First, I inserted a row 1 so that the values you indicate are in row 2 and row 3.
I then selected AI2:AI6 (new helper column) and entered this array formula: =TRANSPOSE(Y2:AC2)
Then, in AJ2, another array formula that's filled down: =IFERROR(INDEX(list,MATCH(0,COUNTIF($AJ$1:AJ1,list),0)),"")
Then, in AD2:AH2, another transpose array formula: =TRANSPOSE(AJ2:AJ6)
AD3 and filled across: =IF(ISNA(HLOOKUP(AD2,$Y$3:$AC$3,1,0)),0,1)

The sum of AD3:AH3 should be your answer. Phew!

There MUST be an easier way. Probably a VBA solution, but I'm going to bed now.

 

[ststern45]

Thankyou for all your help. Very much appreciated!!