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!

 

[FDibbins]

If you already have the DV list created and working, the rest is a (relatively) simple process.

I am not able to view images, so assuming your data looks something like this...

	A​	B​	C​
1​	Name	Blah1	Blah2
2​	aa	10​	15​
3​	bb	10​	15​
4​	cc	10​	15​
5​	aa	10​	15​
6​	bb	10​	15​
7​	cc	10​	15​
8​	aa	10​	15​
9​	bb	10​	15​
10​	cc	10​	15​
11​	aa	10​	15​
12​	bb	10​	15​
13​	cc	10​	15​

With your DV in E1...

	E​	F​	G​	H​
1​	aa	Name	Blah1	Blah2
2​		aa	10​	15​
3​		aa	10​	15​
4​		aa	10​	15​
5​		aa	10​	15​
6​

F2=IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$13=$E$1,ROW($A$2:$A$13)),ROWS($A$1:A1))),"")
This is an ARRAY formula, entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.
Adjust ranges as needed
Then just copy don and across as needed

 

[Kyletok]

that works when I use a simple range like A1:A13, but is it possible to use a table column as the range?
the data im pulling is from a large table of data and I would like for it to find everything in the name column which will always be updated with new entries.

 

[Kyletok]

sorry, I checked and it sure can! thank you so much. that is one step forward.

could you type out the formula for if I were to look for 2 criterias? lets say I was looking for "aa" and any value that is above 15 in another column.

 

[FDibbins]

Sure :0

New data sat...

	A​	B​	C​
1​	Name	Blah1	Blah2
2​	aa	10​	5​
3​	bb	20​	10​
4​	cc	30​	15​
5​	aa	40​	20​
6​	bb	50​	25​
7​	cc	60​	30​
8​	aa	70​	35​
9​	bb	80​	40​
10​	cc	90​	45​
11​	aa	100​	50​
12​	bb	110​	55​
13​	cc	120​	60​

E1 and E2 contain the criteria

	E​	F​	G​	H​
1​	aa	Name	Blah1	Blah2
2​	15​	aa	40​	20​
3​		aa	70​	35​
4​		aa	100​	50​
5​

F2=IFERROR(INDEX(A:A,SMALL(IF(($A$2:$A$13=$E$1)*($C$2:$C$13>=$E$2),ROW($A$2:$A$13)),ROWS($A$1:A1))),"")
still ARRAY entered then copied ad needed

 

[Kyletok]

This works thank you!

haha I hope you don't mind me asking for one more thing.

can I combine a few rows depending on criterias? ill give you an example.

Name	Location	Number of Stores
AA	Toronto	2
AA	Philadelphia	3
BB	Toronto	2
AA	Toronto	4
BB	Ottawa	1

<tbody>
</tbody>

and lets say I did what I tried to do in the previous comments, however this time it will combine the number of stores in the ones in the same location. and the result would be something like this if I was looking for AA

Name	Location	Number of Stores
AA	Toronto	6
AA	Philadelphia	3

<tbody>
</tbody>

as you can see it combined Toronto.

I imagine this would be harder I hope someone is able to help me out with this.

 

[FDibbins]

See if you can adapt this to your needs. I took a different approach, based on your last request.

	A​	B​	C​
1​	Name	Location	Number of Stores
2​	AA	Toronto	2
3​	AA	Philadelphia	3
4​	BB	Toronto	2
5​	AA	Toronto	4
6​	BB	Ottawa	1

For the extract/summary...

	E​	F​	G​
1​	Name	Location	Number of Stores
2​	AA	Toronto	6​
3​	AA	Philadelphia	3​
4​	BB	Toronto	2​
5​	BB	Ottawa	1​

E2=IFERROR(INDEX(A$2:A$6,MATCH(0,INDEX(COUNTIFS($E$1:E1,$A$2:$A$6,$F$1:F1,$B$2:$B$6),),0)),"")
Regular formula, copied down and across to he next column
G2=SUMIFS($C$2:$C$6,$A$2:$A$6,E2,$B$2:$B$6,F2)
copied down

 

[Kyletok]

No that doesn't really help me accomplish what I'm trying to do. You know let's forget about the sum part. If there is a way that I can have it pull information just like you showed me in the comments before, but instead of showing rows that are identical more than once, it'll only show unique rows. If I can accomplish that I can just use the SUMIFS() function after to accomplish the total count for that location.

 

[FDibbins]

Using the same tables from post #7 ...
Put AA (your choice) in D1
E2=IF(IFERROR(INDEX(A$2:A$6,MATCH(0,INDEX(COUNTIFS($E$1:E1,$A$2:$A$6,$F$1:F1,$B$2:$B$6),),0)),"")=$D$1,IFERROR(INDEX(A$2:A$6,MATCH(0,INDEX(COUNTIFS($E$1:E1,$A$2:$A$6,$F$1:F1,$B$2:$B$6),),0)),""),"")
F2=IF(E2=$D$1,IFERROR(INDEX(B$2:B$6,MATCH(0,INDEX(COUNTIFS($E$1:F1,$A$2:$A$6,$F$1:G1,$B$2:$B$6),),0)),""),"")

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	E​	F​	G​
1​	Name	Location	Number of Stores	4​
2​	AA	Toronto	2​	Name	Location	Total # Stores
3​	AA	Philadelphia	3​	AA	Toronto	6​
4​	BB	Toronto	2​	AA	Philadelphia	3​
5​	AA	Toronto	4​	BB	Toronto	2​
6​	BB	Ottawa	1​	BB	Ottawa	1​
7​

In E1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$6=""),MATCH($A$2:$A$6&"|"&$B$2:$B$6,$A$2:$A$6&"|"&$B$2:$B$6,0)),ROW($A$2:$A$6)-ROW($A$2)+1),1))

In E3 control+shift+enter, not just enter, copy across to F3, and down:

=IF(ROWS(E$3:E3)>$E$1,"",INDEX(A$2:A$6,SMALL(IF(FREQUENCY(IF(1-($A$2:$A$6=""),MATCH($A$2:$A$6&"|"&$B$2:$B$6,$A$2:$A$6&"|"&$B$2:$B$6,0)),ROW(A$2:A$6)-ROW(A$2)+1),ROW(A$2:A$6)-ROW(A$2)+1),ROWS(E$3:E3))))

In G3 just enter and copy down:

=IF($E3="","",SUMIFS($C$2:$C$6,$A$2:$A$6,$E3,$B$2:$B$6,$F3))