What have the clients NOT purchased?

[pocquet]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Products[/TD]
[TD]Users[/TD]
[TD]Fee[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr[/TD]
[TD]PROD1[/TD]
[TD]5[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr[/TD]
[TD]PROD2[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr[/TD]
[TD]PROD3[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Excel[/TD]
[TD]PROD1[/TD]
[TD]5[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Prod Table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]Prod1[/TD]
[TD]Cats[/TD]
[/TR]
[TR]
[TD]Prod2[/TD]
[TD]Dogs[/TD]
[/TR]
[TR]
[TD]Prod3[/TD]
[TD]Bees[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Special-K99]

in ProdTable!C2
=IF(COUNTIF(ClientTable!B$2:B$5,A2)=0,"NOT PURCHASED","")

copy down the column

 

[pocquet]

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]

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.

 

[Repush]

Not quite what I am after however

it was what you asked, though . .

find out what products the clients Have Not purchased.

please give an example of the expected result

 

[pocquet]

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]

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

 

[pocquet]

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

Sorry,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Product Not Purchased[/TD]
[/TR]
[TR]
[TD]Excel[/TD]
[TD]Prod2[/TD]
[/TR]
[TR]
[TD]Excel[/TD]
[TD]prod3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

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" .

[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]

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" .

[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.