Share via

EXTRACT ALL ROWS WITH MISSING DATA AND PLACE IN A NEW TABLE.

Anonymous
2024-05-09T23:04:03+00:00

We have a listing of thousands of contracts in excel with about 20 columns. About 10 of those are required fields. We would like to extract all rows where any of the required fields are blank and place this complete result in a new table. The idea being, this table will be given to someone to research and correct the fields. For convenience I would like to keep it in Excel and use Power Query/ VBA, but I am struggling with the code.

If PBI is more efficient, then open to suggestions there as well.

Sample data:

In this case, since ABC123 is missing 1 required field (at minimum) it would be added to the results table. XYZ987 would not, because the missing info is not in a required column. Thank you very much!

Contract Column a Column b Required2 Required2 Required3
ABC123 48 85 83 3
XYZ987 76 87 2 5
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-05-10T02:40:59+00:00

    You are welcome. If the numeric cells in your input dataset do not have 0's, then try this formula

    =if(FILTER(A3:F4,BYROW(D3:F4,LAMBDA(a,OR(a=""))))=0,"",FILTER(A3:F4,BYROW(D3:F4,LAMBDA(a,OR(a="")))))

    4 people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-05-09T23:13:10+00:00

    Hi,

    In cell H3, enter this formula

    =FILTER(A3:F4,BYROW(D3:F4,LAMBDA(a,OR(a=""))))

    Hope this helps.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2024-05-10T15:27:19+00:00

    I had an error in my formula. This worked perfectly!

    Thank you!

    0 comments
  3. Anonymous
    2024-05-10T14:55:54+00:00

    Thanks for the follow up! My results table is made up of #VALUE in every cell of the results table.

    Any idea what might be causing this?

    0 comments
  4. Anonymous
    2024-05-10T00:41:31+00:00

    Thank you! Is there a way to have the result table pull the blank row as is? Without inputting a zero?

    Better yet, could the table list the contract and only the fields that are left blank?

    I appreciate your help!

    0 comments