Returning multiple lines of data for a single query?

  • Thread starter Thread starter jg
  • Start date Start date
J

jg

Worksheet "Orders" (19,000 rows, give or take)
In column A: Customers (of which there are 41 unique entries)
In column B: Locations (from 1 to 8 per customer)
Other columns have additional data from each order.

On worksheet "Query", I'd like to create a function which, for a particular
Customer, returns all possible Locations associated with that customer.

So, for instance, the result might look like this:

A B
1 Customer Alamo
2 Loc1 Ogden
3 Loc2 SLC
4 Loc3 Alb
....

and so on.

What formulae do I need in B2-B4?

Thanks
 
Yeah, sorry. Should have mentioned - already checked out Deb's excellent
site.

However, I don't see anything there that gives me what I want.

For each unique "Customer" in Column A, I'd like to return the 1-8 unique
"Locations" from Column B that are uniquely tied to that customer. Note
that each customer and location occurs many times in their respective
columns.

I guess what I want is a function that gives the result as manually using
AutoFilter. Perhaps some array function using some combination of Index,
Match, If?

Thanks
 
Just put an autofilter on your data and filter on that customer name.
Your other option is to create a pivot table and use the filter on the
pivot table to choose your customer.
 
Back
Top