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

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

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
>.
>

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
> >.
> >
>

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" <neil@discussions.microsoft.com> wrote in message
news:9FE3FDA1-AAE5-4AD0-A572-40780AB4954C@microsoft.com...
> 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
> > >.
> > >
> >

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" <neil@discussions.microsoft.com> wrote in message
> news:9FE3FDA1-AAE5-4AD0-A572-40780AB4954C@microsoft.com...
> > 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
> > > >.
> > > >
> > >
>
>

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"neil" <neil@discussions.microsoft.com> wrote in message
news:B8A8B839-D4D6-456C-9232-AC99BC1719DA@microsoft.com...
> 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" <neil@discussions.microsoft.com> wrote in message
> > news:9FE3FDA1-AAE5-4AD0-A572-40780AB4954C@microsoft.com...
> > > 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
> > > > >.
> > > > >
> > > >
> >
> >