|
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! |
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$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 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! |
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$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 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! |
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$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 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! |
| All times are GMT +1. The time now is 02:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com