What have the clients NOT purchased?

pocquet

Board Regular
Joined
Aug 21, 2013
Messages
118
Hi Guys,

Been a while.

Got one that I cant quite get right in my head how to do it.

I have a list of clients and the products they have purchased.

Client table:

Client NameProductsUsersFee
MrPROD15100
MrPROD21100
MrPROD33100
ExcelPROD15100

<tbody>
</tbody>


Prod Table:

ProductDescription
Prod1Cats
Prod2Dogs
Prod3Bees

<tbody>
</tbody>


So Product table size is actually 100 products and Clients will be over 100k rows but this will be split so no issues.

What I want to do is find out what products the clients Have Not purchased.

Any ideas?

Thanks

Poc
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
HI K99,

Thanks for this, Not quite what I am after however.

The result I want is Client name, Not purchased.

I have a funny feeling I cant do this unless I am in a database, no issue there but was hoping to do it in excel to make it easy to deploy
 
Upvote 0
So specifically you want a list of clients with a list of products they have not purchased?
That's a bit more complicated, a Pivot table or Power Query may solve this but I dont know much about tat part of Excel.
 
Upvote 0
So specifically you want a list of clients with a list of products they have not purchased?
That's a bit more complicated, a Pivot table or Power Query may solve this but I dont know much about tat part of Excel.

Yeah thats exactly it :)

Not sure its possible tbh without plugins
 
Upvote 0
Could you post expected result (as mentioned in post#5 also)?

Sorry,

Client NameProduct Not Purchased
ExcelProd2
Excelprod3

<tbody>
</tbody>

SO we know that the first client has purchased all 3 products, given in the example there are only 3 products they would be completely excluded, but the second has NOT purchased the 2nd and 3rd product so I would want/expect to see two entry.
 
Upvote 0
With your Client names starting "A2" and Related products starting "B2".
Try this for product in Row 1 starting "F1" with "Not Bought" items in rows below each "Product Name" .
Code:
[COLOR="Navy"]Sub[/COLOR] MG25Apr55
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
Ray = Range("A2", Range("B" & Rows.Count).End(xlUp))

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1): .Item(Ray(n, 2)) = Empty: [COLOR="Navy"]Next[/COLOR]

  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
       [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
          [COLOR="Navy"]If[/COLOR] Not K = Ray(n, 2) [COLOR="Navy"]Then[/COLOR]
              nStr = nStr & IIf(nStr = "", Ray(n, 1), ", " & Ray(n, 1))
             [COLOR="Navy"]End[/COLOR] If
          [COLOR="Navy"]Next[/COLOR] n
              .Item(K) = nStr: nStr = ""
        [COLOR="Navy"]Next[/COLOR] K

Ac = 5
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Ac = Ac + 1
    Cells(1, Ac) = K
    Sp = Split(.Item(K), ", ")
    Cells(2, Ac).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
With your Client names starting "A2" and Related products starting "B2".
Try this for product in Row 1 starting "F1" with "Not Bought" items in rows below each "Product Name" .
Code:
[COLOR=Navy]Sub[/COLOR] MG25Apr55
[COLOR=Navy]Dim[/COLOR] Ray [COLOR=Navy]As[/COLOR] Variant, K [COLOR=Navy]As[/COLOR] Variant, nStr [COLOR=Navy]As[/COLOR] [COLOR=Navy]String,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Sp [COLOR=Navy]As[/COLOR] Variant
Ray = Range("A2", Range("B" & Rows.Count).End(xlUp))

[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] n = 1 To UBound(Ray, 1): .Item(Ray(n, 2)) = Empty: [COLOR=Navy]Next[/COLOR]

  [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
       [COLOR=Navy]For[/COLOR] n = 1 To UBound(Ray, 1)
          [COLOR=Navy]If[/COLOR] Not K = Ray(n, 2) [COLOR=Navy]Then[/COLOR]
              nStr = nStr & IIf(nStr = "", Ray(n, 1), ", " & Ray(n, 1))
             [COLOR=Navy]End[/COLOR] If
          [COLOR=Navy]Next[/COLOR] n
              .Item(K) = nStr: nStr = ""
        [COLOR=Navy]Next[/COLOR] K

Ac = 5
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
    Ac = Ac + 1
    Cells(1, Ac) = K
    Sp = Split(.Item(K), ", ")
    Cells(2, Ac).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick


Thanks allot Mick that works great.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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