Forum Discussion

rohithvr's avatar
rohithvr
Copper Contributor
Nov 12, 2023

How to Display Repeated Numbers First Digit from B2 to S2 in U2 Cell?

I have a Excel Sheet containing 2 digit numbers in B2 to S2. Now I want to display the First digit of each repeated number ( if they repeat 2 or more times only ) with parenthesis In U2 cell. Ex: If ...
Excel
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 12, 2023

rohithvr 

 

 

in U2:

 

 

=LET(
  FirstDigit, TOCOL(--LEFT(B2:S2,1)),
  Duplicates, FILTER(FirstDigit, DROP(FREQUENCY(FirstDigit,FirstDigit) > 1, -1)),
  Filtered,   FILTER(FirstDigit, IFNA(XMATCH(FirstDigit,Duplicates),0)),
  Frequency,  DROP(FREQUENCY(Filtered,Duplicates), -1),
  TEXTJOIN(", ",, Duplicates & "(" & Frequency & ")")
)

 

 

Alternative:

 

 

=LET(
  FirstDigit,  TOCOL(--LEFT(B2:S2,1)),
  Duplicates,  FILTER(FirstDigit, DROP(FREQUENCY(FirstDigit,FirstDigit) > 1, -1)),
  JoinDupFreq, LAMBDA(dup, dup & "(" & ROWS(FILTER(FirstDigit,FirstDigit=dup)) & ")"),
  REDUCE("",Duplicates,
    LAMBDA(seed,dup, TEXTJOIN(", ",,seed, JoinDupFreq(dup)))
  )
)

 

Alternative:

 

=LET(
  FirstDigit, TOCOL(--LEFT(B2:S2,1)),
  Pos,        XMATCH(FirstDigit,FirstDigit),
  Frequency,  DROP(FREQUENCY(Pos,Pos),-1),
  IsDup,      Frequency > 1,
  TEXTJOIN(", ",,FILTER(FirstDigit,IsDup) & "(" & FILTER(Frequency,IsDup) & ")")
)

Alternative:

 

=LET(
  FirstDigit, --LEFT(B2:S2,1),
  Frequency,  TRANSPOSE(DROP(FREQUENCY(FirstDigit,FirstDigit),-1)),
  IsDup,      Frequency > 1,
  TEXTJOIN(", ",,BYCOL(FILTER(VSTACK(FirstDigit,Frequency),IsDup), LAMBDA(col, TEXTJOIN("(",,col) & ")")))
)

 

 

 

Resources