List all order numbers that have Item number value

campos1578

New Member
Joined
Jan 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. MacOS
Hey Guys,

I've been trying to find the right formula practically all day.

I've randomly generated Items Numbers and Order Numbers from a separate worksheet and Copy/Pasted into this worksheet.
Column A has item numbers which may repeat. Column B also has Order Numbers that may repeat. Column C is using the vlookup reference from a different worksheet

My intent: Find the maximum number of Item numbers that appear in the maxim amount of Order Numbers and return in a list

Examples
1 - Run formula that finds the maximum combination of Item numbers that appear in the maxim amount of Order Numbers and return in a list
1- Type an Item Number into cell E2 and generate a list of Order numbers which have that item number from column A vertically starting at F2.
2 -Type in 1 item number in E2 or type in multiple Item numbers (Possibly D2 and E2) and return list of orders that match the criteria for two

I want to input a number in cell E2

1671233327146.png
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Maybe something like this:
Book2
ABCDEFG
1Item NumOrder NumItem DescriptionItem numberItem numberOrder NumItem Description
2108011271Cool Whip Extra Creamy Whipped Cream Topping, 8 oz Tub10801127235545861Cool Whip Extra Creamy Whipped Cream Topping, 8 oz Tub
3235545861OREO Double StufChocoIate Sandwich Cookies, Family Size, 20 oz1OREO Double StufChocoIate Sandwich Cookies, Family Size, 20 oz
4502285271Great Value Baked Buttery Round *******s, 3.7 oz1Great Value Marshmallows, 0 oz
5104217461RINGS HAWAIIAN Savory Butter Rolls, 2 Pack
6292098931G-S Classic Bologna Sliced Deli-Style Lunch Meat, 0 Slices Per Package, 2 Ounce Pack
7512592121Yellow Onions, Each
8443916051Eresh Strawberries, b
9242699421Great Value Cauliflower, 2 oz (Frozen)
1098107720115remium Grape Tomato, 0 oz Package
112879057111Great Value Chopped Pecans, 4 oz
12235545861Great Value Marshmallows, 0 oz
131005469461hyson All Natural, Fresh, Boneless, Skinless Chicken Breasts, 2.5 • 4.0 b
141081.34831hotino's Party Pizza, Combination, Frozen Snacks, 0.7 oz, ct
153050343391freshness Guaranteed Homestyle Breaded Chicken Sandwich
162874744101hasteful Selections Honey Gold Potatoes, 24 Oz.
1710450.3391Great Value Ultra-pasteurized Real Heavy Whipping Cream, 6 Oz
188081799721br Pepper Zero Sugar Soda, 20 fl oz bottle
Sheet1
Cell Formulas
RangeFormula
F2:G4F2=FILTER($B$2:$C$18,($A$2:$A$18=E2)+($A$2:$A$18=D2),"No Match")
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top