Extracting all items if they equal a location

[bearcub]

I have a spreadsheet contains the different offices for our company.

I would like to extract, using a formula, all the members that work in that office.

If the Office was named Alcosta, for example, and their were 10 members I would like to show all the members belong to that office.

Then, in another office called Samuel, they have 15 members. I would like to extract all the members that belong to that office.

Is this possible in Excel? I know that the lookup type formulas will only display the first true value.

Thank you for your help in advance,

Michael

 

[Joe4]

How about using Filters?
With Advanced Filters, you can even filter to a new location.
Check out the "Extract Data to Another Location" section here: http://www.contextures.com/xladvfilter01.html

 

[bearcub]

No, sorry Joe, that won't work. I have to create a formula so it updates automatically when another file (sheet) is updated.


I need a formula that will display all the members for a certain office. The original list contains a column that displays the office (it would be located in another file - and this one file will feed 5 other ones).

Does advance filter work in different workbooks or only the same workbook?

Michael

 

[Joe4]

I have to create a formula so it updates automatically when another file (sheet) is updated.

Note that this line is ambiguous. "File" means "workbook". But "sheet" means "worksheet", which exists in the SAME file.
From your next line, tt sounds like you trying to another workbook.
Are you writing a new file each time, or updating an existing workbook?

I really do not know how to do that across workbooks with just formulas.
If it were me, I would probably opt to use VBA to automate the use of Filters to update the data.

 

[Joe4]

Note: I have noticed that you have been posting in both Access and Excel lately.
A task like this is MUCH easier to do in Access, as you would just have 5 active queries instead of 5 sheets, one for each location (or simply one parameter query, where you are prompted for the location when you open it, and it will return the data for that locations).
Queries are dynamic, so any change to the underlying table would automatically be reflected in the queries without you have to do anything at all.

 

[bearcub]

Yes, I do have that set up with a query. However, the static tables are linked to an Excel file. So, I have to update these individually.

Is there an Excel formula that will extract all items if they met a certain criteria?

Michael

 

[Joe4]

However, the static tables are linked to an Excel file. So, I have to update these individually.

Are you talking about the source file, or the 5 files?
If you are talking about the source file, it does not make a difference if it is a "native" Access table, or a linked table from Excel. You can perform Access Queries off of that table regardless of the table-type.

Is there an Excel formula that will extract all items if they met a certain criteria?

Possibly. But as I mentioned in a previous reply, I do not know how to do that myself.
I would opt to use Access, or some combination of Filters and/or VBA, myself.

 

[bearcub]

No, these are the source files. No worries, I think I found a formula from Mike Girvin that I think will do the trick for me.

You do some good points and I am doubting myself now that if what I did set up for the client was the smartest option.

But, I spent several months on this projects looking at each office and found a lot of difficulties in including everything in one database. T

he client had initially mentioned to me before I started the project that the previous database was breaking because of all the exceptions and one off situations that each office presented (plus the input data is coming from all different sources that don't talk to each other).

So, I felt it was smarter to create a database for each office.

Fortunately, the databases are only being used once a year. If it were used more often then I would not have set up the databases the way I have.

Thank you again for the help and advice,

Michael

 

[Joe4]

I think I found a formula from Mike Girvin that I think will do the trick for me.

If you could post that formula here, that would be great. Then if anyone else has the same issue and come across this, they will see the solution.

And I wouldn't mind seeing it either!
(maybe I can learn something)

 

[bearcub]

Sure, Here is the link to his video that explains how to use it (plus he has a workbook download as well). It is a non-array formula (you don't have to use CSE) that uses the aggregate function to house the Index function (the 15 is the small function and the 6 is to ignore errors) function:

https://www.youtube.com/watch?v=KRtD3eWJElw&t=336s

Here is the formula that he uses in the accompanying workbook:

IF(ROWS(E$5:E5)>$G$2,"",INDEX(A$2:A$102,AGGREGATE(15,6,(ROW($A$2:$A$102)-ROW($A$2)+1)/($B$2:$B$102=$F$2),ROWS(E$5:E5))))

I've used this formula several times in my spreadsheets since I learned about it and it does work. The only issue is that you too many of these in a file that slow down calculation time.

The first part of the If statement prevents the array from running which minimizes calculation time.