I'm not sure if this has been asked before, but I couldn't find any similar questions.
I have a very large spreadsheet (>30,000 rows, >250 columns, all cells with data). The data in the spreadsheet is made up of delivery information. The key information is the client name and the location of delivery. There are over 400 clients and each client could have up to 400 deliveries to any of 20 locations.
What I need to do is list all the delivery docket number of orders sent to a client at a specific location in a separate spreadsheet. (I know that I could do this by filtering, but it is very time consuming).
The ideal scenario is that I select the company and location from a drop down, and the a formula(e) in column A would pull in all the information for docket number based on the client and location.
An example:
I tell the system that I want the dockets for the following location:
Client: AAA
Location: XXX
The system returns the following dockets:
Docket number:
000001
000005
000008
....
012030
I assume that I would need to use an INDEX search, but I have no idea how to put it in Excel.
Note: we cannot use macros, as they have been disabled on our systems.
Any help would be gratefully appreciated.
If filtering is time consuming, you may try to create Pivot Table and use Slicer. I randomly generated 10,000 rows of data to test. Creating a Pivot Table and inserting two Slicers took about 30 seconds. You can then select Client Name and Location from the slicers. Please see the image below: