Extract information from a table that meet certain criteria

[Kyletok]

Hi all,

I was hoping someone could help me accomplish something I am trying to get ready for work.

essentially I am trying to do what ExcelIsFun accomplished in this video: https://www.youtube.com/watch?v=6bGKhbUYOas&t=1s

I tried to do what he did but I kept getting #REF , I wanted to download his worksheet and play with it but I am using Excel 2016, and his files are not compatible.


What I am trying to do is this: I want to have a table that will take information from another table that meets a certain criteria.
so in a cell I will have a data validation list for the 5 main stores, and depending on what store is in that cell, the table will update with all the rows that meet the criteria (where the store is that what is in the cell). its similar to Advance Filter but I am trying to make it so the people who use it will only have access to information about those 5 stores, since the main table has a lot more stores that are not relevant to the people I am preparing this sheet for, and I want this sheet to take information from an excel file I will upload to our drive, so its important what they have will always be able to update incase we add another row for one of the 5 stores in the master list.

there are other reasons and things I am planning to do once I figure out how to create this table.

example:

Name	Purchase
Ariel	Example Item1
Alona	Example Item2
Ariel	Example Item3
Ariel	Example Item4
Scott	Example Item5

<tbody>
</tbody>

so the above is the master table, and in the lookup cell I type "Ariel", so the new table I am working on should then automatically change to this.

Name	Purchase
Ariel	Example Item1
Ariel	Example Item3
Ariel	Example Item4

<tbody>
</tbody>

I know its possible since the guy did it in the video, but I'm not sure if its possible using the tables in excel 2016.

regardless, when I tried to do what the guy did above, I got #REF .
if anyone has anything like this for excel 2016, please send it to me so i can see how it works and try to edit it.

thanks everyone!

 

[Aladin Akyurek]

@FDibbins
i tried that but it didn't work if I tried for look for anything other than AA, not sure why.

@Aladin Akyurek
that gets rid of duplicates perfectly, but it doesn't filter them out.

the only solution I have in mind right now is to have 3 tables; the master data table, the one that merges duplicates, and the final one that allows auto filter.
- is there anyway to accomplish the 2 last tables in one table? otherwise I guess I would just have to do the three table approach.

thanks,

What is the output exactly that you want given the input the formula considers?

 

[Kyletok]

Ok this got a bit messy, but try this in the helper column, copied down
D2=-SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6=B2),--($C$2:$C$6<0))+COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,">0")

so close I actually thought it was perfect until i tried a few things

Name	Location	Number of Stores	AA	Name	Location	Number of Stores
aa	Toronto	1	0	aa	Toronto	2
aa	Toronto	1	1	aa	Toronto	2
aa	Toronto	-2	1
			0
			0

<tbody>
</tbody>

still doesnt seem to work properly, as u can see it should add up to 0 and therefore not show.

What is the output exactly that you want given the input the formula considers?

alright I will try to explain as best as I could:

There is a large list of Clients, and every client has one or more account in one of the locations. every time a client deactivates an account we remove it by adding a new entry with the client's name, location, and a -1 for number of accounts. I want to prepare a document that will automatically update depending on which client I give the person viewing access to.
so every team member will only have access to 4 or 5 clients thats where I will give them the document with a data validation for those clients. by selecting one of the clients in the DV list, the list they have should update to the list of locations they have accounts in, and how many accounts they currently have.

so the new list will also sum all the entries of clients and the locations.
example: (Looking at Client Ariel)

Name	Location	Count	Name	Location	Count
George	Toronto	1	Ariel	Ottawa	2
Ariel	Toronto	1
Ariel	Ottawa	2
Ariel	Toronto	-1
Ariel	Ottawa	-1
Ariel	Ottawa	1

<tbody>
</tbody>

So few things the formula did:

• George is not included because the client we are looking for is Ariel (in DV list).
• Ariel, Toronto, is not included because the sum is < 1
• The only entry showing is Ariel, Ottawa because the sum is currently 2.

Its similar to advance filter, but I have to limit access to information.

