# Thread: Create / Consolidate Data Range from Full Values Thanks:  1 Post #5313217 (1) Likes:  1 Post #5313217 (1)

1. ## Create / Consolidate Data Range from Full Values

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

Would become:

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

Cheers,
A

2. ## Re: Create / Consolidate Data Range from Full Values

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.

3. ## Re: Create / Consolidate Data Range from Full Values

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

4. ## Re: Create / Consolidate Data Range from Full Values

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.

Ranges

 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

 Cell Formula C2 =IF(E2="","",MINIFS(A\$2:A\$10,B\$2:B\$10,E2)&IF(COUNTIF(B\$2:B\$10,E2)>1,"-"&MAXIFS(A\$2:A\$10,B\$2:B\$10,E2),"")) D2 =IF(E2="","",MINIFS(A\$2:A\$10,B\$2:B\$10,E2)&"-"&MAXIFS(A\$2:A\$10,B\$2:B\$10,E2)) E2 =IFERROR(AGGREGATE(15,6,B\$2:B\$10,COUNTIF(B\$2:B\$10,"<="&LOOKUP(9.99E+307,E\$1:E1))+1),"")

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Create / Consolidate Data Range from Full Values

You're welcome Andrew.

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

6. ## Re: Create / Consolidate Data Range from Full Values

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:
```// 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```

7. ## Re: Create / Consolidate Data Range from Full Values

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!

8. ## Re: Create / Consolidate Data Range from Full Values

You are welcome

Have a nice day

9. ## Re: Create / Consolidate Data Range from Full Values

Originally Posted by 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!