Example for Auto Increment In Library Management

Example for Auto Increment In Library Management

Question:

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 |

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

Answer:

Following is the screenshot of the spreadsheet created for solving the above question:

In above example (screenshot), we have created both the ranges i.e, Q1:R3 and A1:B5 in the same (single) sheet. 

Following is the formula, which will give the required result by inserting it in the Cell B1 and then dragging it down up to the required row:

=IF(COUNTIF(INDIRECT(CONCATENATE("A1:A";ROW()-1));INDIRECT(CONCATENATE("A";ROW())))=0;VLOOKUP(INDIRECT(CONCATENATE("A";ROW()));Q:R;2)+0.001;MAX(FILTER(INDIRECT(CONCATENATE("B1:B";ROW()-1));INDIRECT(CONCATENATE("A1:A";ROW()-1))=INDIRECT(CONCATENATE("A";ROW()))))+0.001)


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive