0

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?

3
  • 1
    Welcome to Web Applications Please show what you tried, including a sample data for both before and after, show what you have tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Mar 19 at 0:58
  • In addition, it always helps if you show instead of tell. That is, the most efficient and effective way to get help from the volunteer contributors here is to share a link to your spreadsheet (or to a copy of it containing enough realistic data to illustrate the goal fully).
    – Erik Tyler
    Mar 19 at 2:36
  • Hi and welcome to WebApps. I am guessing that you are new to Sheets because there are many ways to do this: by formula, by script, etc. But the easiest of all is by menu and perhaps you are not entirely familiar with this - hence the request by @Rubén to tell us what you've tried. Are you familiar with the documentation for Sort & filter your data.
    – Tedinoz
    Mar 19 at 3:22

1 Answer 1

0

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"

filterandsort


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"

pivottable


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")

query


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.

screenshot


Option#5: Formula-Sort & Filter

Use this formula:

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

formulafilter


Data

data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.