Trouble transforming data

Hi

wondering if anyone has any ideas on this one? I have some matrix data - shown here:

Picture1.png

What I then need to do is to report on that data to find what buildings do a particular supplier provide services to using the first sheet as a source (and showing whether they do or not by showing y/n - the second table is formatted as below:

Picture2.png


So I need to somehow search across all the columns from the first table, and if any of the columns show that the supplier provides a service in a particular building, that I can return a "Y" and if not then "N"


I feel that there should be a way of doing this, but it's beyond me!!

Any help gratefully received.


Thanks

Maybe something along the lines

Formula:

Please Login or Register  to view this content.

Thanks for the reply, but that doesn't seem to work - can I just ask what the ISNUMBER is doing?

Hi,

My source data is from the attached file: Services by Supplier. In this sheet, I have the suppliers that deliver types of services to a number of buildings.

What I would like to do, is to use this data to populate the second file: Suppliers by Buildings.

In this (suppliers by building), I would like the data to show "Y" if the supplier is delivering any service to a particular building. Whereas if the supplier does not deliver a service to leave the cell in Suppliers by building blank.

I have populated the suppliers by building sheet to show how I would like it to work.

Any (further) help, most appreciated.

Thanks

That's great thanks a lot - works a treat.

Great - thanks a lot

Perfect thanks

Thanks for all your help so far - just one last question - would it also be possible to do the following:

My source data is from the attached file: Services by Supp. In this sheet, I have the suppliers that deliver types of services to a number of buildings.

What I would like to do, is to use this data to populate the second file: Supps by Servs.

In Supps by Servs, I would like to return a Y if the supplier provides the service in any of the buildings I have listed in the first file (Services by Supp).

Any further help much appreciated.