Find difference in min and max value
 

Hi,

I have a spreadsheet which has 17 columns of data with anywhere from 3,000
to 12,000 rows. I have a header with the autofilter on and sorted by column
F ascendingly. I would appreciate if someone could please create a macro
for me which would evaluate duplicates in column F and for each group of
these duplicates calculate the difference in the minimum and maximum values
which are located in column L and create a new column beside column L (which
would now be column M) to place the results to be located beside the last
similiar item of duplicates. There will be anywhere from 2 to 5 like items
from column F on each report.

Thank you in advance for your assistance.
--
By persisting in your path, though you forfeit the little, you gain the
great.

Find difference in min and max value
 

On Tue, 31 Mar 2009 08:57:02 -0700, DavidH56
wrote:

Hi,

I have a spreadsheet which has 17 columns of data with anywhere from 3,000
to 12,000 rows. I have a header with the autofilter on and sorted by column
F ascendingly. I would appreciate if someone could please create a macro
for me which would evaluate duplicates in column F and for each group of
these duplicates calculate the difference in the minimum and maximum values
which are located in column L and create a new column beside column L (which
would now be column M) to place the results to be located beside the last
similiar item of duplicates. There will be anywhere from 2 to 5 like items
from column F on each report.

Thank you in advance for your assistance.

Put this formula in M2 (or whichever row your data starts on)

=IF(AND(F2=F1,F2<F3),MAX(($F$2:$F$46=F2)*($L$2:$L $46))-MIN(IF($F$2:$F$46=F2,($F$2:$F$46=F2)*($L$2:$L$46), "")),"")

It's an array formula, so you have to enter with Control+Shift+Enter, not
just enter. Change the cell references to fit your data.

Fill down for all your data.

If your data is sorted by Col F then by Col L, you could simplify this
formula and make it faster. You didn't say that was the case, so I assumed
it was not.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Find difference in min and max value
 

Thank you very much for your response Dick. I tried the formula as an array
as you said and copied down. I sorted by F and L proior to entering the
formula. I got a #REF! symbol in the last similiar cell in column M. I tried
again and got the same.


--
By persisting in your path, though you forfeit the little, you gain the
great.



"Dick Kusleika" wrote:

On Tue, 31 Mar 2009 08:57:02 -0700, DavidH56
wrote:

Hi,

I have a spreadsheet which has 17 columns of data with anywhere from 3,000
to 12,000 rows. I have a header with the autofilter on and sorted by column
F ascendingly. I would appreciate if someone could please create a macro
for me which would evaluate duplicates in column F and for each group of
these duplicates calculate the difference in the minimum and maximum values
which are located in column L and create a new column beside column L (which
would now be column M) to place the results to be located beside the last
similiar item of duplicates. There will be anywhere from 2 to 5 like items
from column F on each report.

Thank you in advance for your assistance.

Put this formula in M2 (or whichever row your data starts on)

=IF(AND(F2=F1,F2<F3),MAX(($F$2:$F$46=F2)*($L$2:$L $46))-MIN(IF($F$2:$F$46=F2,($F$2:$F$46=F2)*($L$2:$L$46), "")),"")

It's an array formula, so you have to enter with Control+Shift+Enter, not
just enter. Change the cell references to fit your data.

Fill down for all your data.

If your data is sorted by Col F then by Col L, you could simplify this
formula and make it faster. You didn't say that was the case, so I assumed
it was not.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Find difference in min and max value
 

On Tue, 31 Mar 2009 17:32:01 -0700, DavidH56
wrote:

Thank you very much for your response Dick. I tried the formula as an array
as you said and copied down. I sorted by F and L proior to entering the
formula. I got a #REF! symbol in the last similiar cell in column M. I tried
again and got the same.

Where range is your data in? Which part of the formula is returning Ref?
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Find difference in min and max value
 

Dick, I tried it again this morning after I got to work and man it worked
beautifully. I had obviously left out one of the parenthesis. You are a
true life and time saver. Thank you very much for your time and attention.


--
By persisting in your path, though you forfeit the little, you gain the
great.



"Dick Kusleika" wrote:

On Tue, 31 Mar 2009 17:32:01 -0700, DavidH56
wrote:

Thank you very much for your response Dick. I tried the formula as an array
as you said and copied down. I sorted by F and L proior to entering the
formula. I got a #REF! symbol in the last similiar cell in column M. I tried
again and got the same.

Where range is your data in? Which part of the formula is returning Ref?
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com