You will find this Google Sheet highlighting rules, highlight rows based on issue and return of items, useful in many circumstances. I think this type of conditional formatting will be most useful for store people as well as in library management.

With conditional formatting, in a project site, a store person can effectively track the issue of tools to site labours / employees. In most cases, the store people issue the tools on daily basis. They issue it in the morning and taken back the item when the labour / employee leaves in the evening. This’s because, lots of the employees may be working temporarily basis and it’s not safe to leave costly tools in their hands for many days.

On the other hand the employees may also not interested in keeping the items in their possession. If any item lost means, the store management may deduct the charges from their salary. I know it very well as I worked in many project sites for years. What I am trying to say is a store person can record issue and return of items in a Google Sheet and highlight them to visually identify the non returned items without going through countless records.

You May Also Like: How to Create a Heat Map in Google Sheets 

As mentioned above, you can use the above type of conditional formatting, i.e., highlight rows based on issue and return of items, effectively in library management too to implement a well manged book issue and return system.

I am giving you the formula for this purpose and a general idea on how to implement this conditional formatting rule in Google Sheets. See one example below related to store issue and return tracking. Then I’ll explain you how to use this formula in library book issue and return tracking.

How to Highlight Rows Based on Issue and Return of Items in Google Sheets

In this example, Matt and Natalie have taken Item C3001 and B4001 respectively and didn’t returned so far. All other employees returned their issued items and so their names are greyed out.

Here I’ve considered three things to find whether the item is returned or not. What are they?

Date, Employee Name and Issued Item Code. That means Column A, Column B and Column C. I am not considering the last column named Status which only says Issued or Returned. If the issued item code (column C) repeats twice, that itself means the item has taken and returned. Then why Date of Issue (Column A) used. Because the next day same person or some other person may again issue the same item. So I used date to differentiate it. Then the use of Name of Employee is justified in the following ways. If an employee returned the item in same day and some other person issued the same, you can differentiate this too by using the name.

Note: The below formulas may not work in your sheet unless you remove the single / double quotes and retype it.

The core formula used (no need to use it in sheet)

=ARRAYFORMULA(IF(B2:B=””,,COUNTIF(($A$2:$A&$B$2:$B&$C$2:$C),($A2:$A&$B2:$B&$C2:$C))))

If you apply this formula in Column F2, you can see that it returns 2 for returned items and 1 for non returned items. This I’m using in the conditional formatting rule.

Custom Formula 1 (Grey out the colour ) to highlight rows based on issue and return of items.

This formula highlights the rows wherever the issue and return completed.

=ARRAYFORMULA(IF(B2:B=””,,COUNTIF(($A$2:$A&$B$2:$B&$C$2:$C),($A2:$A&$B2:$B&$C2:$C))))>1

Hope you already know how to conditional format in Google Sheets. You can access this from the Format menu, Conditional Formatting.

Custom Formula 2 (Yellow colour ) to highlight rows based on issue and return of items.

This formula highlights non returned items.

=ARRAYFORMULA(IF(B2:B=””,,COUNTIF(($A$2:$A&$B$2:$B&$C$2:$C),($A2:$A&$B2:$B&$C2:$C))))=1

In the example, I’ve chosen Yellow colour for this custom formula.

Must Read: How to Highlight Cells Based on Expiry Date in Google Sheets

Google Sheets Conditional Formatting in Library Book Issue and Return Tracking

The same formula you can tweak to use in a library management book tracking system. Here let me explain you how to implement this.

As I’ve told you above, you can use the above same formula with little modification for this purpose. Our core purpose is same. We want to highlight rows based on Issue and return of items. Here item means books.

In the earlier example, we have used Column A, B and C. But here things are different. You should study the data first before applying the formula. Here we can consider the column D which is labelled as Book ID. It contain unique numbers for each book so we can use this ID. But we require one more unique column in order to work our formula. You know why?

If you only consider the Book ID, it may drag you into trouble. Our formula should return the value either one (non returned) or two (returned) for the above conditional formatting to work. The Book ID may repeat so many times. So when we count this column using Countif, it may return number other than one and two.

A book can be issued so many times in a particular period. It depends how fast the person returns the book. So how to make this unique. If you study the data you can find that there is one column that we can depend. That’s Column G which is labelled as Date of Issue.

In the above example, Book ID A1001 (The Woman in White) repeats three times. The first two times indicate the first person who took the book has already returned it and the third one indicates it issued to a new member. So you can differentiate this from the date of issue column.

So here the formula would be;

=ARRAYFORMULA(IF(A2:A=””,,COUNTIF($D$2:$D&$G$2:$G,$D2:$D&$G$2:$G)))

This formula returns the number 1 for non returned book and 2 for returned book. This’s is the basic of our conditional formatting rule.

In conditional formatting, to grey out the returned books, you can use the below formula.

=ARRAYFORMULA(IF(B2:B=””,,COUNTIF(($D$2:$D&$G$2:$G),($D2:$D&$G2:$G))))>1

To highlight the non returned books, use the below formula. I have used the colour Yellow in this case.

=ARRAYFORMULA(IF(B2:B=””,,COUNTIF(($D$2:$D&$G$2:$G),($D2:$D&$G2:$G))))=1

How to Modify this Google Sheets formula for your own requirement. To highlight rows in Google Sheets based on issue and return of items, you can use the above formula with little tweaking. What you want to know is how to add or remove columns in the formula. Here the core formula used is COUNTIF.

Ultimate Tutorial: Learn Google Sheets complete count related functions.

If you want to use Column D and G as per the above example to differentiate the book issue, use it like this. See the above formula where I’ve highlighted the same with green colour. It’s like.

$D$2:$D & $G$2:$G

I’ve just used the ampersand to join columns as it supports array. If you want to add one more column you can simply use another ampersand and add that column. See the below piece of formula taken from our first formula where three columns used (Column A, B and C)

$A$2:$A & $B$2:$B & $C$2:$C

See how three columns added here. In Countif, use it twice and both separated by comma. That’s all about this conditional formatting based on issue and return. Enjoy!