Question description:
This user has given permission to use the problem statement for this
blog.
I have a large set of data (40k entries).
The columns include Account ID, Associate, Order Description, Date and Time.
There are several Account IDs. Each is linked to one Associate. However, the Associates have various Account IDs to service.
I need to generate a report/export data/filter out all the entries that have given criteria.
The tricky thing is my criteria list is also rather long. I have about 100 total possible conditions, so I need all entries that have a given Order Description AND a specific order date. There are 100 totals sets of criteria.
What would you advise would help me do this? How do I filter with many conditional filters? It isn't as simple as clicking the filters I want from each column because 1. the column value must also go with another column value and 2. There are so many possibilities.
Ie. I don't want to generate a list of all orders with Order Description - blueberries, oranges, apples, etc. I want to generate a list of all orders with Order Description blueberries on March 1, 2016, oranges on April 3, 2017, oranges January 5,2017, apples March 10,2017.
Solved by F. F. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
03/04/2017 - 10:43
Hello there, thanks for choosing ExcelChat
User
03/04/2017 - 10:43
Hello! I cannot upload my excel data here because it is not authorized. However, I provided the above description of my issue.
User
03/04/2017 - 10:45
Please advice.
Excelchat Expert
03/04/2017 - 10:45
Okay let me see how can I help you
User
03/04/2017 - 10:47
Do you understand my issue?
Excelchat Expert
03/04/2017 - 10:48
I think the best way is to make filters for each column
Excelchat Expert
03/04/2017 - 10:48
You can use as many filters as you want
Excelchat Expert
03/04/2017 - 10:49
You can filter by different columns in different ways
User
03/04/2017 - 10:49
How do you make filters for each column that correspond with another column? As in filters for Order Description like Oranges that also have the date March 1
Excelchat Expert
03/04/2017 - 10:50
Okay, for example you can select the fruits column and do data and mark filter
Excelchat Expert
03/04/2017 - 10:51
than you can select only oranges in filter
User
03/04/2017 - 10:51
Would these be 'Advanced Filter'?
Excelchat Expert
03/04/2017 - 10:52
It gives you the option to select all what you want even if you want multiple values
Excelchat Expert
03/04/2017 - 10:52
It works perfectly with dates too
User
03/04/2017 - 10:52
I understand that. But all I want is a lot. My question is how I do this over 100 criteria, quickly.
User
03/04/2017 - 10:53
as in I have a separate list of Order Description with linked dates and want to filter the large data set with that
Excelchat Expert
03/04/2017 - 10:53
Also using Pivot Tables is very helpful with filters
User
03/04/2017 - 10:55
i would love to use pivot tables, but pivot tables just show count and sum. Not a full list of the info. Much of my data is non-numerical. I wouldn't not know what to put in the value field.
User
03/04/2017 - 10:59
Hello?
User
03/04/2017 - 11:00
My plan was to use Advanced Filteries with a criteria range from a separate table to filter what I want and then use slicers to guide me through the data.
Excelchat Expert
03/04/2017 - 11:00
The best option I would recommend is filterers, I don;t know if you have anything also in mind
Excelchat Expert
03/04/2017 - 11:02
Let’s refer to link in the following thread which discusses about a similar and see if it helps: https://support.office.com/en-gb/article/Filter-data-in-a-PivotTable-cc1ed287-3a97-4e95-b377-ddfafe79fa8f
User
03/04/2017 - 11:03
Sounds good. When using the separate table for criteria range, how would input that I want all entries with Order Description blueberries on March 1, 2016, oranges on April 3, 2017, oranges January 5,2017, apples March 10,2017. However, I do NOT want the entries for several account IDs?
User
03/04/2017 - 11:04
I do not plan on using Pivot Tables becuase I am not interested in sums of data. I want the entries with their specific info. Not sums of averages.
User
03/04/2017 - 11:05
i intend to to use a separate table with criteria range to filter. However I still need to figure out how to get all the order descriptions and dates I want WITHOUT the entries for several account IDs
User
03/04/2017 - 11:06
wouks I just make an account ID column in the Criteria range and use a formula like all but not a list of info?
Excelchat Expert
03/04/2017 - 11:07
I am trying to find a way that could help you
Excelchat Expert
03/04/2017 - 11:11
So you have to select criteria range with advanced filter, select all the cells and write craters like: Fruits= "Apple" AND date= ......
Excelchat Expert
03/04/2017 - 11:11
You can put them in another location
User
03/04/2017 - 11:12
How about something along the lines of all entries except and then a list of unnecessary account IDs
