Help with Vlookup on customer activity

[spurs79]

Hi,

I have a list of sales made over time with many customers buying many products and many buying different "first products". I don't know how to vlookup the first product purchased by that specific customer? Can somebody guide me please

[Pepe Le Mokko]

Please post a sample sheet ( see yellow banner)

[spurs79]

Now with sample sheet showing the columns which need formulas

Thanks in advance

[GeoffW283]

Four questions:

1. What does "Join product" mean and can you describe in words how it is calculated.
   
2. What column defines the "different products". The "Product code" column sounds promising but it does not seem to contain product codes.
   
3. For "Gap (in days) between this and prior purchase": your sample data is sorted into reverse chronological order. Can I assume this is the case with your real data?
   
4. Your sample data also shows just a single customer. Is your real data sorted by customer or will there be other customers interspersed with the provided data?

[spurs79]

1. Join Product = the first product (listed in Product Code) which a customer bought
2. See above
3. The core data is sorted in reverse chronology - the formula will need to identify the gaps between each transaction and order them correctly
4. The core data has hundreds of thousands of transactions from thousands of customers, this is just a mock of how "a" customer may trade

[AliGW]

This will get the date of a customer's first purchase, if that's what you want (not sure) and assuming you have the subscription version or Excel 2019 (please make your forum profile more explicit - latest is vague):

=MINIFS($B$2:$B$23,$A$2:$A$23,A2)

[spurs79]

This isnt what I need sorry, I can identify the join date but its linking the formula to show what the product code for that dates transaction was

[AliGW]

Just use the result of the MINIFS as one of your lookup values:

=LOOKUP(2,1/(($B$2:$B$23=MINIFS($B$2:$B$23,$A$2:$A$23,A2))*($A$2:$A$23=A2)),$O$2:$O$21)

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[spurs79]

Great, thanks

How about columns G&H

[AliGW]

What about columns G and H?

Fill in a few rows of manually calculated results in the yellow section and attach the workbook again.

[AliGW]

Maybe this for G?

=SUMPRODUCT((1/(COUNTIF($O$2:$O$23,$O$2:$O$23))*($A$2:$A$23=A2)))

Maybe this for H?

=B2-VLOOKUP(A2,$A3:$B$23,2,0)