Repost of Using IF and SUMPRODUCT

[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

 

[Biff]

Hi!

Need more detail.

Post a sample of the data.

Biff

 

[Guest]

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

 

[Guest]

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]

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

 

[Biff]

I forgot to add:

Copy the formula down as needed!

Biff

 

[Guest]

Biff,
Will this formula work if there are more than two entries with the same
supplier and part number?

 

[Biff]

Yes!

Biff

-----Original Message-----
Biff,
Will this formula work if there are more than two entries with the same
supplier and part number?


.

 

[Guest]

Thank you for the help, it works great. You are correct about 6/12/04 being
older.

Joe