Find difference in min and max value

  • Thread starter Thread starter DavidH56
  • Start date Start date
D

DavidH56

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.
 
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.
Click to expand...

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.
 
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.
 
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.
Click to expand...

Where range is your data in? Which part of the formula is returning Ref?
 
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.
 
Back
Top