PDA

View Full Version : Max and min values



sindhuja
09-17-2009, 09:04 AM
Hi,

Have an excel with numerous data…

All I want to find the max and min values in a column with autofilter turned true.

For example I have a column named price and product.

I have filtered for the value "Elmo" product in the column Product wherein I need the max and the min value in the price column. And this should be displayed in the sheet 2 spreadsheet in the specified column.
Can this be done using VBA

-Sindhuja

rbrhodes
09-17-2009, 10:29 PM
Post a small example?

sindhuja
09-18-2009, 06:02 AM
Hi,
Have a sheet named sheet2 with all the details.

In sheet1 I have a catergory specified in column B. This is the first criteria to be filtered in Sheet 2.

Then again in the sheet1, I have certain products for which we are in need of details.

we have to filter for all the values in column H of sheet1 in the sheet2 and get the necessary details.

Have attached sample sheet for the reference.

-Sindhuja

rbrhodes
09-18-2009, 06:37 PM
Hi S,

This is very specific to the example.

I put a list of categories in Col AA on sheet 1 and Data validation in Cell B1 so there is a dropdown list to choose from. That could be automated with an advanced filter set to unique...

The 'Doit' button, filters sheet 2, copies it to sheet 1 and then palces the values in the correct columns. It's very 'step by step' and like I said specific to this example but because it's like that it's also very easy to change and/or adapt.

mdmackillop
09-19-2009, 06:24 AM
You might want to look at Pivot Table solutions

sindhuja
09-21-2009, 10:23 AM
Thanks for helping me out..

Sorry ! I cannot go for the pivot because I have very large number of data and wide variety of category…

Also, I have tried Rhodes sugession and but to be specific I need only some product which is already defined in the Column H of sheet 1 and not for all the products in the category.

This is what I manually do..

I will be filtering out the category defined in the sheet 1 (cell B1) in the sheet 2 (column 1).

Also I have a prdefined products specified in the column H of Sheet 1. Only for those products I need the values of Price,Min Tax %.Max Tax %,Min Date,Max Date and Manufacturer.
Apart from that Product ID is associated with the Product in Sheet 1 which is not there in the Sheet 2.

Let me also give and try and let you know i found something..

-Sindhuja

rbrhodes
09-21-2009, 05:56 PM
Hi s,

Well if I understand correctly all you want in sheet 1 is the details of whatever is in Col H.

This version will:

- Delete Sheet 1 Cols A to G. Leaving the Product ID Col A and the list in Col H
- Filter Sheet 2
- Copy the filtered data to sheet 1 Col K (temp move)
- Do a find for each value in Col H. and copy the details to the correct row
-Delete the temp info

Could skip the temp copy I suppose...

sindhuja
10-02-2009, 12:39 PM
Hi Rhodes….

I tried the coding with my original document.
Am unlucky...Its not working for me…

Let me be clear in my rquirement once again.

1) Filter for the E1 value of Sheet1 in the "Plow" sheet [example A001]

2) Again filter the values specified in the column I of the Sheet1 one by one in the "Plow" sheet. [Example 2,000,000.00 in the loan amount column]

3) Again Check if there is more than one value in the column I of the "Plow" sheet. If more than one value then filter for the unique values, find the max and min values of Rate, Mat rate and the sum of the Value. Update the values in the Sheet1 in the specific columns.

Hope I made it clear now..also attached my data for the reference

Have been strugling wiyh this for quite a long time…

Any help will be highly appreciated.

-Sindhuja

sindhuja
10-05-2009, 08:38 AM
Any help on this pls…

-Sindhuja

sindhuja
10-08-2009, 06:37 AM
Any help pls...

-sindhuja

rbrhodes
10-08-2009, 11:50 AM
Hi sindhuja,

Well I see you've bumped this a couple of times and no assistance. This reply should help. Sometimes people jump in if there is something going on in the thread... You could also check my (rudimentary) web page which offfers help on various levels depending on the project...free, donation, quote. As it says 'Forums aren't helping?' <grin>

Here's a refinement of what I offered before thats seems to do what you need. What it does:

