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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
in ProdTable!C2
=IF(COUNTIF(ClientTable!B$2:B$5,A2)=0,"NOT PURCHASED","")

copy down the column
 

pocquet

Board Regular
Joined
Aug 21, 2013
Messages
118
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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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.
 

pocquet

Board Regular
Joined
Aug 21, 2013
Messages
118
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
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

Could you post expected result (as mentioned in post#5 also)?
 

pocquet

Board Regular
Joined
Aug 21, 2013
Messages
118
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.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

pocquet

Board Regular
Joined
Aug 21, 2013
Messages
118
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,311
Messages
5,635,487
Members
416,860
Latest member
coen078

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
Top