Visits

[medic49]

I have a set of data that I would like to analyze. I am trying to locate customer visits that have visited a store two or more times in the past 12 months. And also, what product they bought two or more times in the past 12 months.



For instance,


Customer #8228 visited the store 3 times and bought 3 pants during that year.



<table class="MsoNormalTable" style="width: 148pt; margin-left: 5.15pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="247"> <tbody><tr style="height: 0.2in;"> <td style="width: 52pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap"> Date
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Cust ID
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Product
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

2/1/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

8228​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Pants
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

2/2/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

4569​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Blouse
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

9/1/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

8791​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Shoes
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

3/1/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

5478​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> T-shirt
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

6/1/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

8228​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Shoes
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

5/11/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

5478​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> T-shirt
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

8/1/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

8228​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Pants
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

8/15/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

3216​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Pants
</td> </tr> <tr style="height: 0.2in;"> <td style="width: 52pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="87" nowrap="nowrap">

1/11/2010​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap">

8228​

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 0.2in;" valign="bottom" width="80" nowrap="nowrap"> Pants
</td> </tr> </tbody></table>

 

[shadow12345]

Not the cleanest way I don't think but this works.

have a helper column (I used D)
In D2 put =b2&c2 and copy it down

Then in E2 put
=COUNTIF($D$2:$D$10,D2) and copy it down

Filter for everything more than 1

 

[chicagodiceman]

Actually, it looks like customer 8228 visited four times although bought pants on three of those visits and shoes on the other. Is it important to know the actual total number of visits regardless of purchases or only count the visits where purchases exceeded two of the same category?

 

[chicagodiceman]

If this difference is important than the following formula would segregate the customer 8228 into two results, 3 for the pants and 1 for the shoes (Still using the helper column of D):

=COUNTIFS(B$2:B$10,B2,C$2:C$10,C2)

 

[medic49]

Thank you!