CODEWITHSUNDEEP
excelexcel-formula
Publius
Publius

Reputation: 1

Excel: Narrow entries from a sheet to those that appear on another sheet

I'm a relatively novice Excel user trying to streamline the following task:

I've got two sheets of product information. Sheet1 has around 3000 entries and Sheet2 has around 1300 entries. Every SKU in the product number column on Sheet2 appears on Sheet1, but some are formatted differently: some cells in Sheet1's SKU column occasionally contain multiple comma-separated entries (Example: PDB2S2FW, PDB2S2V, PDB2S2WH), whereas all Sheet2 SKUs are listed in their own cells.

My goal is to identify the items on Sheet 1 that appear on Sheet2 (with a filter or a helper column) so that I can narrow down Sheet1 to include only the items on Sheet2.

I've been experimenting with a few formulas to attempt this task, but haven't been able to solve for the multiple entry/single cell issue.

Here's my current formula:

=IF(ISNA(MATCH(BJ9,Sheet2!B:B,0)),"Not found","Found")

[Column BJ on Sheet1 and Column B on Sheet2 hold product numbers.]

Any ideas? Thanks!

Upvotes: 0

Views: 88

Answers (1)

StoneGiant
StoneGiant

Reputation: 1497

This answer uses an array formula. If you're new to Excel, you may not know how to do an array formula, so I'll add a quick tutorial and a link at the end of this answer:

This formula will give you a zero if there is not a match and a positive number if there is a match. Enter the formula in a column next to BJ on Sheet1, then drag fill to the bottom. You can then filter out all positive numbers and see those values which are not found on Sheet2.

Assumption: Values start in Row 1. If they don't, change $BJ1 to match the row where values start.

{=LARGE(IFERROR(FIND($BJ1,Sheet2!$B:$B),0),1)}

Note: It might be more efficient if you change Sheet2!$B:$B to reference only the cells that actually have data, instead of the entire column. For example: Sheet2!$B$1:$B:$3000.

Array Formulas

To enter the array formula,

  • Select and copy the above formula excluding the curly braces. The curly braces are there just to show that it is an array formula.
  • Paste the formula into a cell on your spreadsheet.
  • Press Ctrl + Shift + Enter

This page has more info about array formulas.

Caveat

The IFERROR function is only available in later versions of Excel. If you're using an older version, you may need to work with ISERR instead.

Upvotes: 0

Related Questions