Repost of Using IF and SUMPRODUCT

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Joe,
I don't know if you can accomplish your objective with regular Excel
functions.
If you sort your worksheet by Part Number, by Supplier and by Date (in
descending order), the macro below should give you your desired result.

Option Explicit
Dim NumRows As Integer
Dim Iloop As Double
Dim CompDate As Date

Sub CheckDates()
Application.ScreenUpdating = False
NumRows = Range("I65536").End(xlUp).Row

For Iloop = 1 To NumRows
If Cells(Iloop, 9) & Cells(Iloop, 33) = Cells(Iloop + 1, 9) &
Cells(Iloop + 1, 33) Then
Cells(Iloop + 1, 52) = "Yes"
End If
Next Iloop

Application.ScreenUpdating = True
End Sub
 
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
 
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
Click to expand...

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
Click to expand...

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
 
Biff,
Will this formula work if there are more than two entries with the same
supplier and part number?
 
Back
Top