- Filters by Sheet1 E1
- Copies that, turns off filter
- Does a 'Find' for each value
- Posts Min and Max rates and dates for each item

I presumed 'Value' column on the results sheet means the loan value we're searching for and not the 'value' of the loan as in the other sheet.

C'ya

sindhuja
10-09-2009, 10:52 AM
Hello,

Thanks a lot...
Again a challenge for us…

I have attached an sample sheet with the expected results.

For ex , in the case of 2,000,000.00 we have two entries in sheet1 also three entries in the Plow sheet with two INTR_RATE.

Filter for the A001 in the plow sheet then filter for 2,000,000.00 in the LOAN_AMT and again check for the INTR_RATE.


For each unique INTR_RATE capture the min and max details of rate and date and the sum of the value.


Hope atleast now I made clear…
Sorry if am bothering you again and again…

-Sindhuja

rbrhodes
10-11-2009, 12:42 AM
Sorry,

I still don't get the logic. Maybe a bigger example and a more thorough explanation of the rules? I just don't get the double entry of 2M etc.

sindhuja
10-12-2009, 08:18 AM
Hi Rhodes….

To be more clear:

Have a sheet named "Plow" which is the master sheet from where I need to extract the data's.

Another sheet named "sheet1" wherein I have certain values for which only I need details.

As per the example given in the above thread. There are two 20M in the sheet1 as I have 2 INTR_RATE in the Plow for a particular search criteria and for the particular amount.

If there are 3 INTR_RATE for a particular amount then there will be three entries for that amount in the sheet1.

To be simple we have to extract for all values in the column I. If more than one value of the same amount then the we have to get values based on the INTR_RATE column of "plow" sheet… max and min values.

Hope I made it clear…

Thanks in advance for your assistance :)

-Sindhuja

sindhuja
10-15-2009, 10:42 AM
Any help on this pls..

-sindhuja

rbrhodes
10-18-2009, 04:09 AM
Hi.

I'll take another look as soon as time permits

mdmackillop
10-18-2009, 08:21 AM
Try this

sindhuja
10-23-2009, 09:33 AM
Thanks a lot !

It works perfectly for me except for the last value in the Sheet 1.

Its not picking up the values from Plow sheet.
Also am new to the concept of "Scripting.Dictionary"

Can you shed some info about Scripting.Dictionary.

-Sindhuja

mdmackillop
10-23-2009, 02:30 PM
The last value is A002 and should not be picked up.

Scripting Dictionary is like a Collection and can be used to create a list on unique items. This list is used as criteria for the filter.

sindhuja
10-24-2009, 07:30 AM
Thanks for the explanation......
i mean the last value in the amount column...

For the last amount its not giving the expected value from the plow sheet.... when i tried in my original data...

-Sindhuja

mdmackillop
10-24-2009, 08:53 AM
Can you repost sample data to test this properly?

sindhuja
11-03-2009, 02:24 PM
Checked with the coding..

The main criteria for the search in the PLOW sheet is the loan amount in SHEET1.

I found the coding works in other way…

It picks the unique loan amount and displays all the values in the sheet 1 inspite of the loan amount already mentioned in the sheet 1.

To be simple we have to extract for all values in the column I. If more than one value of the same amount then the we have to get values based on the INTR_RATE column of "plow" sheet… max and min values and the collateral values


Please have a look and assist me further in completing my requirement…

-Sindhuja

sindhuja
11-04-2009, 11:19 AM
Hi Md,

I have attached the sample sheet with the expected results...
i did some modifications in the coding per my requirement...

your help is highly needed...

-sindhuja

mdmackillop
11-04-2009, 01:01 PM
I can't open the zip file. Can you post it unzipped?

GTO
11-04-2009, 07:13 PM
Hi Malcom,

I was able to get into the zip at work. Here is file 'cleaned up'.


Sinduja,

I do not believe I deleted any real data, but check to make sure. I think your workbooks are bloating something terrible, as with only about 350 cells of any data, 2500kb filesize is not right. I didn't have any problems with opening or saving the reduced wb.

