• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula help first and last range by group

jk51

Member
Hello

Need help on creating formula for contents.

In column A is reference page follow by column B pages a list of fruits and then create excel formula in column C to print first and last range of page numbers by each different type of fruits. see example below
For example there are 2 pages about apple, look at first and last pages of apple, range is 1-2

Thank you

Jake

Ref pagelist of fruitrange of page number
1apple1-2
1apple1-2
1apple1-2
2apple1-2
2apple1-2
2apple1-2
3banana3-7
3banana3-7
3banana3-7
3banana3-7
3banana3-7
3banana3-7
3banana3-7
4banana3-7
4banana3-7
4banana3-7
5banana3-7
6banana3-7
7banana3-7
8pear8
8pear8
9grapes9-13
10grapes9-13
11grapes9-13
11grapes9-13
12grapes9-13
13grapes9-13
Does anyone know how to
 

Attachments

  • sample.xlsx
    9.1 KB · Views: 8
You could use:
=MINIFS($A$2:$A$28,$B$2:$B$28,B2)&"-"&MAXIFS($A$2:$A$28,$B$2:$B$28,B2)
in C2 copied down, but where there's only one page it leads to the likes of: 8-8.

To avoid that there's this:
Code:
=IF(MINIFS($A$2:$A$28,$B$2:$B$28,B2)=MAXIFS($A$2:$A$28,$B$2:$B$28,B2),""&MINIFS($A$2:$A$28,$B$2:$B$28,B2),MINIFS($A$2:$A$28,$B$2:$B$28,B2) & "-" & MAXIFS($A$2:$A$28,$B$2:$B$28,B2))

which can be shortened if you have the LET function available to you:
Code:
=LET(min,MINIFS($A$2:$A$28,$B$2:$B$28,B2),max,MAXIFS($A$2:$A$28,$B$2:$B$28,B2),IF(min=max,max&"",min & "-" & max))
 
Last edited:
See attached Sheet1 for those formulae in my last reply in columns E:G.
In addition, there's a Power Query solution which condenses the results by removing duplicates… but this doesn't do well for a table of contents because it doesn't account for when fruit do not appear on all pages between the min and max page refs, so on Sheet2 there's another Power Query solution which does. Change the data in the table on the left then update the query table on the right by right-clicking it and choosing Refresh.
 

Attachments

  • Chandoo47197sample.xlsx
    26.3 KB · Views: 4
Last edited:
Another one could be:
Excellent! I'd been playing with AGGREGATE({14,15}… and completely forgot about TEXTJOIN, so this is what I nearly got to (I haven't got my head round MODE.MULT!):
Code:
=TEXTJOIN("-",TRUE,UNIQUE(AGGREGATE({15;14},6,IF(B$2:B$28=B2,A$2:A$28),1)))
 
Code:
= XLOOKUP(fruit,fruit,page,,,{1,-1})
will generate the page numbers but in two columns. To concatenate the columns without running over the rows is harder.
Code:
= BYROW(
    XLOOKUP(fruit, fruit, page,,,{1,-1}),
    LAMBDA( entry, TEXTJOIN("-",,entry))
  )
Either formula would be less repetitive if UNIQUE were used to select individual fruits
Code:
= LET(
    distinct, UNIQUE(fruit),
    BYROW(XLOOKUP(distinct, fruit, page,,,{1,-1}),
      LAMBDA(entry, TEXTJOIN("-",,entry))
    )
  )

77024
 
For that last one Peter, I think you had to make the Distinct column (G) a separate and independent formula (that is, not directly connected to the Page range column (H))?
This one:
Code:
= LET(y,SORT(UNIQUE(Table1[fruit])),y &" " & BYROW(y,LAMBDA(z,TEXTJOIN("-",TRUE,UNIQUE(AGGREGATE({15;14},7,FILTER(Table1[ref page],z=Table1[fruit]),1))))))
avoids the likes of 8-8, is independent of the order of the source data, but puts the fruit and page range in the same cell; now I want the spill range to put the fruit and page range in separate columns… do you know how? (bedtime here)

After that:
77026

(with a different data source, of course).
 
