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.
Note: In the following example I assume that name's format of your documents will be the same for all of them.
Into Sheet1 add another column and format its cells to Number format:
Into cell A2 enter the following formula and then copy it down to cell A51:
= VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "_", ""), ".", ""), "tif", ""))
Now go to Sheet2:
Into cells B2 and C2 enter the following formulas and then copy them down to B5 and C5:
- 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)
Note: The above two formulas are array formulas and after enter some of them you must press CTRL + SHIFT + ENTER instead only ENTER.
Best regards.
P.S. 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).
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 All in one place (edited) (somewhere below). You may prefer it instead the first one.
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 into Sheet2 for MIN and MAX values for cells B2 and C2:
= 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.
Note: These are array formulas too.
...
Comment Edit: After some playing with the formatting of TEXT function, 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: DocNum
Refers to: = OFFSET(Sheet1!$A$1, 1, 0, COUNTA(Sheet1!$A:$A) - 1)
- for the second one enter:
Name: Operators
Refers to: = OFFSET(Sheet1!$B$1, 1, 0, COUNTA(Sheet1!$B:$B) - 1)
The names DocNum and Operators are 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")
Note: 1) You must not leave blank cells between the other data in the columns A and B.
2) The number of populated cells in columns A and B must be equal or you get the #N/A error.
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