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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
581
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
43,462
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
581
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
4,672
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
43,462
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. :)
 

Forum statistics

Threads
1,089,421
Messages
5,408,133
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top