| city | building | floor | wing | seatno |
|---|---|---|---|---|
| blr | egla | 5F | A | 1 |
| blr | egla | 5F | A | 2 |
| blr | egla | 5F | A | 5 |
| blr | egla | 5F | B | 6 |
| blr | egla | 5F | B | 7 |
| blr | egla | 5F | B | 11 |
| blr | egla | 5F | B | 12 |
| blr | egla | 5F | B | 13 |
| blr | egla | 5F | 234 | |
| blr | egla | 5F | 254 |
in Excel, want to get the min and max result as below
| city | building | floor | wing | seatrange_From | seatrange_To |
|---|---|---|---|---|---|
| blr | egla | 5F | A | 1 | 2 |
| blr | egla | 5F | A | 5 | 5 |
| blr | egla | 5F | B | 6 | 7 |
| blr | egla | 5F | B | 11 | 13 |
| blr | egla | 5F | 234 | 234 | |
| blr | egla | 5F | 254 | 254 |
how to do it in excel summarise like above
CodePudding user response：
One way with formulae in ms365:
Formula in G1:
=LET(a,"|",z,DROP(REDUCE(0,REDUCE(TEXTJOIN(a,,A1:D1,"Seatrange_"&{"From","To"}),BYROW(A2:E11,LAMBDA(b,TEXTJOIN(a,0,b))),LAMBDA(c,d,LET(x,TOCOL(c,3),e,TEXTBEFORE(d,a,-1),f,--TEXTAFTER(d,a,-1),g,TAKE(x,-1),h,TEXTBEFORE(g,a,-2),i,--TEXTAFTER(g,a,-1),IF(IFERROR((e=h)*(f=(i 1)),0),VSTACK(DROP(x,-1),TEXTBEFORE(g,a,-1)&a&f),VSTACK(x,d&a&f))))),LAMBDA(j,k,VSTACK(j,TEXTSPLIT(k,a)))),1),IFERROR(--z,z))
This should now look at all 4 column and make a decision whether or not the VSTACK() should include a new row or adjust the previous one.