Hi!
Based on your explanation and the sample data I think
your "Yes" result is incorrect for:
a3 456 def 6/12/04
a7 456 def 1/20/05 Yes
6/12/04 is older than 1/20/2005 so shouldn't the table
look like this:
a1 123 abc 10/1/04 Yes
a2 132 xyz 12/1/03
a3 456 def 6/12/04 Yes
a4 123 abc 1/26/05
a5 789 xyz 12/1/03
a6 456 abc 2/1/05
a7 456 def 1/20/05
This array formula, entered with the key combo of
CTRL,SHIFT,ENTER, will produce the above result:
=IF(AND(SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2))1,C2=MIN
(IF(A$2:A$8=A2,IF(B$2:B$8=B2,C$2:C$8)))),"Yes","")
Biff
-----Original Message-----
What I want to do is create a formula that will look
through my vendor quotes
spreadsheet compare part number, vendor and date on a row
by basis to the
complete spreadsheet and mark Yes in the cell reference
of the old quote that
matches only if there is a newer quote:
cell part vendor quote date updated quote
a1 123 abc 10/1/04 Yes
a2 132 xyz 12/1/03
a3 456 def 6/12/04
a4 123 abc 1/26/05
a5 789 xyz 12/1/03
a6 456 abc 2/1/05
a7 456 def 1/20/05 Yes
Hope this helps
Thanks
Joe
"Biff" wrote:
Hi!
Need more detail.
Post a sample of the data.
Biff
-----Original Message-----
First, sorry for the repost but I wasn't sure if last
weeks post was able to
be followed today.
I'm trying to create a formula that will look for
multiple quotes from the
same vendor that have the same parts listed on them.
If
there are matching
quotes I want to say "Yes" in cell AZ of the older
quote.
The criteria I need
to match is vendor and part number then find the most
current date of the
quote and put "Yes" in the older quote.
TIA
Joe
"Bob Phillips" wrote:
That doesn't check duplicates, it just checks which
rows satisfy all 3
conditions. I can get a Yes with a single row, no
duplicates there.
Explain the problem, what to check for duplication,
etc.,someone will help
you.
--
HTH
RP
(remove nothere from the email address if mailing
direct)
"Joe Gieder"
wrote in message
news:919A02CD-90D9-4CB5-8418-
...
Sorry. That does help. What happens is only blanks
show up and I know
there
are duplicates.
"Bob Phillips" wrote:
So are you just telling us, or saying it doesn't
work? If the latter,
what
happens?
--
HTH
RP
(remove nothere from the email address if
mailing
direct)
"Joe Gieder"
wrote in message
news:26A56A56-B576-443F-A626-
...
First, Thank you for your help and all the
help
you have given.
I have this formula:
=IF(SUMPRODUCT(--($I$3:$I$4999=$I1766),--
($AG$3:$AG$4999=$AG1766),--($AM$3:$
AM$4999$AM1766)),"Yes","")
Briefly I = part numbers, AG = suppliers and
AM =
quote date
What I'm trying to do is have the formula
check
the worksheet for
duplicate
suppliers (AG) that have quoted the same part
number (I) on different
dates
(AM) and leave the results cell AZ blank for
the
match that's the most
current or does not match and put in "Yes" for
the older matches.
TIA
Joe
.
.