Database - Extracting particular information from 3 columns on one row in a long list.

[Jayess]

Instruct contractors help.xlsx

As part of my job i instruct a number of different contracting companies to do pieces of work on the same locations (lets refer to these 'sites' for the purpose of this query).

I have a long list of sites of which i extract regularly from an online database via an SQL query file. this provides a full extract of the entire database which i then maipulate into reports and use to instrcut works on sites.

My problem is that i have 3 types of works that can be done on site and around 12 different contractors of which any combination could be be carried out. (please see spreadsheet example). I have to instruct the contractors from this spreadsheet, but i cannot show the other contractors works, these could be on the same sites or different sites.

The info i have to provde the contractor is a list of sites instructed by Client Ref (site identifiying ID) and the type of works they are doing on that site i.e Civils, Rigging and/or M&E.

I have to run this SQL query to run the report many times a day to refresh the data.

If you have any ideas, please let me know. my spreadsheet is attached. thanks again.

[cheal2]

Here you go this should help with your problem. I added a sheet called Contractor Display, what you could actually show the contractors.

You can just enter the contractor name in the yellow field and then use your filters to get rid of the blanks. Also you can hide columns B-D and make it a bit neater. I left them visible so its clearer how the tool works.

Hope this helps.

[Jayess]

Attachment 228760This is brilliant thanks Only problem is when i pull in the complete data, it's throwing the formulas some how?! I think it's down to blank cells but i'm not 100% sure...

I have attached a list of the complete data. Can you have a look at this and see if you can still make this work. I will be dragging columns like this into the query page on a daily basis.

Thanks ever so much, appreciate all your support

See attached.Instruct contractors 17th.xlsx

[cheal2]

You may be breaking the equation by cutting and pasting your data. When you insert the new data make sure to paste values in to place to maintain your relational integrity.

[Jayess]

You may be breaking the equation by cutting and pasting your data. When you insert the new data make sure to paste values in to place to maintain your relational integrity.

Tried just pasting values but this throws it out. Only works when i put values only data in query tab without blanks in?

[cheal2]

Here's the revised version.

I'd recommend playing around with this some more to determine what is the best method to get your data and calculations to come together. If its just a matter of removing blank data, that may not be such a bad thing to do before you put into your spreadsheet as it may reduce file size and increase speed. Unfortunately without standing over your shoulder I'm not sure how much more help I can be in regards to this.