Excel formula to populate value based on ascending date where values may have same dates

[Exceluser1983]

Hi All,

I have a table of data that contains ~600k rows (1 per customer) with a list of dates that they have purchased their first product.

Example of the data is per blue text in table below.

Im trying to figure out an Excel Formula that will populate the values in red text. I had a formula but it didnt work when a customer purchased multiple products on the same day ( it just showed one product name). I'm not fussed on which name is shown as 1st/2nd/3rd etc when multiple purchases have been done on the same day.

i.e. it doesn't matter with the below if "3rd Product=Shorts" or if it showed "3rd Product=Shoes" just so long that the 4th product didn't have the same value as the 3rd Product.

In summary, if products are purchased on same day it doesnt matter which product name gets populated sequentially just so long as the product name is unique and isn't repeated in a subsequent # Product field.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Customer #[/TD]
[TD]Tshirt[/TD]
[TD]Shorts[/TD]
[TD]Jeans[/TD]
[TD]Shoes[/TD]
[TD]Other Accessories[/TD]
[TD]1st Product[/TD]
[TD]2nd Product[/TD]
[TD]3rd Product[/TD]
[TD]4th Product[/TD]
[TD]5th Product[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]
[/TD]
[TD]17/08/2019[/TD]
[TD]30/03/2011[/TD]
[TD]17/08/2019[/TD]
[TD]01/11/2018[/TD]
[TD]Jeans[/TD]
[TD]Other Accessories[/TD]
[TD]Shorts[/TD]
[TD]Shoes[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope that makes sense. Any formula's that would give me the results in red text above?

Appreciate the help

 

[MUKESHY12390]

Perhaps this..

Here is the file I have uploaded https://gofile.io/?c=1Ozu65.Below are the formula used in solution.


B1=IF(COUNTIF($B$2:B2,B2)>1,B2+VLOOKUP(COUNTIF($B$2:E2,E2),{2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9},2,0),B2)

B2 = =RANK(B4,$B$4:$F$4,1)

G2 = =COLUMNS($G$2:G2)

G4 = =HLOOKUP(G2,$B$1:$F$4,3,0)