Sorting/Filtering Data to Show Only Checked Out Items

Question

I have a Google Sheet that has ~5000 rows of items that I need to sort/filter by various means and I can't seem to wrap my brain around it. I've sorted them alphabetically but I feel like what I need is simple and can be accomplished by a script or a bunch of vlookup functions tied with Sort/Filter commands.

I have eight columns worth of data that need to be sorted/filtered.

• Column A - Name of Person
• Column B - Site
• Column C - Item
• Column D - Tag # of Item
• Column E - Serial # of Item
• Column F - Date Item was Checked Out
• Column G - Date Item was Checked In
• Column H - Notes (if any)

So if I have Jon Doe who's checked out 60 different items over 12 years, I need a way to sort/filter which items have already been checked in, and which items are still outstanding and what those items are so we can contact Jon Doe and let them know he still has 15 items outstanding, what those items are, they're SN #'s, and any notes as well as provide the date he checked out those items. Without having to manually go through and delete the items from this massive list that he's already checked back in. As well as doing this for each person on this list.

Can someone help me with this?

Answer 1

There are many ways to address this question:

Option#1: Menu driven - Filter and Sort

• select columns A to H
• select Data > Create a filter
  • click the "Filter" icon in cell G1.
    • Under "Filter by Condition"
      • select "Is empty", OK
  • click the "Filter" icon in cell A1.
    • Click "Sort A -> Z"

---

Option#2: Menu driven - Pivot Table

• select columns A to H
• Select Insert, Pivot table
  • choose where to locate the pivot table and click "Create"
• Under "Rows", Add
  • "Name of Person",
  • Untick "Show totals"
  • "Item",
  • Untick "Show totals"
  • "Item Serial#",
  • Order "Descending"
  • Untick "Show totals"
  • "Date checked Out",
  • Untick "Show totals"
• Under "Filter", Add
  • "Date Checked In"
    • Under "Status", select "Filter by Condition", "Is empty", click "OK"
  • "Name of Person",
    • Under "Status", select "Filter by Condition", "Is Not Empty", click "OK"

---

Option#3: Formula - Query

=query(Data!A1:H13,"select A, B, C, D, E, F, H where A is not Null and G is null order by A, E")

---

Option#4: Formula: Query-Select a Name

• Use UNIQUE() to create a list of the unique values in Column A ("Name of Person")

• Copy/paste the unique values to create a static list.

• select a cell (say, Cell B1) and apply Data Validation, List from range

  • use the range of unique Names created earlier.

• Select a cell (say, cell A2), and enter this formula =query(Data!A1:H13,"select A, B, C, D, E, F, H where A is not Null and G is null and A contains '"&B1&"'")

Select "names" in Cell B1 to show query values that apply to that name only.

---

Option#5: Formula-Sort & Filter

Use this formula:

=sort(filter(Data!A2:H13,Data!G2:G13 =""),1,true,6,true)

---

Data