Results 1 to 9 of 9

Thread: Create / Consolidate Data Range from Full Values

  1. #1
    New Member
    Join Date
    May 2009
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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


    Thank you in advance!

    Cheers,
    A

  2. #2
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    May 2009
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default 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

    ABCDE
    1Column 1Column 2RangeRangeValue
    2111-31-31
    3214-54-52
    4316-86-83
    54299-94
    652
    763
    873
    983
    1094

    Spreadsheet Formulas
    CellFormula
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  7. #7
    New Member
    Join Date
    May 2009
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Create / Consolidate Data Range from Full Values

    You are welcome

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Create / Consolidate Data Range from Full Values

    Quote Originally Posted by andrewgroom View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •