Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
how can i locate duplicate data in an excel data table?
i have repeating customers that book appointments in advance. this
appointment list winds up in an excel sheet. i want to find future appointments that have the same client ID as an appointment today (to find out how many of my current customers are booked for future visits). neil |
#2
|
|||
|
|||
seems like everybody is using either a conditional format or some other
means. i'm looking for something that looks up TODAY as a date, then looks over at a customer ID, and THEN looks at FUTURE bookings for a one or more bookings by that SAME customer, finally tallying how many of TODAY's customers have FUTURE bookings. with approximately 100 appointments a day, it's hard to track this manually. i'll keep looking for an answer in this thread, and if any programmers want more specific info, i would be happy to pay for help. would it help if i imported the datafile into access? then is there a query or something? i'm pretty amateur at this, but not computer stupid. neil "neil" wrote: i have repeating customers that book appointments in advance. this appointment list winds up in an excel sheet. i want to find future appointments that have the same client ID as an appointment today (to find out how many of my current customers are booked for future visits). neil |
#3
|
|||
|
|||
Hi!
You need to give more detail as to how you file is layed out. Be very specific! Biff -----Original Message----- seems like everybody is using either a conditional format or some other means. i'm looking for something that looks up TODAY as a date, then looks over at a customer ID, and THEN looks at FUTURE bookings for a one or more bookings by that SAME customer, finally tallying how many of TODAY's customers have FUTURE bookings. with approximately 100 appointments a day, it's hard to track this manually. i'll keep looking for an answer in this thread, and if any programmers want more specific info, i would be happy to pay for help. would it help if i imported the datafile into access? then is there a query or something? i'm pretty amateur at this, but not computer stupid. neil "neil" wrote: i have repeating customers that book appointments in advance. this appointment list winds up in an excel sheet. i want to find future appointments that have the same client ID as an appointment today (to find out how many of my current customers are booked for future visits). neil . |
#4
|
|||
|
|||
AptID Date Op ProvID Time Name
PtID 10932 4/21/2004 RSCH HYG3 17:30 Michael, Lynn 1832 11830 4/30/2004 CON1 NEWP 11:00 Waymier, Marcee L 2000 11829 4/20/2004 CON1 NEIL 11:30 Waymier, Marcee L 2000 11828 4/20/2004 CON1 NEIL 9:50 Henry, Michelle 1861 11827 4/22/2004 GV-4 NEIL 9:00 West, Richard 579 10931 4/21/2004 GV-3 NEIL 15:20 Daniels, Delores 1793 10930 3/31/2004 RSCH NEIL 16:00 Rudio, Christina 1846 10929 3/23/2004 GV-3 NEIL 14:40 Corcoran, Martha 762 10928 9/23/2004 RSCH HYG3 14:30 Corcoran, Martha 762 10927 9/28/2004 GV-2 HYG3 15:30 Daniels, Delores 1793 10926 3/24/2004 RSCH NEIL 15:00 xxx, Rebecca 1326 20301 7/19/2005 GV-7 HYG6 11:10 mmm, Jean 596 10925 3/24/2004 GV-3 NEIL 14:00 ccc, Richard 1586 10923 10/14/2004 GV-5 CHIP 8:30 ccc, Michele 1360 16694 10/26/2004 GV-5 CHIP 8:30 ppp, Robyn 2603 10922 4/2/2004 GV-3 NEIL 8:30 bbb, MICHELLE L 1617 10921 4/1/2004 GV-3 NEIL 8:30 bbb, MICHELLE L 1617 "Biff" wrote: Hi! You need to give more detail as to how you file is layed out. Be very specific! Biff -----Original Message----- seems like everybody is using either a conditional format or some other means. i'm looking for something that looks up TODAY as a date, then looks over at a customer ID, and THEN looks at FUTURE bookings for a one or more bookings by that SAME customer, finally tallying how many of TODAY's customers have FUTURE bookings. with approximately 100 appointments a day, it's hard to track this manually. i'll keep looking for an answer in this thread, and if any programmers want more specific info, i would be happy to pay for help. would it help if i imported the datafile into access? then is there a query or something? i'm pretty amateur at this, but not computer stupid. neil "neil" wrote: i have repeating customers that book appointments in advance. this appointment list winds up in an excel sheet. i want to find future appointments that have the same client ID as an appointment today (to find out how many of my current customers are booked for future visits). neil . |
#5
|
|||
|
|||
If I understand what you're looking for, does this work for you?
Labels in A1 to G1. Data in A2 to G100. Enter today's date in H1 - <Ctrl <; Enter this formula in H2, and drag down to copy as needed, or double click on the "fill handle" of H2, to copy the formula down column H as far as there is data in Column G: =SUMPRODUCT((TEXT(B2,"mm/dd/yy")=TEXT($H$1,"mm/dd/yy"))*(TEXT($B$2:$B$100,"m m/dd/yy")TEXT($H$1,"mm/dd/yy"))*($G$2:$G$100=G2)) You can of course, enter any date you wish in H1, to perhaps retrieve other information. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "neil" wrote in message ... AptID Date Op ProvID Time Name PtID 10932 4/21/2004 RSCH HYG3 17:30 Michael, Lynn 1832 11830 4/30/2004 CON1 NEWP 11:00 Waymier, Marcee L 2000 11829 4/20/2004 CON1 NEIL 11:30 Waymier, Marcee L 2000 11828 4/20/2004 CON1 NEIL 9:50 Henry, Michelle 1861 11827 4/22/2004 GV-4 NEIL 9:00 West, Richard 579 10931 4/21/2004 GV-3 NEIL 15:20 Daniels, Delores 1793 10930 3/31/2004 RSCH NEIL 16:00 Rudio, Christina 1846 10929 3/23/2004 GV-3 NEIL 14:40 Corcoran, Martha 762 10928 9/23/2004 RSCH HYG3 14:30 Corcoran, Martha 762 10927 9/28/2004 GV-2 HYG3 15:30 Daniels, Delores 1793 10926 3/24/2004 RSCH NEIL 15:00 xxx, Rebecca 1326 20301 7/19/2005 GV-7 HYG6 11:10 mmm, Jean 596 10925 3/24/2004 GV-3 NEIL 14:00 ccc, Richard 1586 10923 10/14/2004 GV-5 CHIP 8:30 ccc, Michele 1360 16694 10/26/2004 GV-5 CHIP 8:30 ppp, Robyn 2603 10922 4/2/2004 GV-3 NEIL 8:30 bbb, MICHELLE L 1617 10921 4/1/2004 GV-3 NEIL 8:30 bbb, MICHELLE L 1617 "Biff" wrote: Hi! You need to give more detail as to how you file is layed out. Be very specific! Biff -----Original Message----- seems like everybody is using either a conditional format or some other means. i'm looking for something that looks up TODAY as a date, then looks over at a customer ID, and THEN looks at FUTURE bookings for a one or more bookings by that SAME customer, finally tallying how many of TODAY's customers have FUTURE bookings. with approximately 100 appointments a day, it's hard to track this manually. i'll keep looking for an answer in this thread, and if any programmers want more specific info, i would be happy to pay for help. would it help if i imported the datafile into access? then is there a query or something? i'm pretty amateur at this, but not computer stupid. neil "neil" wrote: i have repeating customers that book appointments in advance. this appointment list winds up in an excel sheet. i want to find future appointments that have the same client ID as an appointment today (to find out how many of my current customers are booked for future visits). neil . |
#6
|
|||
|
|||
ragdyer,
i modified your formula (eliminating the TEXT functions as i had imported the .txt file into excel and it assigned numeric values for dates), and WOW that got me over a big hurdle. for the next folks who read this, i added the names of service providers across from I1, J1, K1, etc. and modified ragdyer's formula to incorporate that reference: =SUMPRODUCT(($B2=$H$1)*($B$2:$B$31$H$1)*($D$2:$D$ 31=I$1)*($G$2:$G$31=$G2)) now, i get a numeric result that tells me "for every person who came in today, how many future appointments are in the book, and who are they scheduled to see" this totally rocks. neil "Ragdyer" wrote: If I understand what you're looking for, does this work for you? Labels in A1 to G1. Data in A2 to G100. Enter today's date in H1 - <Ctrl <; Enter this formula in H2, and drag down to copy as needed, or double click on the "fill handle" of H2, to copy the formula down column H as far as there is data in Column G: =SUMPRODUCT((TEXT(B2,"mm/dd/yy")=TEXT($H$1,"mm/dd/yy"))*(TEXT($B$2:$B$100,"m m/dd/yy")TEXT($H$1,"mm/dd/yy"))*($G$2:$G$100=G2)) You can of course, enter any date you wish in H1, to perhaps retrieve other information. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "neil" wrote in message ... AptID Date Op ProvID Time Name PtID 10932 4/21/2004 RSCH HYG3 17:30 Michael, Lynn 1832 11830 4/30/2004 CON1 NEWP 11:00 Waymier, Marcee L 2000 11829 4/20/2004 CON1 NEIL 11:30 Waymier, Marcee L 2000 11828 4/20/2004 CON1 NEIL 9:50 Henry, Michelle 1861 11827 4/22/2004 GV-4 NEIL 9:00 West, Richard 579 10931 4/21/2004 GV-3 NEIL 15:20 Daniels, Delores 1793 10930 3/31/2004 RSCH NEIL 16:00 Rudio, Christina 1846 10929 3/23/2004 GV-3 NEIL 14:40 Corcoran, Martha 762 10928 9/23/2004 RSCH HYG3 14:30 Corcoran, Martha 762 10927 9/28/2004 GV-2 HYG3 15:30 Daniels, Delores 1793 10926 3/24/2004 RSCH NEIL 15:00 xxx, Rebecca 1326 20301 7/19/2005 GV-7 HYG6 11:10 mmm, Jean 596 10925 3/24/2004 GV-3 NEIL 14:00 ccc, Richard 1586 10923 10/14/2004 GV-5 CHIP 8:30 ccc, Michele 1360 16694 10/26/2004 GV-5 CHIP 8:30 ppp, Robyn 2603 10922 4/2/2004 GV-3 NEIL 8:30 bbb, MICHELLE L 1617 10921 4/1/2004 GV-3 NEIL 8:30 bbb, MICHELLE L 1617 "Biff" wrote: Hi! You need to give more detail as to how you file is layed out. Be very specific! Biff -----Original Message----- seems like everybody is using either a conditional format or some other means. i'm looking for something that looks up TODAY as a date, then looks over at a customer ID, and THEN looks at FUTURE bookings for a one or more bookings by that SAME customer, finally tallying how many of TODAY's customers have FUTURE bookings. with approximately 100 appointments a day, it's hard to track this manually. i'll keep looking for an answer in this thread, and if any programmers want more specific info, i would be happy to pay for help. would it help if i imported the datafile into access? then is there a query or something? i'm pretty amateur at this, but not computer stupid. neil "neil" wrote: i have repeating customers that book appointments in advance. this appointment list winds up in an excel sheet. i want to find future appointments that have the same client ID as an appointment today (to find out how many of my current customers are booked for future visits). neil . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is the order of my data table opposite from graph? | Charts and Charting in Excel | |||
How do you rotate a table of excel data from horizontal to vertic. | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | New Users to Excel | |||
pasting excel data in a powerpoint slide | Excel Discussion (Misc queries) | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |