Forum Discussion
I need HELP with excel sheet sample below
Hello,
I have same numbers with same colours.(Ex: Yellow for 9101)
I want to fill B column with 1 to infinity(1,2,3,4.....) for same numbers or colours(it does'n matter becasue every number has already a colour) in A column automatically.
For example,
A2:1245, 1 should be written automatically to B2,
A6:1245, 2 for B6
A8:1245, 3 for B8 like the Excel sheet sample below. Is there any formula or hit to do this?
8 Replies
I offer a generalized solution:
=Instanceλ(A:.A)Instanceλ is included in the attached workbook
Hi,
You can paste following formula in cell B2 and drag it all the way down to get desired results:
=IF(IFNA(VLOOKUP(A2,$A$1:$A1,1,FALSE),1)=1,1,COUNTIF($A$1:$A1,$A2)+1)
Please let me know if it helps. I have also attached a file for your reference.
Regards,
Nikhil
It works and your example was very helpful for me. Thank you so much!!
Your formula is very good but I just found a mistake now.
If the number is 1 in A1 column, B1 column is filling automatically as 1 but it should be different for each 1.
In the excel sheet sample below, A-B column was done with formula, and D-E column was by hand so you can see the difference for number 1.
Hi Nikhil,
Why do you need VLOOKUP here? Simple COUNTIF as Takmil suggested works fine.
