Match against three columns

[thompsob18]

Howdy,

I've been asked to provide a solution for a rather difficult Excel problem.

A client has a worksheet with about 14000 rows, containing course information for their staff.

The three items we're looking at here are the staff member's name, course code, and completion date. For example (in CSV):

Name,Code,Date
"John Doe",ODHS75A,29/04/2002

For the purpose of this exercise, assume:

1:1 - Headings
A2:A14000 - Names
B2:B14000 - Codes
C2:C14000 - Dates

What we want to do is set up another sheet, with staff names in column A, and course codes in row 1, with matching completion dates making up the bulk of the table.

I could normally match against each name & code, with an INDEX/MATCH array formula. However the problem is that some courses are taken regularly by staff, hence there is more than one date match for a given name and course code. In that case, the most recent completion date needs to be returned.

I really need to implement this with formulas (no VB, as the client has to maintain the sheet themselves). It doesn't have to be a single cell formula, I'm happy to use multiple columns per course code, or multiple rows per staff member, and hide any "working" cells if necessary.

Any and all assistance is much appreciated.

Thanks

-Ben.

[Rocky E]

Hi Ben,
You did a WONDERFUL job of defining your input, but you don't provide enough on the output you are looking for. Give us the same detail for the output, please...

Rocky...

[Yogi Anand]

Hi thompsob18:
This is how I approached it -- step by step ...

1. I first inserted a column C to concatenate Name & Course entries -- naming this field NameCode. I converted the formulas in NameCode field to values.
2. Then I sorted the list by Date--descending, and then NameCode-- ascending.
3. Then I inserted a column either side of NameCode field. Then I used Advanced Filter on just the NameCode field to extract Unique records only.
4. Then I copied the unique records to a new worksheet, say cells A1:A14000
5. Then I used the VLOOKUP function to lookup the date to correspond each entry in column A of this sheet using the original list in sheet1. I could also have used the Index and Match functions to accomplish this.
6. Then it was just a matter of parsing the NameCode field back into Name and Code fields.

Hope This Helps

Regards!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 16:56 ]

[thompsob18]

OK, here's what I require in for output, in more detail:

1:1 - Course Codes
A:A - Saff Names
B2:IV65536 - Most Recent Date matching course code & name

For example, if:
A4 = "John Doe"
D1 = ODHS75A
Then formula in cell D4 would match the course completion date for "John Doe" & "ODHS75A" in the source table (Columns A & B in my original post), and if there are multiple matches, return the most recent completion date (Column C).

Hope this makes things a bit clearer.

One thing I've thought about: Finding the "most recent date" could be done with a simple IF X>Y type comparison of the datevalues... Food for thought, anyway.

[thompsob18]

Hi Yogi, thanks for your suggestion.

Unfortunately, while I would be happy to manipulate the data in the way that you've suggested, my client is not very Excel literate, and would have difficulty in regularly performing the steps you describe.

What I'm trying to develop here is a foolproof, black-box worksheet ie: If I were to lock the cells containing the search formulas, the client would be able to enter their own names and course codes in column A and Row 1, and the matches would "magically appear" without any extra steps.

[Chris Davison]

Hi Ben,

would allow a single click as acceptable ?

If so, maybe a pivot table would help out.

Give your data a dynamic named ranged so you don't have to worry about it expanding or shrinking over time, then set up the pivot table as follows :

Row field = name
Column field = course code
Data = date

double click on Data and assign it "max" rather than "count" or "sum"

then format this field as date and whatever is appropriate (say, dd-mm-yyyy)

and it should do exactly what you need, just needing a right click to refresh on addition of new records :

Microsoft Excel - thompsob18.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
D47		=
*	A	B	C	D	E	F	G	H
1	Name	Course Code	Date	*	Max of Date	Code	*	*
2	John	1	:alert('36892')>01/01/2001	*	Name	1	2	3
3	John	2	:alert('=+C2+1')>02/01/2001	*	George	:alert('36897')>06-Jan-01	:alert('36898')>07-Jan-01	:alert('36896')>05-Jan-01
4	John	3	:alert('=+C3+1')>03/01/2001	*	John	:alert('36898')>07-Jan-01	:alert('36899')>08-Jan-01	:alert('36900')>09-Jan-01
5	John	1	:alert('=+C4+1')>04/01/2001	*	Paul	:alert('36904')>13-Jan-01	:alert('36902')>11-Jan-01	:alert('36903')>12-Jan-01
6	John	2	:alert('=+C5+1')>05/01/2001	*	Ringo	:alert('36902')>11-Jan-01	:alert('36900')>09-Jan-01	:alert('36901')>10-Jan-01
7	John	3	:alert('=+C6+1')>06/01/2001	*	*	*	*	*
8	John	1	:alert('=+C7+1')>07/01/2001	*	*	*	*	*
9	John	2	:alert('=+C8+1')>08/01/2001	*	*	*	*	*
10	John	3	:alert('=+C9+1')>09/01/2001	*	*	*	*	*
11	Paul	1	:alert('36892')>01/01/2001	*	*	*	*	*
12	Paul	2	:alert('=+C11+1')>02/01/2001	*	*	*	*	*
13	Paul	3	:alert('=+C12+1')>03/01/2001	*	*	*	*	*
14	Paul	1	:alert('=+C13+1')>04/01/2001	*	*	*	*	*
15	Paul	2	:alert('=+C14+1')>05/01/2001	*	*	*	*	*
16	Paul	3	:alert('=+C15+1')>06/01/2001	*	*	*	*	*
17	Paul	1	:alert('=+C16+1')>07/01/2001	*	*	*	*	*
18	Paul	2	:alert('=+C17+1')>08/01/2001	*	*	*	*	*
19	Paul	3	:alert('=+C18+1')>09/01/2001	*	*	*	*	*
20	Paul	1	:alert('=+C19+1')>10/01/2001	*	*	*	*	*
21	Paul	2	:alert('=+C20+1')>11/01/2001	*	*	*	*	*
22	Paul	3	:alert('=+C21+1')>12/01/2001	*	*	*	*	*
23	Paul	1	:alert('=+C22+1')>13/01/2001	*	*	*	*	*
24	George	2	:alert('36892')>01/01/2001	*	*	*	*	*
25	George	3	:alert('=+C24+1')>02/01/2001	*	*	*	*	*
26	George	1	:alert('=+C25+1')>03/01/2001	*	*	*	*	*
27	George	2	:alert('=+C26+1')>04/01/2001	*	*	*	*	*
28	George	3	:alert('=+C27+1')>05/01/2001	*	*	*	*	*
29	George	1	:alert('=+C28+1')>06/01/2001	*	*	*	*	*
30	George	2	:alert('=+C29+1')>07/01/2001	*	*	*	*	*
31	Ringo	3	:alert('36892')>01/01/2001	*	*	*	*	*
32	Ringo	1	:alert('=+C31+1')>02/01/2001	*	*	*	*	*
33	Ringo	2	:alert('=+C32+1')>03/01/2001	*	*	*	*	*
34	Ringo	3	:alert('=+C33+1')>04/01/2001	*	*	*	*	*
35	Ringo	1	:alert('=+C34+1')>05/01/2001	*	*	*	*	*
36	Ringo	2	:alert('=+C35+1')>06/01/2001	*	*	*	*	*
37	Ringo	3	:alert('=+C36+1')>07/01/2001	*	*	*	*	*
38	Ringo	1	:alert('=+C37+1')>08/01/2001	*	*	*	*	*
39	Ringo	2	:alert('=+C38+1')>09/01/2001	*	*	*	*	*
40	Ringo	3	:alert('=+C39+1')>10/01/2001	*	*	*	*	*
41	Ringo	1	:alert('=+C40+1')>11/01/2001	*	*	*	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo