Formula to find all occurrences of text within 2-d array and list row label for each...

[o_snap]

Hello all,

I've found many helpful answers here while browsing as a guest, but can't find the solution to this problem. So, I've joined and here's my first post....

I have a 2-d array of data containing a list of objects and the drawings they are found on. Each row lists a unique object, and each column is a drawing type. In each cell is the name of the drawing containing the object.

Drawings can contain multiple objects, and drawing names are unique to a particular type.

The goal is to use formulas to produce a unique list of the drawings found in B2:F5, with every object found on each drawing in the cells to the right.

I have found formulas to generate the drawing list, but need help identifying and listing each occurrence of the objects.

Attached is a workbook with the above. Any help would be greatly appreciated!

Thanks,
Todd

[tigeravatar]

Todd,

Welcome to the forum!
Using your example file, use this array formula in in cell B8:

Please Login or Register  to view this content.

Then copy over to E8, then copy B8:E8 down to B17:E17

[o_snap]

Thanks ~tigeravatar, I appreciate the quick response!

I must be doing something wrong, cause when I paste the formula into B8 (+ctrl-shift-enter) I get "0" - and when I copy down and across I get zeroes also. Any ideas?

Thanks,
Todd

[tigeravatar]

See attached.

[o_snap]

Awesome! That's exactly what I needed - and the formula is much smaller than I expected, so I could expand it to much larger data sets. Thanks for your help ~tigeravatar! I'll mark this thread "solved."

Regards,
Todd

[tigeravatar]

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save