Create / Consolidate Data Range from Full Values

[andrewgroom]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Would become:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]1-3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4-5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6-8[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance!

Cheers,
A

 

[JustynaMK]

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:

=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:

=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]

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]

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
	A	B	C	D	E
1	Column 1	Column 2	Range	Range	Value
2	1	1	1-3	1-3	1
3	2	1	4-5	4-5	2
4	3	1	6-8	6-8	3
5	4	2	9	9-9	4
6	5	2
7	6	3
8	7	3
9	8	3
10	9	4
Ranges

 

[JustynaMK]

You're welcome Andrew.

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

 

[sandy666]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Range[/td][td=bgcolor:#70AD47]Column2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]1-3[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]

1​

[/td][td][/td][td]4-5[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]6-8[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]

2​

[/td][td][/td][td]9-9[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

6​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

7​

[/td][td=bgcolor:#DDEBF7]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

8​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

9​

[/td][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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]

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!

 

[sandy666]

You are welcome

Have a nice day

 

[Peter_SSs]

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!

Glad to contribute to your options.