Hope someone can help me accomplish this.

thank you!

 

[Aladin Akyurek]

@Kyletok

Please try to post a small sample (input) then followed by the output that goes with that input.

Try to also specify the condition under which the output must be generated. (The last post uses both "sum" and "count", a usage that was confusing [to me].)

 

[SpillerBD]

Your sample is a simple Pivot Table. A pivot Table Filter can eliminate the zero/less than zero items.
For Distribution purposes like you are asking, the Name would go in the Page Filter of the Pivot Table and then using the Show Report Filter Pages, you end up with a Sheet for each name with their summary. If the Pivot Table Option, "Save source data with file" is deactivated, each sheet can be uploaded with appropriate share permissions to OneDrive/File sharing location.

 

[Kyletok]

@Kyletok

Please try to post a small sample (input) then followed by the output that goes with that input.

Try to also specify the condition under which the output must be generated. (The last post uses both "sum" and "count", a usage that was confusing [to me].)

here is an example of information that is on the table in which we are pulling information FROM.

Client Name	Location	# of Accounts
Ariel	Toronto	1
Scott	Ottawa	1
Marina	Montreal	2
Ariel	New York	1
George	Philadelphia	2
George	Toronto	2
Ariel	Toronto	-1
Marina	Montreal	-3
Ariel	Toronto	1

<tbody>
</tbody>

Now lets say I am making this documents for a team member who I want to have access only to information about George and Ariel. (not sure if you need to know this, but the master file will be on our shared cloud, and their individual documents will pull information from it).

I want the table that they see to do 3 things.

1. not include entries that are below 1 in the "# of accounts" column
2. not show the same name, location twice, just sum them up into one entry.
3. only show one client at the time, given by the cell which has a DV list.

so the team member will be able to choose to either see information about George or Ariel, and the document will automatically update as he changes the name in the DV cell.

so if he put George in the DV cell, his table would look like this:

Client Name	Location	# of Accounts
George	Philadelphia	2
George	Toronto	2

<tbody>
</tbody>

if he put Ariel, his table would look like this

Client Name	Location	# of Accounts
Ariel	Toronto	1
Ariel	New York	1

<tbody>
</tbody>

As you can see:

• Every location appears only once! despite having several entries in the master file.
• no entry with # lower than 1 will show, it will be hidden from them as the formula should exclude it.
• there can be many many locations per client, but each location should only show once with the sum of accounts in that location.

lets say another team member selected Marina in the DV list.
--> They would get no result because Marina has only one location which is Montreal, and the sum of montreal is -1.
so like i said, no entry with a value below 1 should show.

Hopefully I made sense here. let me know.

 

[FDibbins]

So this would be a progressive list? meaning that...
Ariel/Tor starts on Pos, then goes Neg and does not go Pos again
Ariel/Ott starts on Pos, goes Neg, goes back to Pos and does not go Neg again
??

I am having a hard time working through your sample from post #22

so the new list will also sum all the entries of clients and the locations.
example: (Looking at Client Ariel)


Name Location Count Name Location Count
George Toronto 1 Ariel Ottawa 2
Ariel Toronto 1
Ariel Ottawa 2
Ariel Toronto -1
Ariel Ottawa -1
Ariel Ottawa 1
So few things the formula did:
•George is not included because the client we are looking for is Ariel (in DV list).
•Ariel, Toronto, is not included because the sum is < 1
•The only entry showing is Ariel, Ottawa because the sum is currently 2.

Based on that, shouldn't A/T be 1, not 2?

 

[Kyletok]

on a normal list if u were to sum
George Toronto 1
Ariel Toronto 1
Ariel Ottawa 2
Ariel Toronto -1
Ariel Ottawa -1
Ariel Ottawa 1
(2)+(-1)+(1)=2
and because it is a value higher than 0, it should pull it from the master list. if it were below 1, it should not pull it. My team members should only see the entries where the sum of the {Client, Location} is above 0

the stuff under the blue heading in my comment #22 is what the output should look like, the stuff on the left is the input.