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

Exceluser1983

New Member
Joined
Sep 1, 2019
Messages
1
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 #TshirtShortsJeansShoesOther Accessories1st Product2nd Product3rd Product4th Product5th Product
123
17/08/201930/03/201117/08/201901/11/2018JeansOther AccessoriesShortsShoes

<tbody>
</tbody>

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

Appreciate the help :):)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

MUKESHY12390

Well-known Member
Joined
Sep 18, 2012
Messages
889
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,589
Messages
5,597,053
Members
414,118
Latest member
moversnpackers

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
Top