First Product Sold in a customer?

[Manish Mahajan]

Hi
This is my first post here, and my days of frustration finally led me here. Would request some help please. Stuck on this problem since several days now.

Data Set: I have the following data set. Illustrative shown below, the actual data runs into several thousands of rows. As you can see, the customer id repeats, product also repeats and month of earliest purchase can also be repeating.

[TABLE="width: 322"]
<tbody>[TR]
[TD="align: center"]Customer Id
[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Month of Earliest Purchase[/TD]
[/TR]
[TR]
[TD="align: center"]D1[/TD]
[TD="align: center"]Bread[/TD]
[TD="align: center"]Dec-13[/TD]
[/TR]
[TR]
[TD="align: center"]D1[/TD]
[TD="align: center"]Wine[/TD]
[TD="align: center"]Oct-13[/TD]
[/TR]
[TR]
[TD="align: center"]D2[/TD]
[TD="align: center"]Chalk[/TD]
[TD="align: center"]Feb-13[/TD]
[/TR]
[TR]
[TD="align: center"]D2[/TD]
[TD="align: center"]Milk[/TD]
[TD="align: center"]May-13[/TD]
[/TR]
[TR]
[TD="align: center"]D2[/TD]
[TD="align: center"]Bread[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: center"]D2[/TD]
[TD="align: center"]Clips[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: center"]D3[/TD]
[TD="align: center"]Pencil[/TD]
[TD="align: center"]Mar-14[/TD]
[/TR]
[TR]
[TD="align: center"]D4[/TD]
[TD="align: center"]Bread[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: center"]D4[/TD]
[TD="align: center"]Pencil[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: center"]D4[/TD]
[TD="align: center"]Chalk[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: center"]D4[/TD]
[TD="align: center"]Milk[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
</tbody>[/TABLE]

From the above dataset, I want to extract first product sold in a customer and arrange it as below

[TABLE="width: 443"]
<tbody>[TR]
[TD="align: center"]Customer Id
[/TD]
[TD="align: center"]First Product Sold[/TD]
[TD="align: center"]Month of Earliest Purchase[/TD]
[/TR]
[TR]
[TD="align: center"]D1[/TD]
[TD="align: center"]Wine[/TD]
[TD="align: center"]Oct-13[/TD]
[/TR]
[TR]
[TD="align: center"]D2[/TD]
[TD="align: center"]Bread + Clips[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: center"]D3[/TD]
[TD="align: center"]Pencil[/TD]
[TD="align: center"]Mar-14[/TD]
[/TR]
[TR]
[TD="align: center"]D4[/TD]
[TD="align: center"]Bread + Pencil + Chalk + Milk[/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to do this? Any help would be greatly appreciated. Thanks

 

[Oeldere]

for D4 this is AND or this is an OR statement.

Do all criteria's have to met?

 

[Manish Mahajan]

for D4 this is AND or this is an OR statement.

Do all criteria's have to met?

Hi Oeldere
Yes it is a AND view, which is part of the problem. Using Vlookup, INdex/match only returns the first match. In case of D4, we have as many as four products all bundled in the month. So for D4, the answer should indicate all the four products!

 

[vds1]

I have a partial solution..

Lets assume your data is in Column A , B and C

First sort columns ABC by Customer ID , then add level, Month of earliest Purchase (oldest to newest)

In D2, Insert below formula and drag down,

=IF(A2=A1,IF(C2=C1,"First Product","Not First Product"),"First Product")

 

[Oeldere]

Mabye with an filter on costumer (D4).

 

[Manish Mahajan]

I have a partial solution..

Lets assume your data is in Column A , B and C

First sort columns ABC by Customer ID , then add level, Month of earliest Purchase (oldest to newest)

In D2, Insert below formula and drag down,

=IF(A2=A1,IF(C2=C1,"First Product","Not First Product"),"First Product")

Thanks for replying vds1
I have managed to get the first unique product out, by some similar method as suggested by you. In fact, I have gone a step further and instead of getting "Not First Product" I get "2 Products" or "3 Products" so on. But the challenge remains in identifying the product name whenever I get "2 Products" or "3 Products"