Finding all products a company has bought based on 1 product

MykolW

New Member
Joined
Dec 3, 2004
Messages
12
I've always tried to find a solution to this problem and it has always escaped me.

There are two columns of importance. A customer number and a product number.

Row Customer ID Product ID
1 1011 A
2 1011 B
3 1011 C
4 1012 A
5 1013 B
6 1014 A
7 1014 C
8 1015 C

I would like to have another column that flags any Cutomer ID that has purchased a given Product ID, but I also want to flag all the rest of the Product ID's they have purchased.

In the table above, if a customer had purchased Product A, all Product IDs from that customer should be flagged. The result would be that rows 1, 2, 3, 4, 6, and 7 would be flagged.

Any ideas?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
This is what you want, but it doesn't make sense...
Book1
ABCD
1RowCustomer IDProduct IDFlag
211011A1
321011B1
431011C1
541012A1
651013B
761014A1
871014C1
981015C
Sheet1
 
Upvote 0
You only want a customer flaged if they have purchased a certain product. If they have purchased a certain product then you want to flag every cell with that customers name or ID. The flag would only apply to every cell a customer is in just as long as they purchased a perticular item. Am I understanding this right?
 
Upvote 0
You've got it right babycody.

The reason for this is that I can pull a report based on product or customer. But if I want to see what a company is also buying, if they buy "Coke", I have no way to find out other than to filter the report myself. If I'm not totally mistaken, this would be similar to the logic used on websites that offer a, "Customers of this product also purchased X" service.
 
Upvote 0
Maybe I should have said that babycody was right in his explanation of the problem. I'm still looking for an answer :biggrin:

It's quite possible that Aladin answered this question years ago.
 
Upvote 0
MykolW said:
Maybe I should have said that babycody was right in his explanation of the problem. I'm still looking for an answer :biggrin:

It's quite possible that Aladin answered this question years ago.

If you download and install the latest version of the morefunc.xll add-in...
MykolW.xls
ABCDEFG
103
2Customer IDProduct IDIdx (Cust)3
31011A Product IDC
41011B Customer IDBought Also:
51011C11011B
61012A 1014A
71013B 1015 
81014A  
91014C2
101015C3
Sheet1


C1 must house a 0.

C3, copied down:

=IF((B3<>"")*(B3=$G$3),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,"")

F1:

=MATCH(9.99999999999999E+307,F5:F65536)

F2:

=LOOKUP(9.99999999999999E+307,C1:C10)

F5, copied down:

=IF(ROW()-ROW($F$5)+1<=$F$2,LOOKUP(ROW()-ROW($F$5)+1,$C$3:$C$10,$A$3:$A$10),"")

G3 is used to specify a product.

G5:

=INDEX(UNIQUEVALUES(IF(ISNUMBER(MATCH($A$3:$A$10,$F$5:INDEX($F$5:$F$65536,$F$1),0))*($B$3:$B$10<>$G$3),$B$3:$B$10,"")),ROW()-ROW($G$5)+1)

which is confirmed with control+shift+enter then copied down.
 
Upvote 0
I have to get our IT guy to come and install the add-in, but what I did up until now seems to work.

I have one more question. Do you feel your head always tilting to one side because your brain is so big? That's quite the solution.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top