Combining multiple cell content based on multiple criteria (IF, LOOKUP, INDEX??)

[MacWatts]

Hi,
I am unable to solve an issue and I was hoping to get some assistance from someone. I have a spreadsheet of data which needs to be transferred over to another spreadsheet but in a different format. Attached you will see an example of some of it that a copied from a pivot table of the original date. I need to create a single description for each printer that contains the description of each of the associated printer accessory(ies) as well as the number of accessories . In my example you can see the one I was able to create but that formula is not going to work for all the instances as there isn't always an accessory or the number of accessories vary.

I have been trying to use an array formula to avoid having to refer to a single cell but I have not been successful combining more than one criteria.

[Tsjallie]

Maybe your problem is in the formula. Does this work? Put NULL between quotes, because that was giving an error.

Please Login or Register  to view this content.

The last one was already between quotes.

[MacWatts]

No unfortunately not. If there isn't an accessory I don't want NULL to come back I just want any result. The last one I put in quote to pull NULL if the Category isn't 'Printer'. My main issue is that the data isn't uniform, my formula will work if there are 2 accessories for each printer but that not always the case, my formula doesn't adjust for that variable.

[Tsjallie]

Thx for the rep! Even while I didn't help you yet
Hope to solve your problem now with this upload.
Let me know if you need any explaination.

[MacWatts]

This is great thank you. Not exactly what I wanted but it is close enough to allow me to to tweak it to get what I need. I'll post the final iteration once I'm done. As for the rep its well deserved, thank you very much.

[MacWatts]

Here is a final version if you are interested, thanks again for your help it's greatly appreciated.

[Tsjallie]

One suggestion yet: there are a lot of repeating parts in the formula. You could create a name for them. Would increase the readability of the formula and helps you understand what was going on when haven't look at it for a few weeks.

[MacWatts]

Interesting... that sounds like it could be useful and although I have an idea of what you mean I'm not sure how I'd implement it in my formula. Can you show me one example?

[Tsjallie]

Here's an example of the use of names in formulas. I choose one cell randomly (F2). Hope it's self explainatory.

[MacWatts]

Good stuff that makes sense, I probably will incorporate that in. Thanks again.