# 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 the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### 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,102,765
Messages
5,488,719
Members
407,655
Latest member
messer5740

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...