Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper Contributor

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 B2, C2, D2 containing numbers 21, 26, 28, I want to display that in U2 cell like 2 (3). is it possible? I have tried some formula, but it showing the repeated numbers along with how many time they repeat.

screenshot_495.png

3 Replies

@rohithvr Give a try to the following formula-

=LET(x,LEFT(I2:R2,1),y,UNIQUE(LEFT(I2:R2,1),TRUE),z,MAP(y,LAMBDA(t,SUM(--(x=t)))),TEXTJOIN(", ",1,y&" ("&z&")"))

Harun24HR_0-1699784203712.png

 

 

@rohithvr 

 

Sample.png

 

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) & ")")))
)

 

 

 

@rohithvr 

 

Hi,

 

I think you can first add a helper column to get "first digit", and then add another column to count the number of occurrences of the "first digit".

Screenshot 2023-11-12 at 8.02.42 PM.png

 

I attached an example based on your data.