Forum Discussion

Jasmyn1565's avatar
Jasmyn1565
Copper Contributor
Aug 29, 2024

Condensing Data into a Range

Hello,   I am looking to condense data into a range. I have an example below.   In the sheet it shows: (All the data is in the same cell) Sally 1 Sally 2 Sally 3 Carl 1 John 1 John 2   Is...
Formulas and Functions
training
djclements's avatar
djclements
Silver Contributor
Aug 29, 2024

Jasmyn1565 I have a few questions...

  1. What version of Excel are you using?
  2. Do the numbers always start from 1 and include all numbers from 1 to the maximum occurrence for each name?
  3. What exactly do you mean by "all the data is in the same cell"?

If you have Excel for MS365, one possibility (based on my assumptions for question 2 & 3) could be:

 

=LET(
    rng, A2:A7,
    unq, UNIQUE(TEXTBEFORE(rng, " ")),
    qty, COUNTIF(rng, unq & "*"),
    unq & " 1" & IF(qty>1, "-" & qty, "")
)

 

Sample Results

 

However, I'm not entirely sure my interpretation of your described dataset was accurate.

djclements's avatar
djclements
Silver Contributor
Aug 30, 2024

Jasmyn1565 Further to my previous reply, if the entire list of names/numbers is actually in the same cell, separated by line breaks (Alt+Enter), you could try something along these lines:

 

=LET(
    arr, TAKE(TEXTSPLIT(A2, " ", CHAR(10)),, 1),
    unq, UNIQUE(arr),
    qty, MMULT(N(unq=TOROW(arr)), EXPAND(1, ROWS(arr),, 1)),
    unq & " 1" & IF(qty>1, "-" & qty, "")
)

 

Sample Results

Resources