Multiple Highest Numbers and Adjacent Cells

[jaclrsen]

I've got a workbook where there are dates and amounts entered in one sheet called 'dates'. the amounts are in one column and the dates are in an adjacent column. For instance, A7 is 01/01/2015 and B7 is $100.00, A8 is 02/01/2015 and B8 is $50.00 and so on.

On another sheet, I'm trying to display the highest amount and corresponding date. The only problem is I have more than one dates with the same highest amount. On this other sheet I have used the following:

This is for displaying the dates and adjacent cells

Please Login or Register  to view this content.

This is for displaying the highest value

Please Login or Register  to view this content.

On the sheet, I'm trying to display the highest amount in one cell which works with the above formula. The corresponding date in another cell is also shown but only for the first instance. If there are two or more dates with same amount value, it only picks up the first date. I'm trying to include all dates which have the highest number. Just display the highest number once and in the cell underneath the corresponding date and any other dates which have this same highest value.

For example, A1: Displays Highest Value A2: Displays Highest Value Date B2: Displays Highest Value Date (If Applicable) C2 Displays Highest Value Date (If Applicable) and so on. Like this A1: $100.00 B2: 01/01/2015 B3: 02/01/2015 B4: 03/01/2015. Please note, B3 and B4 are only shown if applicable. I know this can be done with LARGE but I just want them shown if they have the highest value.

Any help would be appreciated.

[Richard Buttrey]

Hi,

Please upload your workbook and manually add the results that you expect to see, clearly indicating which ranges are data and which are results.

[Glenn Kennedy]

Hopefully, this is what you need... If not, please post your worksheet.

[jaclrsen]

Hi Richard and Glenn, Thank you for your responses. I'm sorry Glenn because I'm using Excel 2003 and I'm getting a 'Name Error' on
some of the cells. I've created a workbook which includes three sheets. These are 'current results', 'dates' which is the source data,and 'desired results'. There is also some text to explain things. I hope this is ok. Thank you, JL

[Glenn Kennedy]

I didn't spot that you were on Excel 2003. It just needed a tweak to the error trapping bit. try this.

[jprlimey]

Glenn,

Very neat solution. I tried to follow your solution to better understand but got lost. I hope I'm not being too presumptuous but could you possibly explain the formula structure in more detail.

Thks

[Glenn Kennedy]

It's returning the dates from column A that match the highest value ($100) found in column B. It looks at all the values in B and returns true or false, depending n whether the value = $100, or not. It then returns the row numbers corresponding to the dates. Six is then subtracted from the row numbers ('cos the data starts in row 7 - after the subtraction the numbers run from 1 - x, corresponding to their positon in the array). Where the first bit found TRUE, the number is kept, where the first bit found FALSE, FALSE is kept. The numbers remaining (9,14 & 24) are the ARRAY row numbers that meet the criterion (Amount = $100). Excel then returns the lowest date, corresponding to that row number, then the next lowest, etc, etc...

Clear as mud??

[Tony Valko]

Try this...

Enter this formula in D15:

=IF(COLUMNS($D15:D15)>COUNTIF(Dates!$B7:$B30,MAX(Dates!$B7:$B30)),"",MAX(Dates!$B7:$B30))

Enter this array formula** in D16:

=IF(COUNT(D15),INDEX(Dates!$A:$A,SMALL(IF(Dates!$B7:$B30=$D15,ROW(Dates!$B7:$B30)),COLUMNS($D16:D16))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

Select D15:D16 and copy across until you get blanks.

[jaclrsen]

Hi, Thank you so much Glenn and Tony. Both solutions worked perfectly and they were what I was needing. Thank you so much for your help with this. I hope that you have a great day. JL

[Tony Valko]

You're welcome. We appreciate the feedback!