Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Indexing, matching and vlookups?
I have posed a similar question before and did not get a response. I have
changed the format and the question to hopefully recieve an answer. Please at least tell me why I cannot obtain my answer, if you would be so kind. Below I have a list of supplies, products and prices listed by month. They sell similar products at different prices. I buy them monthly at a set price for the month. I need to compare 1.) The difference I paid for the same items from the same vendor. 2.) Note the items I purchased that I did not buy from the same vendor for the previous month. Please give me the formula as if it were in column G. January Sales Febuary Sales column A B C D E F G customer item Jan price customer item Feb Price formula ? Bill apple $1.00 Adam beans $2.55 new Bill bean $2.00 Adam pear $4.30 new Bill orange $3.50 Bill apple $1.20 $.20 Bill peach $4.00 Bill bean $2.00 $.00 Randy apple $1.05 Bill orange $3.30 $.20 Randy beans $2.40 George apple $1.25 new Randy cherry $3.60 Randy apple $1.05 $.00 Randy grape $0.80 Randy bean $2.35 $.05 Randy orange $3.75 Randy grape $0.90 $-.10 Randy nuts $4.70 Randy orange $3.65 $.10 Rick apple $1.00 Randy peach $4.50 new Rick bean $2.60 Rick apple $0.95 $.05 Rick peach $4.50 Rick beans $2.45 $-.15 Rick pear $4.20 Rick peach $4.50 $.00 Rick beans $4.60 Rick pear $4.20 $.00 Rick nuts $5.00 Rick peas $4.60 $.00 Tom apple $1.10 Rick nuts $5.00 $.00 Tom cherry $3.40 Tom cherry $3.30 $.10 Tom grape $1.10 Tom grape $0.90 $.20 Tom pear $4.40 Tom pear $4.40 $.00 Tom peas $4.60 new Thanks in advance, inthestands |
#2
|
|||
|
|||
Ok this is what I'd do - and I'm assuming you can make modifications to
the spreadsheet - ie inserting columns - I'd insert a column between B and C and enter in a formula that would combine column A and column B ( seller and product for Jan) =+A2&" "&B2 Then insert a column between the NEW column F and G and enter a formula that would combine column E and F ( seller and product for Feb) =+E2&" "&F2 Now you can write a VLOOKUP formula to see if you find a match on CUSTOMER/ITEM from this months (Feb) compared to last months =IF(ISERROR(VLOOKUP(G2,$C$2:$D$21,2,FALSE)),"new", H2-(VLOOKUP(G2,$C$2:$D$21,2,FALSE))) This will give you NEW if a match is not found, or calculate the difference between last months and this months. here are the columns and items I have A B C D E F G H I customer item customer/item comb Jan Price customer item customer/item comb Feb Price Formula One issue - you'll need to make sure that you have the exact spelling in each month for both name and item or it won't find the item correctly. Hope this helps send you in the right direction. |
#3
|
|||
|
|||
Inthestands,
Here is an alternative solution without adding columns(please note, some of the labels were different between the months - bean vs. beans was skewing the results so I made the change to beans for everyone-typos will cost you here): I've placed these in columns A7 through G7 with the formula in G formula in g8: =IF(SUMPRODUCT(--($A$8:$A$28=D8),--($B$8:$B$28=E8),--($C$8:$C$28))=0,"new",F8-SUMPRODUCT(--($A$8:$A$28=D8),--($B$8:$B$28=E8),--($C$8:$C$28))) ....and then copy down. Only other comment: the ranges in each portion of the sumproduct formula need to be consistent, so you need to use the highest row number(28 here) for the 2 months you're comparing to make sure to include all the data. It doesn't matter that there is no data in a28. customer item Jan customer item Feb change in vendor price/new Bill apple $1.00 Adam beans $2.55 new Bill beans $2.00 Adam pear $4.30 new Bill orange $3.50 Bill apple $1.20 $0.20 Bill peach $4.00 Bill bean $2.00 new Randy apple $1.05 Bill orange $3.30 ($0.20) Randy beans $2.40 George apple $1.25 new Randy cherry $3.60 Randy apple $1.05 $0.00 Randy grape $0.80 Randy beans $2.35 ($0.05) Randy orange $3.75 Randy grape $0.90 $0.10 Randy nuts $4.70 Randy orange $3.65 ($0.10) Rick apple $1.00 Randy peach $4.50 new Rick bean $2.60 Rick apple $0.95 ($0.05) Rick peach $4.50 Rick beans $2.45 ($2.15) Rick pear $4.20 Rick peach $4.50 $0.00 Rick beans $4.60 Rick pear $4.20 $0.00 Rick nuts $5.00 Rick peas $4.60 new Tom apple $1.10 Rick nuts $5.00 $0.00 Tom cherry $3.40 Tom cherry $3.30 ($0.10) Tom grape $1.10 Tom grape $0.90 ($0.20) Tom pear $4.40 Tom pear $4.40 $0.00 Tom peas $4.60 new hth, Dave Thanks in advance, inthestands |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|