yogi_Set Up A Table To Automatically Increment Each Successive Record For An Entity By 1

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #717   Aug 28, 2012     www.energyefficientbuild.com.

user Bruno Augusto said: (http://productforums.google.com/forum/?zx=4uvknqql63q3#!category-topic/docs/spreadsheets/kTr_DCRDRoM)
Auto-Increment 

I should describe a little better the subject, but it would be awful... >.<

In my office I have a big workbook with several different sheets and, for better structure, there are one workbook for each month.

There are a few auto-increments in these workbooks, which means the first value must be the last value, from last month incremented by one.

Once I need to duplicate the "Template Workbook" every month before start using it, I decided to add a group of cells from which I can do some sort of configurations:

+-----------------------+

|      Last Records     |

|   (from Last Month)   |

+-----------------------+

| Book ID | Last Record |

+---------+-------------+

|   A15   |    1.234    |

+---------+-------------+

|   B28   |    5.678    |

+---------+-------------+

|   C05   |    9.012    |

+-----------------------+

For future reference, let's name this range as Q1:R3.

In the previous version of this "Template Workbook", it was an easy task because each Book ID had its own sheet.

But I was wasting my working time by opening between all the sheets all the day. And then, I decided to add all records in a single sheet and when I need some specific data, I just have to query them, just like a database:

+---------+-------------+

| Book ID | Next Record |

+---------+-------------+

|   A15   |    1.235    |

+---------+-------------+

|   B28   |    5.679    |

+---------+-------------+

|   A15   |    1.236    |

+---------+-------------+

|   B28   |    5.680    |

+---------+-------------+

|   C05   |    9.013    |

+---------+-------------+

For future reference, let's name this range as A1:B5

And now the real problem about the auto-incrementing, to be added in Column B:

I need to check if the value entered in Column A is the first occurrence of that value in entire Column.

If so, the value to be added in Column B will be what I have defined in R1 PLUS 1, because it refers to the first record, of that book in that month.

If it's NOT the first occurrence, I need to find the penultimate occurence of that Book ID in Column A, grab the value of its Column B and then increment it.

Applying this idea to table above, it would be:

+---------+-------------+----------+---------+

| Book ID | Next Record |  First?  |   Use   |

+---------+-------------+----------+---------+

|   A15   |    1.235    |    YES   |  R1 + 1 |

+---------+-------------+--------------------+

|   B28   |    5.679    |    YES   |  R2 + 1 |

+---------+-------------+--------------------+

|   A15   |    1.236    |    NO    |  B1 + 1 |

+---------+-------------+--------------------+

|   B28   |    5.680    |    NO    |  B2 + 1 |

+---------+-------------+--------------------+

|   C05   |    9.013    |    YES   |  R3 + 1 |

+---------+-------------+--------------------+

I sincerely hope you can understand, because it was very difficult to me to explain.

------------------------------------------------------------------------------------------

following is a solution to the problem