List unique items from list and find first negative value for each unique record

  • Hi all,


    I am struggling to find a solution to this problem, and I am hoping someone would be able to help me out:


    I have a list of items in column A that repeat a random number of times each with a corresponding date in column B and quantity in column C.


    What I am hoping for is a formula of some sort by where I could find the unique list of all items in the list in column G, the date that corresponds to the first negative record for that item in column H, and the value of the first negative record for that item in column I. If a non-negative item is not found, then the result should be the last record in the list for that item.


    I have included a sample with the desired result in the attachment.


    VBA solution would be ok as well, but I would like to see if a formula is possible at all...


    Thank you in advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!