Create / Consolidate Data Range from Full Values

andrewgroom

New Member
Joined
May 7, 2009
Messages
21
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 1Column 2
11
21
31
42
52
63
73
83
94

<tbody>
</tbody>

Would become:

Column 1Column 2
1-31
4-52
6-83
94

<tbody>
</tbody>


Thank you in advance!

Cheers,
A
 

Some videos you may like

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
Joined
Aug 28, 2016
Messages
647
Office Version
365, 2013
Platform
Windows
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
Joined
May 7, 2009
Messages
21
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
Joined
May 28, 2005
Messages
45,255
Office Version
365
Platform
Windows
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
Joined
Aug 28, 2016
Messages
647
Office Version
365, 2013
Platform
Windows
You're welcome Andrew.

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,850
or without any formula but with PowerQuery aka Get&Transform (if you are able to use it):

Column1Column2RangeColumn2
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
Joined
May 7, 2009
Messages
21
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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,255
Office Version
365
Platform
Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top