Last edited:
This is one for @Peter Bartholomew , because I'm struggling. I've been shown a different way from mine in Power Query to obtain TOC entries like
banana| 3, 5-7 instead of banana| 3-7 in the case where there is no banana on page 4 (anyone reading this out of context will think I'm absolutely nuts bananas). This method I think can lend itself to a formula equivalent.
It goes like this:
Start with a list of page numbers for one fruit
Create a unique sorted list from it (the original list is not sorted)
Put an index no. against the result
Subtract the index from the page number. This results in the same number appearing where there's a consecutive run in the page numbers - a grouping.
Use MINIFS and MAXIFS using the grouping
TEXTJOIN the results.
Eg. (Sheet3 of the attached):
77032
So how to eliminate all those helper columns (D:H), then do it for more than one fruit, again, hopefully, in one cell which spills into multiple rows and 2 columns?
I'm not in the insider update for Office365 but I've discovered after a recent update that I have access to LAMBDA now so I'm trying to get the hang of it.

Sheet2 of the attached holds something similar to the above in columns M:R.
There's no Power Query

Thanks to Bill Szysz here: https://www.excelguru.ca/forums/sho...-list-of-numbers&p=46994&viewfull=1#post46994
 

Attachments

  • Chandoo47197.xlsx
    20.4 KB · Views: 8
Last edited:
Good news that you have Lambda functions! I have probably spent far too long demonstrating solutions that only I can use. Do you also have Lambda helper functions such as MAP, SCAN etc?

This may help. Because your method uses MINIFS/MAXIFS (which require range references) on dynamic arrays, I would have to reimplement those functions as Lambda functions. As a consequence, I have taken a slightly different route and implemented difference functions FDiffλ and BDiffλ to identify the jumps.

The functions I used are
Code:
= LET(
  refList, SORT(UNIQUE(pageNums)),
  strt,    FILTER(refList, BDiffλ(refList)>1),
  end,     FILTER(refList, FDiffλ(refList)>1),
  TEXTJOIN(", ",,IF(strt<end, strt&"-"&end, strt)))

"FDiffλ"
= LAMBDA(pg,
    LET(
      k, SEQUENCE(COUNT(pg)),
      IFERROR(INDEX(pg,k+1),∞) - INDEX(pg, k)
  ))
 
"BDiffλ"
= LAMBDA(pg,
    LET(
      k, SEQUENCE(COUNT(pg)),
      INDEX(pg,k) - IF(k>1, INDEX(pg, k-1))
  ))
 

Attachments

  • Chandoo47197.xlsx
    20.9 KB · Views: 3
@bosco_yip
I sometimes forget how much was possible using traditional methods. It reminds me of debates along the lines of: "Do not use array formulas because they are powerful; use them because they make whole classes of inconsistency errors almost impossible to achieve".

77041
This is all pretty heavy going, full-on programming. Rather than assuming sorted data, the formula performs the sort
 

Attachments

  • PageByGroup.xlsx
    16.4 KB · Views: 2
This is one for @Peter Bartholomew , because I'm struggling. I've been shown a different way from mine in Power Query to obtain TOC entries like
banana| 3, 5-7 instead of banana| 3-7 in the case where there is no banana on page 4 (anyone reading this out of context will think I'm absolutely nuts bananas). This method I think can lend itself to a formula equivalent.
It goes like this:
Start with a list of page numbers for one fruit
Create a unique sorted list from it (the original list is not sorted)
Put an index no. against the result
Subtract the index from the page number. This results in the same number appearing where there's a consecutive run in the page numbers - a grouping.
Use MINIFS and MAXIFS using the grouping
TEXTJOIN the results.
Eg. (Sheet3 of the attached):
View attachment 77032
So how to eliminate all those helper columns (D:H), then do it for more than one fruit, again, hopefully, in one cell which spills into multiple rows and 2 columns?
I'm not in the insider update for Office365 but I've discovered after a recent update that I have access to LAMBDA now so I'm trying to get the hang of it.

Sheet2 of the attached holds something similar to the above in columns M:R.
There's no Power Query

Thanks to Bill Szysz here: https://www.excelguru.ca/forums/sho...-list-of-numbers&p=46994&viewfull=1#post46994

Please refers to p45cal's attached file, herein is the formula solutions without helper columns and dynamic functions

1] In "Sheet 1" Range of the page no, in C2 CSE formula :

=MID(CONCAT(IFERROR(CHOOSE(MMULT(0+ISNUMBER(MATCH(UniquePage+{1,-1},UniquePage,0)),{1;3}/2)+1,", ","-")&UniquePage,"")),2,99)

2] In "Sheet 1" Range of ref page no by group, in I2 CSE formula copied down :

=MID(CONCAT(IFERROR(CHOOSE(MMULT(0+ISNUMBER(MATCH(UniqueRefPage+{1,-1},UniqueRefPage,0)),{1;3}/2)+1,", ","-")&UniqueRefPage,"")),2,99)

77067

3] In "Sheet 2" Range of the page no for single cell data, In C2 CSE formula :

=MID(CONCAT(IFERROR(CHOOSE(MMULT(0+ISNUMBER(MATCH(UniquePageNo+{1,-1},UniquePageNo,0)),{1;3}/2)+1,", ","-")&UniquePageNo,"")),2,99)

77070
 

Attachments

  • PageNoByGroup(BY).xlsx
    18 KB · Views: 6
Last edited:
@bosco_yip
I sometimes forget how much was possible using traditional methods. It reminds me of debates along the lines of: "Do not use array formulas because they are powerful; use them because they make whole classes of inconsistency errors almost impossible to achieve".

View attachment 77041
This is all pretty heavy going, full-on programming. Rather than assuming sorted data, the formula performs the sort

Please refers to Peter's layout, herein is my traditional formula solution & without dynamic functions

In E2, CSE formula copied down :

=MID(CONCAT(IFERROR(CHOOSE(MMULT(0+ISNUMBER(MATCH(item+{1,-1},item,0)),{1;3}/2)+1,", ","-")&item,"")),2,99)

77064
 

Attachments

  • PageByGroup(Peter).xlsx
    13.2 KB · Views: 6
Last edited:
@bosco_yip
An amazing piece of work. I liked the use of 'item' to hold the array formula for multiple reuse and the building and splitting of the concatenated array of results worked like magic. For my own work, I have completely switched to 365 but I can still admire the mastery of traditional Excel methods!
 
In replying to Bosco, I appear to have deleted one of my own posts, mistaking it for a quote of a quote :(.
For what it is worth, this is the documentation with line by line commenting.
77079

I might just settle for the final formula though
Code:
= Indexλ(pageNums, indexedItems)
 
Back
Top