Newbie help - Write unique values under certain conditions

[JenBerlin]

Hi guys and girls,

I am new to this forum and in search of some help for a VBA newbie.
I am just starting out with this and I am a bit confused.

What I am trying to do is create a list of unique values under certain conditions. I will try to explain it as best as I can.

Imagine we have four columns:
Column A contains a product, so for example Apples, Bananas, Oranges...
Column B contains names, so for example Peter, Carl, Linda...
Column C contains stores, so for example Supermarket, Cornershop, ...
Column D contains a number, so for example 1, 2, 3...

Now let's say I want to see who bought apples in the supermarket, I would like to query kind of like this:
For all rows where column a = "Apples" and column c = "Supermarket", write me Column b value and expect something like
Peter
Linda
if only these two bought apples in the supermarket.

But I need to make sure its only unique people, so if Peter bought apples at the supermarket twice, I do not want to see
Peter
Linda
Peter

(I hope this makes sense).

Now the next step would be to see what was the maximum amount of apples that peter has ever bought at the supermarket, so Im thinking of something like:
For all rows where column a = "Apples" and column b = "Peter" and column c = "Supermarket", find me the maximum value that is ever displayed in Column D.

Like I said, I'm totally new to VBA so I am not really sure how to get started with this, but what I do know is that doing this with if statements would take me forever, so I want to give vba a try. If any of you have helpful suggestions on where to look / what to look at, or even a small code example I would be very grateful.

Many thanks,
Jennifer

 

[Useful]

How about PivotTable?

 

[JenBerlin]

Thanks Useful, that is a good idea if I just want to see each item individually, then that will certainly work.
However, I am planning to take this a bit further than described above, so it would be very helpful to just create the lists and have them written somewhere, so that I can then later query on the lists, if that makes sense?

Edit because I just tried the pivot table:
It shows me the correct information, but as far as I can see, there does not seem to be an option to count how many different people bought apples, if some bought them more than once?
So it is not quite what I need.

 

[Useful]

options/sort&filter/insert Slicer?

 

[MickG]

With something like :=
"F1 = "apples" , "G1" = Store1" and "H1" = "Name1" then the following code will return:-
[TABLE="width: 166"]
<tbody>[TR]
[TD]"apples_store1" := Max Number for Name1 = 2

apples_store1 := Names :=
Name1 (2)
Name22 (1)

[COLOR=Navy]Sub[/COLOR] MG30Sep18
[COLOR=Navy]Dim[/COLOR] Q
[COLOR=Navy]Dim[/COLOR] Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] ct [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] nRng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Stg [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] Dic [COLOR=Navy]As[/COLOR] Object
[COLOR=Navy]Dim[/COLOR] p
[COLOR=Navy]Dim[/COLOR] Str [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] NamStrg [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
   [COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR=Navy]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
        Dic.CompareMode = 1
   [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        Stg = Dn & "_" & Dn.Offset(, 2)
            [COLOR=Navy]If[/COLOR] Not Dic.exists(Stg) [COLOR=Navy]Then[/COLOR]
                [COLOR=Navy]Set[/COLOR] Dic(Stg) = CreateObject("Scripting.Dictionary")
            [COLOR=Navy]End[/COLOR] If
        
        [COLOR=Navy]If[/COLOR] Not Dic(Stg).exists(Dn.Offset(, 1).Value) [COLOR=Navy]Then[/COLOR]
                Dic(Stg).Add (Dn.Offset(, 1).Value), Dn.Offset(, 3)
        [COLOR=Navy]Else[/COLOR]
            Q = Dic(Stg).Item(Dn.Offset(, 1).Value)
                Q = Q + Dn.Offset(, 3)
            Dic(Stg).Item(Dn.Offset(, 1).Value) = Q
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Dn
ct = Range("H1")
     NamStrg = Range("F1") & "_" & Range("G1")
        Str = Str & NamStrg & " := Names := " & Chr(10)


            For Each p In Dic(NamStrg) 
               Str = Str & p & " (" & Dic(NamStrg).Item(p) & ") " & Chr(10)
            [COLOR=Navy]Next[/COLOR] p
    
MsgBox """" & NamStrg & """" & " := Max Number for " & ct & " = " & Dic(NamStrg).Item(ct) & Chr(10) & Chr(10) & Str


[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick[/TD]
[/TR]
</tbody>[/TABLE]