Assign serial number without sorting...

Just wondering if this is possible, and I think it is but the question is. How?

I have a list of products in Column A. There are several products like Tea, Sugar, Wheat etc...This list keeps on increasing as and when new ones are added or if the stock of the same one is received with obviously other things like quantity, price, vendor etc in the columns from B onwards...

I used the Countif function to determine how many times stock for each was received. In addition to it, I need the following:

1) In Column BA I would like to get the serial number / sequence of each product against it. For example: If I have Wheat 5 times in column A in non- consecutive rows (Row 14, 56, 78,128, 330) I need 1, 2, 3, 4, 5 as the serial numbers in Column BA. In other words for Wheat in Row 14 the corresponding serial number should be 1, in Row 56 it should be 2 etc and for other products it should again start from 1 and end depending upon their occurence.

Secondly, if a new product is added it should be automatically added to the listing where I have applied Countif using unique product ranges and give its count.

The challenge is that I cannot sort the data every now and then due to some restrictions, otherwise a simple solution could have been sorting and just dragging the serial numbers.

Hope my request is quite explanatory!

Looking for your expertise on it!

lifeisaspreadsheet,

Assumptions:
Row 1 is a header row
Actual data starts in row 2

In cell BA2 and copied down, something like this?

=COUNTIF(A$2:A2,A2)

HTML Code: 

Make it a table; the formula should automatically be copied down when you add a row.


Regards, TMS

And, if you did sort the records:

HTML Code: 

Regards, TMS

Super! This was very simple. sometimes the brain just doesn't function, as in when you think too much, and that's what I did. LOL!...Thanks:-)

One more on this one and this has been an ongoing problem...that haven't been able to get resolution to.

I need to be able to auto populate the data entered in one sheet to a few others. So if in this case, If I entered Mango in Column A, I need to get the same data entered in 2 ways:

Sheet 2: Same row as sheet 1
Sheet 3: last empty row of the data in it.

Please advise.

You're welcome. Thanks for the rep.


This is a completely different question, albeit using the same data/workbook. The solution, I suspect, would require VBA ... and warrants a new thread in its own right.

That said, I would normally advise against generating sheets with subsets of a "main" sheet. Maintenance becomes a PitA. if you must do it, the easiest way would be to use an Advanced Filter to select the products and copy them to a new location ... that is, clear the target sheet each time and copy all the data afresh.

Regards, TMS

Got it, will follow it in my future posts.

Thanks for all this assistance!

As always, Excel Help Forum Rocks:-)

Marking it solved now.