Isolating unique sku's from a customer/sku list

[Ed Denmark]

I have customers (125) on a fixed price sheet. Each
price sheet is unique as is each customer.

I have a list of all of sales (OVER 30000) by line item
by customer by SKU.
what is the formula that I can use to determine which of
those SKU items were sold to the

different customers that were fixed price sales? I need
to isolate the sales that were made

to them that were not fixed prices.

FIXED PRICE LIST
LINE customer Part # FIXED PRICE
1 Bob's Shop SKU123 $1.00
2 Bob's Shop SKU124 $2.00
3 Bob's Shop SKU125 $7.00
4 Carl's Work SKU034 $2.00
5 Carl's Work SKU123 $1.10
6 Carl's Work SKU456 $3.20
7 The Barn SKU145 $3.20
8 The Barn SKU123 $1.00
9 The Barn SKU987 $2.50

Actual Sales
SALES Part # PRICE SOLD
1 Bob's Shop SKU123 $1.00
2 Bob's Shop SKU123 $1.00
3 Bob's Shop SKU123 $1.00
4 Bob's Shop SKU233 $1.40
5 Bob's Shop SKU233 $1.45
6 Bob's Shop SKU124 $2.00
7 Bob's Shop SKU125 $7.00
8 Carl's Work SKU034 $2.00
9 Carl's Work SKU034 $2.00
10 Carl's Work SKU456 $3.20
11 Carl's Work SKU111 $2.90
12 The Barn SKU145 $3.20
13 The Barn SKU123 $1.00
14 The Barn SKU987 $2.50


Thanks in advance,

Ed

 

[Max]

Assuming this table is in Sheet1, cols A to D, data from row3 down

FIXED PRICE LIST
LINE customer Part # FIXED PRICE
1 Bob's Shop SKU123 $1.00
2 Bob's Shop SKU124 $2.00

etc

and this is in Sheet2, cols A to D, data from row3 down

Actual Sales
SALES Part # PRICE SOLD
1 Bob's Shop SKU123 $1.00
2 Bob's Shop SKU123 $1.00

etc

Put in E3:
=IF(ISNA(MATCH(1,(Sheet1!$B$3:$B$11=B3)*(Sheet1!$C$3:$C$11=C3),0)),"Non
FP","FP")

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy E3 down

Col E will return the flags:
Non FP = Non Fixed Price
FP = Fixed Price

You could then use auto-filter on col E to get Non FP cases
Adapt to suit

 

[Guest]

Thank you for the answer, you saved me many tedious hours
of hand calculating this data!

 

[Max]

You're welcome !
Thanks for the feedback