Indexing, matching and vlookups?

[inthestands]

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

 

[mar10]

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.

 

[Dave Breitenbach]

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