Mark

sindhuja
11-05-2009, 09:16 AM
Pls check for the attachment...

sindhuja
11-16-2009, 09:24 AM
Any help pls...

sindhuja
11-22-2009, 01:57 AM
Hi Md...

Any clue of my requirement..

mdmackillop
11-22-2009, 09:28 AM
Try this version

Sub Test()
Dim a, d, i, e, f, g 'Create some variables
Application.ScreenUpdating = False
'Get Col Types
With Sheets("Plow")
.Activate
Set rng = Range(.Cells(2, "G"), .Cells(Rows.Count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible)
End With
Set d = CreateObject("Scripting.Dictionary")
On Error Resume Next
For Each cel In rng
d.Add cel.Value, cel.Value
Next
a = d.Items 'Get the items
'Filter on Col types
For i = 0 To d.Count - 1
Sheets("Plow").Range("$A:$I").AutoFilter
Sheets("Plow").Range("$A:$I").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("E1")
Sheets("Plow").Range("$A:$I").AutoFilter Field:=7, Criteria1:=a(i)
'Get Loan amounts
With Sheets("Plow")
Set rng = Range(.Cells(2, "C"), .Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlCellTypeVisible)
End With
Set e = CreateObject("Scripting.Dictionary")
On Error Resume Next
For Each cel In rng
e.Add cel.Value, cel.Value
Next
f = e.Items 'Get the items
'Filter on Loan Amounts
For g = 0 To e.Count - 1
Sheets("Plow").Range("$A:$I").AutoFilter
Sheets("Plow").Range("$A:$I").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("E1")
Sheets("Plow").Range("$A:$I").AutoFilter Field:=7, Criteria1:=a(i)
Sheets("Plow").Range("$A:$I").AutoFilter Field:=3, Criteria1:=Format(f(g), "#,###,##0.00")

With Sheets("Plow")
Set rng = Range(.Cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlCellTypeVisible)
Dim tmp
End With

Select Case rng.SpecialCells(xlCellTypeVisible).Count
Case 2
Set c = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Offset(1)
Set s = Sheets("Plow").Cells(Rows.Count, 2).End(xlUp)
c.Offset(, 0) = s.Offset(, 2)
c.Offset(, 1) = s.Offset(, 3)
c.Offset(, 2) = s.Offset(, 3)
c.Offset(, 3) = s.Offset(, 4)
c.Offset(, 4) = s.Offset(, 4)
c.Offset(, 5) = s.Offset(, 5)
c.Offset(, 7) = s.Offset(, 1)
Case Else
With Sheets("Plow")
Set rng2 = Range(.Cells(2, "I"), .Cells(Rows.Count, "I").End(xlUp)).SpecialCells(xlCellTypeVisible)
End With
Set e = CreateObject("Scripting.Dictionary")
On Error Resume Next
For Each cel2 In rng2
e.Add cel2.Value, cel2.Value
Next
b = e.Items
For j = 0 To e.Count - 1
Sheets("Plow").Range("$A:$I").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("E1")
Sheets("Plow").Range("$A:$I").AutoFilter Field:=9, Criteria1:=Format(b(j), "###,##0.00")
With Sheets("Plow")
Set rng2 = Range(.Cells(1, "I"), .Cells(Rows.Count, "I").End(xlUp)).SpecialCells(xlCellTypeVisible)
End With
Set s = Sheets("Plow").Cells(Rows.Count, 2).End(xlUp)
Set c = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Offset(1)
c.Offset(, 0) = Application.Sum(rng2.Offset(, -6))
c.Offset(, 1) = Application.Min(rng2.Offset(, -4))
c.Offset(, 2) = Application.Max(rng2.Offset(, -4))
c.Offset(, 3) = Application.Min(rng2.Offset(, -3))
c.Offset(, 4) = Application.Max(rng2.Offset(, -3))
c.Offset(, 5) = s.Offset(, 5)
c.Offset(, 7) = s.Offset(, 1)
Next
End Select
Next
Next
Sheets("Plow").Range("$A:$I").AutoFilter
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub