A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Reducing duplicate courses to totals of each day



 
 
Thread Tools Display Modes
  #1  
Old April 15th, 2008, 05:32 PM posted to microsoft.public.excel.worksheet.functions
Adam
external usenet poster
 
Posts: 545
Default Reducing duplicate courses to totals of each day

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!


  #2  
Old April 15th, 2008, 07:00 PM posted to microsoft.public.excel.worksheet.functions
vezerid
external usenet poster
 
Posts: 739
Default Reducing duplicate courses to totals of each day

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$10 00,$L$2:L2&$M
$2:M2,0)))*($A$2:$A$1000K2),$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
On Apr 15, 7:32 pm, Adam wrote:
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!


  #3  
Old April 17th, 2008, 10:13 AM posted to microsoft.public.excel.worksheet.functions
Adam
external usenet poster
 
Posts: 545
Default Reducing duplicate courses to totals of each day

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

Thank you for your time and effort

Regards


"vezerid" wrote:

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$10 00,$L$2:L2&$M
$2:M2,0)))*($A$2:$A$1000K2),$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
On Apr 15, 7:32 pm, Adam wrote:
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!



  #4  
Old June 20th, 2008, 01:10 PM posted to microsoft.public.excel.worksheet.functions
Sivakumar O. K.
external usenet poster
 
Posts: 2
Default Reducing duplicate courses to totals of each day

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




"Adam" wrote:

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

Thank you for your time and effort

Regards


"vezerid" wrote:

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$10 00,$L$2:L2&$M
$2:M2,0)))*($A$2:$A$1000K2),$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
On Apr 15, 7:32 pm, Adam wrote:
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!



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.