First Product Sold in a customer?

Manish Mahajan

New Member
Joined
Sep 15, 2014
Messages
3
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.

Customer Id
ProductMonth of Earliest Purchase
D1BreadDec-13
D1WineOct-13
D2ChalkFeb-13
D2MilkMay-13
D2BreadJan-13
D2ClipsJan-13
D3PencilMar-14
D4BreadJan-13
D4PencilJan-13
D4ChalkJan-13
D4MilkJan-13

<tbody>
</tbody>

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

Customer Id
First Product SoldMonth of Earliest Purchase
D1WineOct-13
D2Bread + ClipsJan-13
D3PencilMar-14
D4Bread + Pencil + Chalk + MilkJan-13

<tbody>
</tbody>

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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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!
 
Upvote 0
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")
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top