Extract all rows from a range that meet 1 exact criteria

Hi everyone,

I have tried to do this about 10-15 different formulas and have been leaning towards using a macro since I can't figure the formula out.

Each column, starting with B and ending at AH (32 total), is a different product. Each row in column A is a different supplier (300+). Then I've gone through and marked what suppliers (Column A) supply what products (Columns B to AH) with an "X" in the cells.

End Goal:

Similar to just filtering the columns individually, I would like a list to be pulled for suppliers offering each product, if they are a manufacturer or distributor, what region they are located in, their first name, and email address to be pulled. All of these are separate columns so filtering 3 columns 3 or 4 different times only gives me contacts for 1 product but I will also need to do this for 4 other products so it takes a fair amount of time just to get contact lists together and sorted. If I can automate this part I will save so much time overall since we do anywhere from 5 to 30 each a week.

I have been debating doing a Macro to pull the information I need for each product but I have 32 products (Columns B-AH) and will likely be adding more as time passes so I don't really want to deal with that volume of Macros lol. I have also never worked with Macros before so if someone has a suggestion on doing a Macro differently, I would appreciate it.

Example: I need details for suppliers who do Pipe CS. By using my existing filter in Column C, I know that the following suppliers will do this material;

Supplier 15
Supplier 21
Supplier 47
Supplier 50
Supplier 54
Supplier 55
Supplier 84
Supplier 139
Supplier 216
Supplier 268
Supplier 272
Supplier 313

In addition to the above, I need to have their details from columns AI to AL pulled and listed as well. I then need to have the provided list split based on if they are Distributors or Manufacturers and then split again based on if their region is USA or one of the other selections.

i.e. Pipe CS Distributers - USA
Supplier 21 Distributor USA Tom 1 Sample Email #21
Supplier 47 Distributor USA Tom 2 Sample Email #47
Supplier 54 Distributor USA Sue 1 Sample Email #54
Supplier 139 Distributor USA Sue 2 Sample Email #139
Supplier 216 Distributor USA Mary 1 Sample Email #216

So on and so forth.

For the first "leg" of the solution please find a drop down in AP1 to select product.

Then in AP3 and filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

Then for the other fields in AQ3 filled down and across column AT.

Formula:

Please Login or Register  to view this content.

I am at a loss on how to do the other splits. I am not sure there would be a simple way to do this with the layout I've proposed. (There are drop downs in AQ1:AR1.) It may help if you could give us an idea of the layout(s) you envision.

Very well done, Flame! I tried this formula without the dropdown menu prior to posting here but it always came back #NAME. You adding the dropdown menu actually makes it better than what I envisioned with the tables split further.

I've seriously put 40+ hours into what I am calling my "Bid Database" (Excel Workbook haha), having barely started trying to learn complicated formulas such as this one, and this portion alone has absorbed more than 10 hours trying to figure it out so thank you a ton for that solution!!

I do have one question regarding those formulas. The drop down menu in AQ and AR do not seem to do anything when I change them to another drop down option (i.e. USA to UK); are they supposed to filter those columns?

**EDIT Note: I believe I figured this one out. You added them but said you were not sure how to go about splitting the data further. If that is accurate, I will likely just take these two drop down menu's and not worry about sorting it any further.

Also, what does the 15,6 do here? "AGGREGATE(15,6,(ROW". I'll look more into aggregate formula too since i'm not familiar with it at all.

I've gotten the table to show and filter the data properly and I had an idea to also show another bit of information similar to the last. I'm trying to manipulate that formula to do this but I cannot get it to recognize more than the 1st item from the drop down menu.

My goal now is to also pull the "Item #" (Column A) and "Item Description" (Column B) from the "Select Options" Sheet onto the "SupplierProduct Sourcing" Sheet in the designated area based on the drop down menu in E4. The example workbook part 2 that is attached shows the Item #'s already in place but if I could instead have those match the item # per each description,that would be ideal.

How can I go about manipulating the formula he provided to do that? I think I could figure out how to get item #'s to match with description but I cannot figure out how to pull the description first.

I'll start here:

Also, what does the 15,6 do here? "AGGREGATE(15,6,(ROW". I'll look more into aggregate formula too since i'm not familiar with it at all.

This part of the formula does all the heavy lifting. It returns a calculated array of row numbers as well as a host of #DIV/0! errors.

(ROW($A$2:$A$335)-MIN(ROW($A$2:$A$335))+1)/(INDEX($B$2:$AH$335,,MATCH($AP$1,$B$1:$AH$1,0))="X")

AGGREGATE is like the "Swiss Army Knife" of functions. It will fulfill 19 different functions each referenced by number. The number 15 is the equivalent of the SMALL function. The first 13 functions pertain to ranges only. Functions 15 to 19 pertain to ranges or calculated arrays. The 6 is one of the options to ignore those errors.

Initially I was of the understanding you would need 3 different output summaries depending upon whether you wanted Suppliers only another for Suppliers + Manufacturer/Distributor and yet another for Suppliers + Manufacturer/Distributor + Region + other etc. etc.

I was struggling to see if that could all be done in one output summary. So far everything I was coming up with would result in enormously large, complex (and ill advised) formulas. Rather than proceeding I was waiting for feedback on the first part.

The alternative was to do a single output of Suppliers + Manufacturer/Distributor + Region + other etc. etc. but it sounded like that was what you were already doing with filtering but trying to find something more robust.

I am still not clear on what you envision as the final product(s) --- layouts, inputs, options. Do you want separate output summaries?

If I understand correctly you want 'EXAMPLE SHEET' to return all suppliers from 'Source' sheet that have all of the unique Products from 'Select Options' and have "X" under that product in 'Source' sheet.

First as an aside: I took the liberty of changing your index arrays in 'EXAMPLE SHEET' column K:N to relative column references. ex: INDEX(Sourcing!AI$2:AI$334,. That way you only have to enter the formula once in K2. When you fill across all the column references change automatically. It saves a lot of typing and tedious editing.

In select 'Select Options' C6 this formula returns the number of unique Products in column C.

Formula:

Please Login or Register  to view this content.

There is a second way to do this that also allows for auditing those results against the Product headers. In 'Sourcing' row 339

Formula:

Please Login or Register  to view this content.

Then in A339

Formula:

Please Login or Register  to view this content.

The only other change I made to the two formulas you had in your upload 'EXAMPLE SHEET' is to change the denominator in AGGREGATE to (MMULT(ISNUMBER(MATCH(Sourcing!$C$1:$AH$1,'Select Options'!$C$8:$C$157,0))*(Sourcing!$C$2:$AH$335="X"),ROW($1:$32)^0)='Select Options'!$C$6)

The whole formula in J2

Formula:

Please Login or Register  to view this content.

I hope this does what you want.

Please see if the attached takes care of this part. It's the only part I worked on. I added a sheet to do it --- 'Example 1 - Drop Down (2)'

I altered one of the formulas you gave me to get the suppliers for each product type listed out below on the sourcing table so I am essentially trying to pull from each column the ones that offer product types from Column C on Select Options Sheet.

The formulas must be array entered this time.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.In J2 of 'Example 1 - Drop Down (2)' and filled down.

Formula:

Please Login or Register  to view this content.

In K2 filled down and across.

Formula:

Please Login or Register  to view this content.