Create / Consolidate Data Range from Full Values

andrewgroom

New Member
Hi,

I have tried to find an answer to this but to no avail.

I am trying to condense a full list of values into ranges (and there isn't necessarily a pattern). E.g.

 Column 1 Column 2 1 1 2 1 3 1 4 2 5 2 6 3 7 3 8 3 9 4

<tbody>
</tbody>

Would become:

 Column 1 Column 2 1-3 1 4-5 2 6-8 3 9 4

<tbody>
</tbody>

Cheers,
A

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

JustynaMK

Well-known Member
Hi Andrew,

Assuming that your example is presented in cells A1:B10 (i.e. row 1 is a header, rows 2-10 are values), you can use the following formulas in columns D&E:

Cell E2 - array formula - enter with Ctrl+Shift+Enter:
Code:
``=IFERROR(INDEX(\$B\$2:\$B\$10,MATCH(0,COUNTIF(\$E\$1:E1,\$B\$2:\$B\$10),0)),"")``
Cell D2 - array formula - enter with Ctrl+Shift+Enter:
Code:
``=IFERROR(INDEX(\$A\$2:\$A\$10,MATCH(\$E2,\$B\$2:\$B\$10,0))&"-"&INDEX(\$A\$2:\$A\$10,MAX(IF(\$B\$2:\$B\$10=\$E2,ROW(\$B\$2:\$B\$10)))-1),"")``
Now drag & drop both formulas into the remaining cells (D3:E10).

Hope it helps.

andrewgroom

New Member
Thank you so much! This worked perfectly! I expanded it to cover ranges of values of over 1000 rows (and replicated across multiple columns) and it worked just as I needed.

I really appreciate your time taken to respond.

Cheers,
A

Peter_SSs

MrExcel MVP, Moderator
I understand that you already have a suitable solution, but in case it is of interest to you, in columns D:E below are some alternative formulas that do not require the Ctrl+Shift+Enter confirmation.
Column C also contains another alternative in case you want the single value, as you gave in your sample in post 1, where relevant.

Excel Workbook
ABCDE
1Column 1Column 2RangeRangeValue
2111-31-31
3214-54-52
4316-86-83
54299-94
652
763
873
983
1094
Ranges

JustynaMK

Well-known Member
You're welcome Andrew.

And Peter, many thanks for sharing! It's great to learn something new.

sandy666

Well-known Member
or without any formula but with PowerQuery aka Get&Transform (if you are able to use it):

 Column1 Column2 Range Column2 1​ 1​ 1-3 1​ 2​ 1​ 4-5 2​ 3​ 1​ 6-8 3​ 4​ 2​ 9-9 4​ 5​ 2​ 6​ 3​ 7​ 3​ 8​ 3​ 9​ 4​

Code:
``````[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GRP = Table.Group(Source, {"Column2"}, {{"Count", each _, type table}}),
Record1 = Table.AddColumn(GRP, "Custom", each Table.First([Count])),
Expand1 = Table.ExpandRecordColumn(Record1, "Custom", {"Column1"}, {"Column1"}),
Record2 = Table.AddColumn(Expand1, "Custom", each Table.Last([Count])),
Expand2 = Table.ExpandRecordColumn(Record2, "Custom", {"Column1"}, {"Column1.1"}),
Merge = Table.CombineColumns(Table.TransformColumnTypes(Expand2, {{"Column1", type text}, {"Column1.1", type text}}, "en-GB"),{"Column1", "Column1.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Range"),
ROC = Table.SelectColumns(Merge,{"Range", "Column2"})
in
ROC[/SIZE]``````

andrewgroom

New Member
Thank you all for your responses!! Very interesting and I love that there are so many different approaches to finding a solution. You all rock!

You are welcome

Have a nice day

Peter_SSs

MrExcel MVP, Moderator
Thank you all for your responses!! Very interesting and I love that there are so many different approaches to finding a solution. You all rock!

1,089,299
Messages
5,407,451
Members
403,143
Latest member
CTremblay