# Excel-getting the extreme values of a data group

Hi

I am sorry, I have to re-open this query.

Actually I have tried to reply to an earlier response for this query but as I am unable to attach the spread sheet there, sending this as a new topic again.

Please let me know how to attach a file in the reply window.

The idea of inserting a Pivot Table and there by getting Min & Max is good but this works for numeric fields only, I believe.

The format of document numbers in my spread sheet is like this:1234_0456.0789.tif and I should not change them in any manner.

They are arranged in increasing order.

Every day, I am happening to filter on each operator and get their From tif & Thru tif numbers.

Please find the appended sheet and review the report (placed in sheet-2) that I need to generate on a daily basis based on the data present in sheet-1.

Please suggest me the best practice, so I can avoid manual extraction of From tif & Thru tif numbers and save my time.

Thanks & Regards

Kumar.

Attachment | Size |
---|---|

Allotment.xlsx | 10.02 KB |

## RE: Getting the extreme values

Hi,

Here's another solution.

In the following example I assume that name's format of your documents will be the same for all of them.Note:Into

Sheet1add another column and format its cells toNumberformat:Temp Doc #Document #OperatorInto cell

A2enter the following formula and then copy it down to cellA51:= VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "_", ""), ".", ""), "tif", ""))Now go to

Sheet2:Into cells

B2andC2enter the following formulas and then copy them down toB5andC5:- this is for

B2:= VLOOKUP(MIN(IF((Sheet1!$A$2:$A$51) * (Sheet1!$C$2:$C$51 = A2) <> 0; (Sheet1!$A$2:$A$51) * (Sheet1!$C$2:$C$51 = A2); FALSE)); Sheet1!$A$2:$B$51; 2; FALSE)- this is for

C2:= VLOOKUP(MAX((Sheet1!$A$2:$A$51) * (Sheet1!$C$2:$C$51 = A2)); Sheet1!$A$2:$B$51; 2; FALSE)The above two formulas are array formulas and after enter some of them you must pressNote:CTRL + SHIFT + ENTERinstead onlyENTER.Best regards.

About your other question: You cannot attach file to Comment. Instead, you can edit your forum question and there you can extends your requirements or attach another file(s).P.S.## Hi Manny Thank you for your

Hi Manny

Thank you for your extended ideas with regard to my concern.

In Sheet-2, Vlookup is encountering an error at the logical operation "<> 0".

This is happening in B2 & C2 as well.

I have used CTRL+SHIFT+ENTER but did not work.

Could you please look into this and do the needful.

Regards

Kumar.

## Error in formula

Hi,

Please, replace all ";" (semicolon) with "," (comma) into the formula. Usually I do it before post some formula but this time I forget. Sorry for that...

If you have interest, take a look at my other suggestion, which is into the comment

(somewhere below). You may prefer it instead the first one.All in one place (edited)## Manny... Thank you for fixing

Manny...

Thank you for fixing the problem quickly.

Vlookup is now working fine.

I have tried the alternative method too and found the result.

Could you please respond to my concern in the previous comment.

Regards

Kumar.

## All in one place (edited)

If you want to avoid to add an additional column into

Sheet1, you can use the following formulas intoSheet2forMINandMAXvalues for cellsB2andC2:= REPLACE(REPLACE(REPLACE(MIN(IF(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2:$A$51, "_", ""), ".", ""), "tif", "")) * (Sheet1!$B$2:$B$51 = A2) <> 0, VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2:$A$51, "_", ""), ".", ""), "tif", "")) * (Sheet1!$B$2:$B$51 = A2), FALSE)), 5, 0, "_"), 10, 0, "."), 15, 0, ".tif")and

= REPLACE(REPLACE(REPLACE(MAX(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2:$A$51, "_", ""), ".", ""), "tif", "")) * (Sheet1!$B$2:$B$51 = A2)), 5, 0, "_"), 10, 0, "."), 15, 0, ".tif")They are a bit complicated for reading, but thus all is in one place.

These are array formulas too.Note:...After some playing with the formatting ofComment Edit:TEXTfunction, I managed to simplified the above formulas to the following:= TEXT(MIN(IF(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2:$A$51, "_", ""), ".", ""), "tif", "")) * (Sheet1!$B$2:$B$51 = A2) <> 0, VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2:$A$51, "_", ""), ".", ""), "tif", "")) * (Sheet1!$B$2:$B$51 = A2), FALSE)), "#\_####\.####.tif")and

= TEXT(MAX(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2:$A$51, "_", ""), ".", ""), "tif", "")) * (Sheet1!$B$2:$B$51 = A2)), "#\_####\.####.tif")Not much, but is better than nothing...

## Manny, this is brilliant.

Manny, this is brilliant. Thanks a lot.

As you said, this is complicated to read but magical in execution.

A quick question is..

How can I make this work for if the data has to go beyond record-51.

Can we make this work for the entire Column-A populated with document numbers?

Please suggest.

Regards

kumar.

## Use dynamic ranges

You can use dynamic ranges. The half part of the following topic shows how to did dynamic range:

www.excelexperts.com/Excel-Tips-Dynamic-Charting

To work for you, create two name ranges:

- for the first one enter:

Name:DocNumRefers to:= OFFSET(Sheet1!$A$1, 1, 0, COUNTA(Sheet1!$A:$A) - 1)- for the second one enter:

Name:OperatorsRefers to:= OFFSET(Sheet1!$B$1, 1, 0, COUNTA(Sheet1!$B:$B) - 1)The names

DocNumandOperatorsare just for example. You can change them if you want.Now use these dynamic ranges into the formulas. For example:

= TEXT(MIN(IF(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DocNum, "_", ""), ".", ""), "tif", "")) * (Operators = A2) <> 0, VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DocNum, "_", ""), ".", ""), "tif", "")) * (Operators = A2), FALSE)), "#\_####\.####.tif")and

= TEXT(MAX(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DocNum, "_", ""), ".", ""), "tif", "")) * (Operators = A2)), "#\_####\.####.tif")1) You must not leave blank cells between the other data in the columnsNote:AandB.2) The number of populated cells in columns

AandBmust be equal or you get the#N/Aerror.## Thank you Manny. This should

Thank you Manny.

This should be a good excercise for me. I will try the above recommended steps and get back to you if the problem persists.

Regards

Kumar.

## Manny... Could you please

Manny...

Could you please respond to the above concern.

Thanks & Regards

Kumar.

## Add sheet1: column c

Add sheet1:

column c =EXTRAE(A2;1;4) and name it cod1

column d =EXTRAE(A2;6;4) and name it cod2

column e =EXTRAE(A2;11;4) and name it cod3

column f =CONCATENAR(C2;D2;E2)and name it codtext

column g =VALOR(F2) and name it codnum

column h =A2 and name it document#

create a pivot table in the sheet 3 with all of this values, using max and min value.

Then add columns:

colum d =BUSCARV(IMPORTARDATOSDINAMICOS("From tif";$A$3;"Operator";"Kumar");Sheet1!G:H;2;FALSO)

colum e =BUSCARV(IMPORTARDATOSDINAMICOS("From tif";$A$3;"Operator";"Kumar");Sheet1!G:H;2;FALSO)

colum f =D4&" "&"-"&" "&E4

and then ocult the columns b and c. You have to refresh the pivot table each time you change values in sheet 1.

I know that I have the excel in spanish, but sure you can find the translation.

I hope that can help you.

Best regards,

Emma