CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mathew Gellner-Garcia
Mathew Gellner-Garcia

Reputation: 15

Google Sheets: How to grab unique names and numbers from one sheet and adding it to another sheet based on a specific value

I just started working at a new company and I thought that I'd make a spreadsheet for my personal use. My boss came across me working on that spreadsheet and loved it so far. They've been manually entering values into their own spreadsheets since they've started and I thought it was super tedious, so I've been working on my own for the last week trying to improve efficiency on my end. Now my bosses want me to do this for the other branches... so the pressure is real for me. I've completed every other feature they've requested, but I'm struggling with this last one so I'm here now requesting help.

I have two sheets. One called "Inspection Results" and "Still Hasn't Passed Inspection."

"Inspection Results" looks something like this:

Timestamp Date Name MS Number Address Jurisdiction Inspection Result
4/21/2021 4/21/2021 John Smith MS12345 123 Test St Orange Passed
4/20/2021 4/20/2021 John Smith MS12345 123 Test St Orange Failed
4/21/2021 4/21/2021 Rick Grimes MS12356 123 Meme St Riverside Passed
4/19/2021 4/19/2021 Michelle Obama MS54321 123 Demo St Long Beach Failed
4/18/2021 4/18/2021 Michelle Obama MS54321 123 Demo St Long Beach Failed
4/18/2021 4/18/2021 Courage Dog MS98765 123 Coward St Orange Cancelled

"Still Hasn't Passed Inspection" currently looks like this:

Name MS Number

I want to search every row in "Inspection Results" and find the Names(Column C) of the people that do not have a inspection result(Column G) of "Passed" and write a new row in "Still Hasn't Passed Inspection" with the Name(Column C) and MS Number(Column D) of those people that could not be found with a "Passed" inspection result.

I want the table in "Still Hasn't Passed Inspection" to have unique rows. For example, there are some rows in "Inspection Results" that I have the same Name and Inspection Result. In the table that I posted, Michelle Obama failed inspection. I only want Michelle Obama to appear one time in the "Still Hasn't Passed Inspection" sheet. John Smith failed inspection one day, but passed on the following day so he wouldn't be included in the table.

Basically, based on the example table I posted above, I want "Still Hasn't Passed Inspection" to look like this:

Name MS Number
Michelle Obama MS54321
Courage Dog MS98765

I'm trying to automate this process so I, or whoever, doesn't have to keep manually entering and deleting rows every time someone finally passes inspection. If someone submits a "Passed" entry into "Inspection Results" for someone that previously "Failed" or "Cancelled", I want "Still Hasn't Passed Inspection" to be updated.

Is there a way I can accomplish this with certain formulas or am I going to have to create a script on this and, if so, does anyone have a reference they can point me towards? I'm not an experienced coder. I'm not familiar with Javascript and its syntax, but I do know how variables, if statements, and for loops work. I'm not asking anyone to write me a bunch of code on this. This isn't your responsibility. If you want to, of course, it would always be appreciated. Any hints or reference towards resolving this would be amazing.

Thank you.

Upvotes: 1

Views: 484

Answers (1)

Doug Moore
Doug Moore

Reputation: 1193

I am a big fan of the query (QUERY(data, query, [headers])) function. Using it, you could do something like this on "Still Hasn't Passed Inspection"

=query(Inspection Results!A2:G, "Select C, D where G = 'Failed' OR G = 'Cancelled'")

This is saying to look at the data in the Inspection Results sheet, column A2 through G (specifying no row number will go to the end of the sheet).

It is then grabbing the contents of column C and column D in Inspection Results and pulling those into the sheet with the query function, in separate columns, a row for each result. It will only pull in the results where column G (Inspection Results in your example) is the string "Failed".

You would then need a query to get the ones that have passed, and filter the resultset based on that, such as:

=filter(query(A2:G, "Select C, D where G = 'Failed' OR G = 'Cancelled'"), arrayformula(not(countif(query(A2:G, "Select D where G = 'Passed'"), query(A2:G, "Select D where G = 'Failed' OR G = 'Cancelled'")))))

This is essentially saying, "Filter my query of not-passed inspections by looking at passed inspections, and not including any with the same MS Number"

Upvotes: 1

Related Questions