Finding Rows in a table that have multiple values.

ahooper10

New Member
Joined
Jun 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hey guys,

This is probably a simple fix but it's not been easy to find for me.

I have a group of products that we sell within a set. Example - Set Name 016 contains products HR95, SG66 and so on.

I have this data laid out in a table with the Sku and Set name followed by each individual product SKU within that set.

I am trying to figure out how I can search/filter this data to find rows with multiple SKUS.

For example - I want to find all the rows that contain HR65 AND SG66.

Some of my SKUs are alphanumeric and some are just numeric and also each set contains a different number of SKUs so a simple left to right sort and then filter by column didn't work.

Hope I've explained this okay. Excel isn't my strong point!
 

Attachments

  • Capture.JPG
    Capture.JPG
    67.7 KB · Views: 5

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
236
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Something like this might help. Put what you are looking for in L1:

MrExcelPlayground2.xlsm
ABCDEFGHIJKLMNOPQRSTU
1c
2skuitem1item2item3item4item5item6item7item8item9skuitem1item2item3item4item5item6item7item8item9
31abcdef1abcdef000
42gh3cjk000000
53cjk5cnopqrstu
64alm
75cnopqrstu
86vwb
Sheet25
Cell Formulas
RangeFormula
L3:U5L3=FILTER(A3:J8,(B3:B8=L1)+(C3:C8=L1)+(D3:D8=L1)+(E3:E8=L1)+(F3:F8=L1)+(G3:G8=L1)+(H3:H8=L1)+(I3:I8=L1)+(J3:J8=L1),"")
Dynamic array formulas.
 

ahooper10

New Member
Joined
Jun 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Something like this might help. Put what you are looking for in L1:

MrExcelPlayground2.xlsm
ABCDEFGHIJKLMNOPQRSTU
1c
2skuitem1item2item3item4item5item6item7item8item9skuitem1item2item3item4item5item6item7item8item9
31abcdef1abcdef000
42gh3cjk000000
53cjk5cnopqrstu
64alm
75cnopqrstu
86vwb
Sheet25
Cell Formulas
RangeFormula
L3:U5L3=FILTER(A3:J8,(B3:B8=L1)+(C3:C8=L1)+(D3:D8=L1)+(E3:E8=L1)+(F3:F8=L1)+(G3:G8=L1)+(H3:H8=L1)+(I3:I8=L1)+(J3:J8=L1),"")
Dynamic array formulas.

Hey James,

Thanks so much for this.

This formula works perfectly for a single item and is really helpful. However, what if I want to find multiple values.

As an example in your sheet above, what if I want to put C AND J in L1. How would I go about this?

Thanks

Ashley
 

Forum statistics

Threads
1,136,802
Messages
5,677,814
Members
419,722
Latest member
Rizzol

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top