Excel - Formula to determine most recent appearance of a string

Hi All,

I need a formula to identify the most 'recent' appearance of a string in a data set.

The data is customer codes (they are generally six character alpha-numerics, but that doesn't likely matter).  The codes represent the last time that a customer had an interaction with the business.

The data is laid out in the worksheet thus:

Dates across the top, with customer codes underneath the date (with a single spacer row).
The customer codes are not sorted, and even if they were, a given code would not necessarily appear on the same row under different dates, and the depth (number of rows) of codes under a date can vary from date to date.


Question:

What formula can I use to identify the most recent date that a given code appears under?

I would strongly prefer a formula using native functions rather than using VBA or a UDF if possible.


Thanks,

Alan.
LVL 23
AlanConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
If you have Excel 2010 or later, you can use the AGGREGATE function in a regular formula:
=IFERROR(INDEX($B$1:$G$1,1,AGGREGATE(14,6,COLUMN($B$3:$G$11)/($B$3:$G$11=$A12),1)-COLUMN($B$3)+1),"")

The formula above assumes that the desired Customer Code is in cell A12, that the dates are in B1:G1 and that the customer codes are in B3:G11. The ranges for the customer codes and dates may extend beyond your actual data.
LastDateQ28268038.xlsx
0
AlanConsultantAuthor Commented:
Hi,

Apologies - I should have said, we have only Excel 2007 available.

Is it possible to re-write that using native Excel 2007 functions?  I appreicate it might be somewhat longer!

Thanks,

Alan.
0
byundtMechanical EngineerCommented:
You can use an array-entered formula in Excel 2007:
=IFERROR(INDEX($B$1:$G$1,1,MAX(IF($B$3:$G$11=$A12,COLUMN($B$3:$G$11)))-COLUMN($B$3)+1),"")

To array-enter a formula:
1. Click in the formula bar
2. Hold the Control and Shift keys down
3. Hit Enter
4. Release all three keys. Excel should respond by adding curly braces surrounding your formula. If you don't see them (or if you see an empty string where there should have been an answer), then repeat steps 1 through 4.
LastDateQ28268038.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlanConsultantAuthor Commented:
Brilliant - thank you very much.

Alan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.