Title: Excel formula for max or min of multiple occurrences of vlookup.

Source: https://stackoverflow.com/questions/64206037/excel-formulae-for-max-or-min-of-multiple-occurrences-of-vlookup

【Question】

I need to do a complicated vlookup/maxif type of selection. The data I have is as below:

Row Col G  Col H  Col I  colJ  col K

1  Bench  Strip  Block  BenchAbove  BenchBelow

2  1    1    4

3  1    1    5

4  1    1    6

5  1    1    7

6  1    1    8

7   8   1   4   ??   ??

8   8   1   5

9   8   1   6

10  8   1   7

11  8   1   8

12  9   1   4

13  9   1   5

14  9   1   6

15  9   1   7

…..This list is long (this is a sample only)

For every combination of (Strip, block) like say (1,4) There are benches like 1, 8 and 9. So bench above for 8 is 1 and bench below for 8 is 9. I need to determine the bench above and bench below for each row. There is no bench above 1 and no bench below 9.

I don’t think vlookup is the solution here. Not sure if MAX(IF..) can help either. What would be the best formula to obtain say on row 7, block combination is 1,4. The bench in question is 8. The bench above is 1 and bench below is 9. So two formulas will be required on Col J and Col I above.

The expected result for the above sample data is:

Row Col G  Col H  Col I  colJ  col K

1  Bench  Strip  Block  BenchAbove  BenchBelow

2  1    1    4    -       8

3  1    1    5    -       8

4  1    1    6    -       8

5  1    1    7    -      8

6  1    1    8    -      8

7  8    1    4    1      9

8  8    1    5    1      9

9  8    1    6    1      9

10  8   1    7    1      9

11  8   1    8    1      9

12  9   1    4    8      -

13  9   1    5    8      -

14  9   1    6    8      -

15  9   1    7    8      -

 

【Answer】

 

The difficulty of getting the task done is how to get data from same group according to the relative position.

For the combination of (Strip, Block) like say (1,4), there are benches (1,8,9). As BenchAbove is “the previous Bench” relative to the current row, theirs are (null,1,8); and since BenchBelow is “the next Bench” relative to the current row, theirs are (8,9,null).

It’s hard to express “the relative position” and manipulate data in one group with an Excel formula or in VBA, so both are not the right tool for doing the task. Even we manage to work out a script, it is narrowly applicable. So if the goal is to get “the previous two Benches” or “Benches from row 2 to row 4” relative to the current row, we should rewrite the formula or the VBA code. Office 365 is helpless, too.

Python Pandas and esProc SPL are another two scripting languages that can handle Excel data conveniently. The latter is particularly easy to use in handling your problem. SPL can directly handle data in each group and has special syntax for expressing a relative position. It uses [-1] to represent the previous one and [1] or [+1] to represent the next one, for instance. For your question, the SPL script is as below:

	A	B
1	=file("D:/data.xlsx").xlsimport@t()
2	=A1.group(Strip,Block)	/ Group rows by Strip,Block
3	=A2.(~.run(BenchAbove=Bench[-1],BenchBelow=Bench[1]))	/ intra-group calculation: get   required values in each group
4	=A3.conj().sort(Strip,Bench)	/ Concatenate the groups and   sort rows by the original order
5	=file("D:/result.xlsx").xlsexport@t(A4)

SPL is good at handling operations on xls or csv files. Find more examples and learn more about SPL in http://www.raqsoft.com/p/script-over-csv-xls.