Find missing asset numbers in Excel

Question

Hi, I am looking for a simple formula in Excel, to match up a column of numbers, along with another column showing a description, with another workbook of numbers.

Basically, I need to filter out any missing numbers from the first workbook that cannot be found in the second workbook. The results need to show both the number and it's description. See screenshot attached.

Thanks.

Answer 1

You can return only the asset IDs that do not exist in the other workbook by using a formula that checks for absence with COUNTIF. Then you can pull the description alongside it.

In your first workbook, assuming:

Asset IDs are in A2:A1000

Descriptions are in B2:B1000

The second workbook is named OtherFile.xlsx and its list of numbers is in Sheet1!A:A

Use this formula to flag missing items:

=IF(COUNTIF([OtherFile.xlsx]Sheet1!A:A, A2)=0, A2, "")

Next to it, return the matching description:

=IF(COUNTIF([OtherFile.xlsx]Sheet1!A:A, A2)=0, B2, "")

Filtering these two columns will show only the IDs and descriptions not found in the second workbook.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

You have one worksheet with the IDs of interest. A second worksheet has IDs and descriptions for many but not all possible IDs. You want to build a worksheet containing the IDs and descriptions for those IDs in the first and the second.

=FILTER(Sheet2!A1:B7,NOT(ISERROR(MATCH(Sheet2!A1:A7,Sheet1!A1:A8,0))))

Change the ranges (A7 and B7) to match the set of descriptions and (A8) to match the number of IDs of interest.

The MATCH function determines if the ID with a description is one of interest.

The ISERROR function traps the situation where it is not.

The NOT function lets the filter include only the ones that are of interest,