Tracking Missing Entries in Google Sheets

Other Questions Google Sheets
1

Hey everyone! I’m working on a Google Sheet that’s linked to a Form. I’ve got a column with Names and several columns for Values. There’s also a list of all available Names.

I need help creating a formula that does two things:

  1. Checks if any names from the total available list are missing in the Names column
  2. Warns me if a name is present but has no values in its row

I’m not sure how to combine COUNTIF or other functions to make this work. Has anyone tackled something similar before? Any tips or formula suggestions would be super helpful!

Here’s a quick example of what my sheet looks like:

| Names  | Value1 | Value2 | Value3 |
|--------|--------|--------|--------|
| John   | 10     |        | 5      |
| Sarah  |        | 15     |        |
| Mike   |        |        |        |

Available Names: John, Sarah, Mike, Emma, Tom

How can I highlight that Emma and Tom are missing, and that Mike has no values? Thanks in advance for any help!

2

I’ve faced this issue before in my data analysis work. Here’s a solution that might help:

For missing names, try this array formula:

=FILTER(AvailableNames, COUNTIF(Names, AvailableNames)=0)

It’ll return names from AvailableNames not found in the Names column.

For rows without values, use:

=ARRAYFORMULA(IF(COUNTA(B2:D2)=0, “No Values”, “”))

This checks each row and flags those without any entries.

Combine these with conditional formatting for visual alerts. You could also use VLOOKUP or INDEX/MATCH for more complex scenarios. Let me know if you need further clarification on implementing these formulas.

3

hey there charlielion22, i’ve got a trick for ya. try using QUERY function to find missing names:

=QUERY(AvailableNames, “select * where not Col1 matches '” & JOIN(“|”, Names) & “'”)

for empty rows, use ARRAYFORMULA with ISBLANK:

=ARRAYFORMULA(IF(COUNTIF(B2:D2, “”)=COLUMNS(B2:D2), “Empty”, “”))

hope this helps mate!

4

I’ve dealt with a similar issue before, and here’s what worked for me:

For tracking missing names, you can use a combination of FILTER and ISNA functions. Something like:

=FILTER(AvailableNames, ISNA(MATCH(AvailableNames, Names, 0)))

This will return a list of names that are in your AvailableNames list but not in your Names column.

As for empty rows, a COUNTIF function could work:

=COUNTIF(B2:D2, “”)=COLUMNS(B2:D2)

This checks if the count of empty cells in a row equals the total number of columns, indicating all values are missing.

You might want to combine these into a more complex formula or use conditional formatting to highlight the issues visually. It takes some trial and error, but once you get it right, it’s a real time-saver for data management.