Formula to return the last text and value within duplicates

[Shellybelly]

Does anyone know a formula that can return the last text and value within duplicates? for example i have a list of 5 first breaks with time values.

Column 1 Column 2
First Break 14:00
First Break 14:01
First Break 14:02
First Break 14:03
First Break 14:04

with the above example I would like a formula that only pull the Text with the highest time value in column 2. Is there a formula or a macro that can do this? I am stuck.

Note there are also other duplicate statuses same as the example.

Once I get the data to Pull the statuses as one with the highest value, i can run my Filter Macro to copy the data to another sheet.

Can anyone help me? see my attachment below. My blue field is my formula field.

[JohnTopley]

TRy

=INDEX($A$1:$A$5,MATCH(MAX($B$1:$B$5),$B$1:$B$5,0))

I cannot download file as forum gives "Invalid Attachment" message.

[Speshul]

This should do it, however there are duplicate values in Column B. This formula will pull the text associated with the highest number, and the last instance of that number in the list.


Use Control + Shift + Enter to enter this Array Formula

Please Login or Register  to view this content.

[Speshul]

(Note: The formula above mine works too, and will pull the first instance in cases where there are multiple matches for Highest Date)

[Shellybelly]

for some reason the formulas are not working, they just mirror the exact info that I have. love the formulas doh but I would like for the formula to only pull the last first break and the highest time value associated with in the next door column. I only need one first break to be returned with the highest break time recorded.

[Shellybelly]

Can anyone download the file now? I am using column k which has the duplicates and column c which has the names and column F which has the times.

Please let me know...I am more than willing to work through this? More than one head can do amazing things

[Speshul]

you have to hold Control + Shift will entering my formula, and make sure you modified the ranges in JohnTopley's formula to include the entire data set of your file (his example only runs to row 5)

[Shellybelly]

I'm not sure what I am doing wrong but its not working. Is it possible you can update the sample file and so I can see where I am going wrong?

[Speshul]

I can't upload from the computer I am on at the moment, if you see {}'s around my formula you are doing it right though, if that's the case I am not sure what is wrong

In the formula bar, it should look like

{=INDEX(A:A,MAX(--(B:B=MAX(B:B))*ROW(B:B)))}

[Shellybelly]

The formula works but it just return the wrong results...Its either showing blanks or list all the first break that;s in the column.

[Speshul]

Ahhh I gotcha, I misunderstood the requirements.

I thought you were looking for the text in column A which corresponds with the last-occurance of the largest value in column B - that's what my formula is doing.


Let me look at this again

[Shellybelly]

I really appreciate your help on this. my brain is on pause lol

[Shellybelly]

Hi Speshul any other way i can do this?

[AB33]

I could have a go using a macro, but I do not understand your request.
Please include a desired result sheet as an attachment.

[Shellybelly]

Hi AB33, attached is my attachment showing how I would like the results to look after the macro.

I would like for all the agents statuses with this highest duration to be listed as one instance instead of duplicates as shown in the before macro sheet.

example:

one agent may have 5 first break status with the same duration or sometimes with each duration being a second higher than the previous.

Column 1 Column 2
First Break 14:00
First Break 14:01
First Break 14:02
First Break 14:03
First Break 14:04

I would like for the First Break status with the highest duration be listed as shown in the after macro sheet.

[JohnTopley]

VBA solution:

Please Login or Register  to view this content.

Click RUN button on tab "After Macro"