| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Repost of Using IF and SUMPRODUCT
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 ... 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 ... 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 |
|
#2
|
|||
|
|||
|
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 . |
|
#3
|
|||
|
|||
|
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 . |
|
#4
|
|||
|
|||
|
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 . . |
|
#5
|
|||
|
|||
|
I forgot to add:
Copy the formula down as needed! Biff -----Original Message----- 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 . . . |
|
#6
|
|||
|
|||
|
Biff,
Will this formula work if there are more than two entries with the same supplier and part number? "Biff" wrote: I forgot to add: Copy the formula down as needed! Biff -----Original Message----- 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 . . . |
|
#7
|
|||
|
|||
|
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 "Joe Gieder" wrote: 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 ... 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 ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
| Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
| Sumif not Sumproduct | Excel Worksheet Functions | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Hybrid Mode
