How to Condense Consecutive Name and Number Values in Excel
Question details
The user needs to summarize consecutive text entries containing a name and a sequential number into a condensed range format (e.g., converting 'Sally 1', 'Sally 2', 'Sally 3' to 'Sally 1-3').
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Grouping text entries by name and extracting the highest consecutive number to create a summarized range list.
- Observed behavior
- Users need to transform a list of individually numbered name entries into a consolidated list showing the name alongside its numerical range.
Ensure your data is formatted consistently with a space separating the name and the number, and verify whether your software version supports dynamic array functions like LET and UNIQUE.
Use the LET and UNIQUE Functions in Microsoft 365
This dynamic array formula is the most efficient way to condense consecutive names and numbers, assuming numbering starts at 1 and has no gaps.
This solution leverages modern dynamic array functions. It works perfectly when your data contains names followed by sequential numbers starting from 1 with no missing numbers in the sequence.
Click on an empty cell where you want the summarized list to appear.
Input the following formula, adjusting 'A2:A7' to match your actual data range: `=LET(rng,A2:A7,unq,UNIQUE(TEXTBEFORE(rng," ")),qty,COUNTIF(rng,unq&"*"),unq&" 1"&IF(qty>1,"-"&qty,""))`
Press the Enter key to execute the formula. The results will automatically spill down, displaying the condensed values like 'Sally 1-3' and 'Carl 1'.
Use Helper Columns for Older Versions of Excel
A traditional approach for users who do not have access to dynamic array functions.
Condense Data Easily with WPS Spreadsheet
WPS Spreadsheet offers excellent compatibility with modern Excel formulas, including advanced functions for data manipulation. You can seamlessly manage, summarize, and condense consecutive data lists for free.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the name and number entries.
- 2. Apply the summary formula: Select a blank cell and input the necessary array formula to extract and count unique names.
- 3. Format the output: Use the concatenation operator (&) within WPS to format the final output as the summarized range.
Frequently Asked Questions
What if my numbers don't start at 1 or have gaps?
The standard LET formula assumes sequences starting at 1. If there are gaps or different starting numbers, you will need a more complex formula combining SEQUENCE, FILTER, and grouping logic, or you may prefer to use Power Query to group consecutive sequences.
Why is the TEXTBEFORE function returning an error?
The TEXTBEFORE function is only available in Microsoft 365 and newer versions. If you see a #NAME? error, it means your version of Excel does not support it. You can use LEFT and FIND as a substitute: `=LEFT(A2, FIND(" ", A2)-1)`.
Can I accomplish this data summarization using Power Query?
Yes, Power Query is excellent for this task. You can load the data, split the column by delimiter, group the rows by the Name column, calculate the Min and Max for the Number column, and then merge them back into a single text string.
