Reducing duplicate courses to totals of each day

[Adam]

It might be easier if i just explain my scenerio.

In each row of my spreadsheet is one person.
The code (Column A) represents a course that I run.
The date (Column G) represents the date that it ran.
Not everybody has a date arranged yet and so there are also blanks in Column G.
There is usually 60+ people attending each course.
More than one course can occur on one day.

What I would like is a function (on seperate sheet) to put

A list of the dates with the newest at the top in Column A
The course code in Column B
Only have one duplicate of each code and date combination

This would mean there maybe duplicates of the date when there is more than
one course on that day.

For example-

Column A > Column G

PSPNUR > 02/Apr/08
PSPNUR > 08/Apr/08
PSPNUR > 08/Apr/08
USNUAD > 02/Apr/08
PSPNUR > 02/Apr/08
USNUCB > 16/Apr/08
USNUCB > 16/Apr/08
USNUCB > 15/Mar/08
USNUAD > 10/Apr/08
USNUAD > 10/Apr/08
PSPNUR > 10/Apr/08

Turns into

USNUCB > 16/Apr/08
USNUAD > 10/Apr/08
PSPNUR > 10/Apr/08
PSPNUR > 08/Apr/08
PSPNUR > 02/Apr/08
USNUAD > 02/Apr/08
USNUCB > 15/Mar/08



Hope you can help!

 

[vezerid]

Here is a formula implementation, which requires some work.
I assume that you have an ordinal number to the left of your course
codes (i.e. A=1, 2, 3..., B=USNUCB, USNUAD,... and similarly all
columns have moved by one, so dates are now in H)
I further assume that your data start from A2 and your "sorted/
filtered" list will start at K2, where you want the ordinal of the
latest course. In K2:

=LOOKUP(99^99,A:A)

In K3, the following *array* formula (commit with Shift+Ctrl+Enter),
which you copy down:

=MAX(IF(NOT(ISNUMBER(MATCH($B$2:$B$1000&$H$2:$H$1000,$L$2:L2&$M
$2:M2,0)))*($A$2:$A$1000<K2),$A$2:$A$1000))

In L2

=VLOOKUP($K2,$A:$H,2,0)

In M2

=VLOOKUP($K2,$A:$H,3,0)

Copy L2 and M2 down, parallel to the formulas in K.

HTH
Kostis Vezerides

 

[Adam]

Sadly this isn't working but don't worry about it - Ill figure it out

Thank you for your time and effort

Regards

 

[Sivakumar O. K.]

First give a column header for the two columns.

Id Date
PSPNUR 2-Apr-08
PSPNUR 8-Apr-08
PSPNUR 8-Apr-08
USNUAD 2-Apr-08
PSPNUR 2-Apr-08
USNUCB 16-Apr-08
USNUCB 16-Apr-08
USNUCB 15-Mar-08
USNUAD 10-Apr-08
USNUAD 10-Apr-08
PSPNUR 10-Apr-08

Select these records (including the header). Then select the menu Data ->
Filter -> Advanced Filter. Check the unique records option. This will
eliminate the duplicate rows.

Now go to Data -> Sort, Select the Date column and check 'Descending' option.
The result:

Id Date
USNUCB 16-Apr-08
PSPNUR 10-Apr-08
PSPNUR 2-Apr-08
USNUAD 10-Apr-08
PSPNUR 8-Apr-08
USNUAD 2-Apr-08
USNUCB 15-Mar-08