Solved: Selecting Value from List based on Multiple Criteria

[radach]

Hey all, tried searching the borad for a few days now to find an answer to this issue I am having

I have a list of tickets worked by the agents and it is in 4 columns
Column A - Ticket Queue
Column B - Ticket Date
Column C - Ticket number
Column D - ticket State

What I am trying to do is find the oldest ticket in each queue in each state for example
I need the date for the oldest ticket in the Billing Queue in Pending Customer status, the date for the oldest ticket in the Billing Queue in Open status

Any ideas on how to do this? I have tried using match and index formulas I found on this site but I cant get it to evaluate the State and Queue in determingin the answer

[mdmackillop]

Can you post your workbook with sample data? Use Manage Attachments in the Go Advanced section.

[radach]

Here is a sample. The only difference between this data and what I will be using is that the timestamp column (B) will contain time and date vs. the date that is on the attachment

[CBrine]

radach,
The date column you are using is actually not a real date, it's a text string. If your data remains that way, you will not be able to find the value you are looking for. You also have codes listing your current status, which are meaningless without further explanation.

So it sounds like you want a solution that provides a summary by
Current Queue
Showing what the lowest timestamp is for an open tickets.

ex
Tier 2 Tech Support Sev 2 10/23/2006
Tier 2 Billing

[CBrine]

radach,
The date column you are using is actually not a real date, it's a text string. If your data remains that way, you will not be able to find the value you are looking for. You also have codes listing your current status, which are meaningless without further explanation.

So it sounds like you want a solution that provides a summary by
Current Queue
Showing what the lowest timestamp is for an open tickets.

ex
Tier 2 Tech Support Sev 2 10/23/2006
Tier 2 Billing 10/23/2006
Etc....

Let me know what the status code for open is, and we can continue.

Cal

[radach]

Here is the updated sheet for use...

Status 4 is open
Status 14 is pending customer

What I would like ot see is

Tier 2 Billing - Open - 2007-01-16 14:40:00
Cancellation Refunds 1st Touch - Pending Customer - 2007-01-16 14:39:42
....

[CBrine]

I think this is what you are looking for. Take a look at the attached sheet. The formula is pretty complex, since it's an array formula embedded in a index(match()) structure. If you have any question just ask.

PS
Your date values were still not valid excel dates. You need to do a search and replace on the "-" and change it to "/" to be able to make this work.
(If this is an English version of Excel you will need to change, in another language, I'm not sure)

HTH
Cal

[radach]

It works... Thanks

[mdmackillop]

Hi Radach
If this is Solved, can you mark it so using Thread Tools
Regards
MD