< Go Back

Excel - COLUMN Function Problem - Expert Solution

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
Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.