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.

Customer #	Tshirt	Shorts	Jeans	Shoes	Other Accessories	1st Product	2nd Product	3rd Product	4th Product	5th Product
123		17/08/2019	30/03/2011	17/08/2019	01/11/2018	Jeans	Other Accessories	Shorts	Shoes

<tbody>
</tbody>

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)