Max and min values

[sindhuja]

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]

Post a small example?

[sindhuja]

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]

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]

You might want to look at Pivot Table solutions

[sindhuja]

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]

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]

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]

Any help on this pls…

-Sindhuja

[sindhuja]

Any help pls...

-sindhuja

[rbrhodes]

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]

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]

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]

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]

Any help on this pls..

-sindhuja

[rbrhodes]

Hi.

I'll take another look as soon as time permits

[mdmackillop]

Try this

[sindhuja]

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]

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]

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