Pulling non-zero data from a large table array

**Akkori** · 04-23-2009, 10:49 AM

I have a data table used to break down the ingredients for multiple items.
Rows are items names, columns are all of the ingredients. I have attached a PDF with a sample of what I am doing.

So what I am shooting for now if a way to extract all the non-zero data from a specific row on sheet2, and display it on sheet1.
On sheet1, I choose one specific Column A item from a combo box, and I would like the spreadsheet to automatically pull the data from sheet2 and display on sheet1 so I can manipulate just that data. For example...

Please Login or Register  to view this content.

Ive done this before, with help of course, but it was a few years ago, and I cannot remember how it went. This data table is a couple hundred rows long, and about 30 column wide.

Where do I start? Thanks!

**martindwilson** · 04-23-2009, 11:32 AM

pdf? whats wrong with xls?

**Akkori** · 04-23-2009, 12:31 PM

Heh, didn't even think about that....

The last workbook I had included a third page where all the data was pulled together. I then referenced *that* table on the first page.

**NBVC** · 04-23-2009, 01:37 PM

See attached.

Formula in D20:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

Formula in H20:

Please Login or Register  to view this content.

confirmed with just ENTER and copied down.

Note: You cannot have merged cells with array formulas.... so first remove merging of cells.

You can cut and paste the table to another sheet so that the formulas will automatically adapt.

If you don't want the Complexity data, delete that row after completing above steps.

**Akkori** · 04-23-2009, 02:40 PM

Yeah, that looks just like I remember. Man! Now I gotta try and read it so I understand it, lol. Thanks a lot! I may be back if I can't adapt it to the larger purpose, but this is a great start.