From To Number * Rev
A B 1 -
A C 1 -
A D 1 -
B A 1 -
B A 2 -
B A 1 A

* This column to auto assign

Here is the spreadsheet that I am working with. What this spreadsheet is used for is to check out a document number. The document number is in the format from-to-#-rev. The user select who the document is from, who it is going to and the gets the next sequential number for that from-to combinations. What I would like to do is auto assign the next sequential number. The snag comes into place when you take into consideration revs. Therefore you can not just count the from-to combination and add one for the next number. Some how I have to "match" the combination in the columns and find the max value. Any suggestions?

Hi
Please explain the purpose of Rev column and how it going to behave. Also note in your chart, letter A is put in Rev column but all rows above that are blank. What does it mean?



Genius is 99% perspiration and 1% inspiration

CC,

If I understand correctly you are looking to auto assign the number column based on the number of preceeding instances in the list of From - To - Rev.

If this is the case then you can achieve your result in a couple of ways.

Firstly, you could add a new column alongside the existing table (assume demo table is in cells A1:D7) in column E. The formulae in this column should concatenate the From - To - Rev combination on each row (i.e. formula in cell E2 is =CONCATENATE(A2,",",B2,",",D2)). Yopu then need to count the preceeding number of instances of each unique combination plus one in each row for the number (i.e. formula in cell C2 is =COUNTIF(E$1:E1,E2)+1).

Alternatively, you could use an array formula. This will dispense with the need to add a new column but will leave the number formula more complex. In this instance the formula you need to type into cell C2 is {=SUM((A$1:A1=A2)*(B$1:B1=B2)*(D$1:D1=D2))+1}. The curly brakets indicate that the formula is an array formula, you can't type these brakets in but rather you must enter the forula as typed without brakets and then exit the cell by pressing Crtl + Shift + Enter.

Hope this helps,
Maccas