Need help with sequential number formula - Excel 2007
I need to find a way to enter a sequential number for each Combination of two cells in two columns.
Example:
[td]Class Category Number
Fruits Apples 1
Fruits Apples 2
Fruits Oranges 1
Fruits Apples 3
Vegetables Tomatoes 1
Vegetables Onion 1
Vegetables Tomato 2
Fruits Apples 4
If you have formula or Macro that would be great.
Thanks!
Last edited by kaurka; 10-24-2012 at 07:33 PM.
see the file
notice tomatoes don't equal Tomato (so your example gives the false result).
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Another way (without helper column)
Assuming your data in columns A & B
=SUMPRODUCT(($A$1:$A1 = $A1) * ($B$1:$B1 = $B1))
copy down.
Regards,
Vandan
Thanks for quick reply!
Tomato and Tomatoes was a typo but thanks for catching that.
Is there another way of doing it without showing the column?
The cells in column A and B will be entered via User Form, and i need to assign sequential numbers automatically.
You can hide the helpcolumn
Or
you can use a pivot table
see the example.
Last edited by oeldere; 10-24-2012 at 05:05 PM. Reason: or you can use a pivot table
I have this formula but it only counts the first column.
=IF(A21="","",COUNTIF(A$4:A21,A21))
Whats great about it however, it can be copied down the column, and once column A and B are entered, column C would display the sequential number. Can someone edit it so it would take column B into account?
Thx!
Figured it out! Thanks everyone for your help!
Please add the used formula, so other member can also learn of it.
Formula Used:
=IF(A4="","",SUMPRODUCT(($A$4:$A4=$A4)*($B$4:$B4=$B4)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks