MS Query, Use both 'Like' and 'Not Like' in multiple instances

v8chevy68

New Member
Joined
Jan 10, 2017
Messages
12
Morning All
First, apologies if this is already answered in the Forum, but i could not find anything similar

I have a very large data set in excel with 1000's of rows and i cannot uses simple filters as this is not appropriate to generate subsequent reports from the data, therefore i have to use MS query to initially exclude various data, this has always served me well up to now, however I am now stuck!
Example below is nothing like the real data i am working with but to simplify my question: assume for easy to understand data that I would like to create a query based upon used cars, that shows all cars that are:
Colour: LIKE Black, or Yellow or silver, but NOT LIKE Green or blue or Red or graphite

I cannot simply state LIKE Black, or Yellow or silver, and leave it at that, i have to further specify the NOT LIKES too because of the real complex dataset I am working with (unfortunately because the data is sensitive to my company i cannot use real examples of the data or naming types of the fields)

Criteria Field: Colour
Value: Like 'Black' or 'Yellow' or 'Silver'
or: Not like 'Green' or 'Blue' or 'Red' or 'Graphite'

This doesn't work, i suspect i need to state all syntax in the first row but because of my real dataset value names which are each quite long i run out of character space to fit all into the first Value row


I assume that this (below) should work, but as said i run out of characters space for this row with my real data
Value: LIKE 'Black' or 'Yellow' or 'Silver' & NOT LIKE 'Green' or 'Blue' or 'Red' or 'Graphite'

Has anybody had experience of this, any help really appreciated
Many thanks to all
Regards
V6chevy68
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top