0

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.

Clauric
  • 1,826
  • 8
  • 29
  • 48
  • You could write a very clunky array formula to do this, but if your spreadsheet is large it is going to be slow. The ideal solution is probably to rather use a database (e.g. MySQL) but that's a whole different skill set... – Dan Jun 22 '15 at 15:25
  • Also, how are your data organized? What goes down the rows, what goes across the columns? – Dan Jun 22 '15 at 15:26
  • Unfortunately, the data is exported in to a TXT document (old system), and Excel would seem to be the best platform as there is a large amounts of calculations done, based on the client information. The Client ID and location are in columns. Each row would contain a large amount of information, but I can access that data using HLOOKUP, if I have the docket numbers. – Clauric Jun 22 '15 at 15:27

1 Answers1

1

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:

Data, Pivot Table and Slicer, and the result

kelvin 004
  • 413
  • 2
  • 7