Select a sub set from a table of records

[Pete100mph]

Club Person Date Joined
Brough Susan 11/11/2011
HIM Alexis 10/12/2011
HIM Bill 12/07/2011
HIM Eric 01/01/2011
HIM Susan 13/11/2011
Humb Bill 11/07/2011
Other Kate 13/11/2011
Sykes Alexis 14/11/2011
Sykes Kate 14/11/2011
Tech Alexis 11/09/2011
Tech Beth 03/11/2011
Tech Susan 10/11/2010

I want to extract from this table the club that each person joined first: i.e.

Club Person Date Joined
HIM Eric 01/01/2011
Humb Bill 11/07/2011
Tech Alexis 11/09/2011
Tech Beth 03/11/2011
Tech Kate 11/08/2011
Tech Susan 10/11/2010

Can anyone advise please

[southward]

Hi,

There is probably an easier way to do this, but my solution involves three array formulas. If your list is extensive the combination of all the formulas might take a while to complete.

The first formula is for providing a unique list of names:

Please Login or Register  to view this content.

The second formula returns the first date associated with the name:

Please Login or Register  to view this content.

The third formula matches the name with the date to return the name of the club:

Please Login or Register  to view this content.

All of these formulas are arrays and need to be entered with CTRL + SHIFT + ENTER.

After entering the formula in the desired cell, copy downward.

See attached example that uses the data you provided.

Hope this is helpful.

Cheers

[Pete100mph]

Firstly, thank you very much for your prompt response, it is really appreciated.

I have not worked with array formulae before, I will copy your sensible approach of breaking the problem into three bits. I will use your answer to practice then, when I'm comfortable I will have a go at using it to solve my real problem - similar in concept to the small example I used on the forum - with over 4500 records.

Again - Thank you very much.

Peter

[southward]

You are very welcome. Glad I could be of some assistance.

Cheers

[CK76]

Alternate method using Pivot Table.

Add helper column to original list, named it "Min Date"
=MIN(IF($B$2:$B$13=B2,$C$2:$C$13))

Load range to Pivot Table.

Add calculated column in Pivot Table.
='Date Joined' ='Min Date'

Add "Date Joined" and the calculated field to Values field (both as SUM in this case).

Add "Club" as Parent Row Label and "Person" as child.

Right click on "Person" row label and set filter by values. Sum of "Calculated Column" = 1.

See attached sample.