Help filtering a list

[megdawoman]

I have a list of all times a customer entered/exited a building. Said customers pay a monthly fee for access and we have identified that some customers somehow have two access cards and seem to be sharing them and only paying one fee. I need to filter the list of entries and exits so it ONLY shows the customers that are doing so using more than one card number, but I want the filtered list to show all entries and exits for those customers. Anyone have any ideas how I might tackle it? Column A contains customer name, Column B contains the card number, and the rest of the columns contain the entry and exit data.

 

[Joe4]

I think it would be helpful to see some sample data and your expected results based on that. Just be sure to "dummy up" your data remove/replace any sensitive data first.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, it would be helpful to see which version of Excel you are using, as the newer versions of Excel have some new functions that might be helpful here. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[AlanY]

try this

Book1
	A	B	C	D
1	Customer	Card N0
2	Customer01	1		>1 card
3	Customer02	3		>1 card
4	Customer03	5
5	Customer02	4		>1 card
6	Customer05	6
7	Customer01	2		>1 card
8	Customer07	7
9	Customer08	8		>1 card
10	Customer08	8		>1 card
11	Customer08	9		>1 card
Sheet3

Cell Formulas
Range	Formula
D2:D11	D2	=IF(AVERAGEIF($A$2:$A$11,A2,$B$2:$B$11)=B2,"",">1 card")

 

[megdawoman]

I think it would be helpful to see some sample data and your expected results based on that. Just be sure to "dummy up" your data remove/replace any sensitive data first.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, it would be helpful to see which version of Excel you are using, as the newer versions of Excel have some new functions that might be helpful here. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you for that information. My sample data is below.

Book1.xlsx
	A	B	C	D
1	Name	Card#	Time	Action
2	Merryl Streep	KVB235	04:32:49	Exit
3	Alannah Miles	GTD370	07:52:50	Entry
4	Jennifer Lawrence	HTY716	08:11:45	Entry
5	Snoop	25080	08:17:33	Entry
6	Michael Meyers	HUH366	08:33:53	Entry
7	John Doe	KDT927	08:35:47	Exit
8	Jessica Simpson	HCG166	08:54:06	Entry
9	Michael Jackson	KCV414	09:14:56	Entry
10	Rihanna	HUK837	10:50:10	Entry
11	Jessica Simpson	25193	11:31:03	Exit
12	Jessica Simpson	HCG166	12:50:24	Entry
13	John Doe	KDT927	13:02:32	Entry
14	Jessica Simpson	HCG16	14:04:08	Exit
15	Rihanna	HUK837	15:17:25	Exit
16	Jessica Simpson	25193	15:36:51	Entry
17	Britney Spears	KNR221	15:58:24	Entry
18	Alannah Miles	GTD370	16:29:46	Exit
19	Michael Meyers	HUH366	17:03:49	Exit
20	Michael Meyers	HUH366	17:03:51	Exit
21	Justin Timberlake	25101	17:10:59	Exit
22	Jennifer Lawrence	HTY716	18:08:04	Exit
23	Britney Spears	KNR221	18:24:46	Exit
24	Jessica Simpson	25193	18:28:01	Exit
25	Snoop	25080	18:31:46	Exit
26	Michael Jackson	KCV414	19:42:38	Exit
27	Merryl Streep	KVB235	19:53:10	Entry
RAW

 

[Joe4]

Please show us your desired/expected results based upon your example.

 

[AlanY]

Column E shows no of cards of each customer

Book1
	A	B	C	D	E
1	Name	Card#	Time	Action	No of cards
2	Merryl Streep	KVB235	04:32:49	Exit	2
3	Alannah Miles	GTD370	07:52:50	Entry	2
4	Jennifer Lawrence	HTY716	08:11:45	Entry	2
5	Snoop	25080	08:17:33	Entry	2
6	Michael Meyers	HUH366	08:33:53	Entry	3
7	John Doe	KDT927	08:35:47	Exit	2
8	Jessica Simpson	HCG166	08:54:06	Entry	2
9	Michael Jackson	KCV414	09:14:56	Entry	2
10	Rihanna	HUK837	10:50:10	Entry	2
11	Jessica Simpson	25193	11:31:03	Exit	3
12	Jessica Simpson	HCG166	12:50:24	Entry	2
13	John Doe	KDT927	13:02:32	Entry	2
14	Jessica Simpson	HCG16	14:04:08	Exit	1
15	Rihanna	HUK837	15:17:25	Exit	2
16	Jessica Simpson	25193	15:36:51	Entry	3
17	Britney Spears	KNR221	15:58:24	Entry	2
18	Alannah Miles	GTD370	16:29:46	Exit	2
19	Michael Meyers	HUH366	17:03:49	Exit	3
20	Michael Meyers	HUH366	17:03:51	Exit	3
21	Justin Timberlake	25101	17:10:59	Exit	1
22	Jennifer Lawrence	HTY716	18:08:04	Exit	2
23	Britney Spears	KNR221	18:24:46	Exit	2
24	Jessica Simpson	25193	18:28:01	Exit	3
25	Snoop	25080	18:31:46	Exit	2
26	Michael Jackson	KCV414	19:42:38	Exit	2
27	Merryl Streep	KVB235	19:53:10	Entry	2
Sheet1

Cell Formulas
Range	Formula
E2:E27	E2	=COUNTIFS(A:A,A2,B:B,B2)

 

[AlanY]

Column E shows no of cards of each customer

Book1
	A	B	C	D	E
1	Name	Card#	Time	Action	No of cards
2	Merryl Streep	KVB235	04:32:49	Exit	2
3	Alannah Miles	GTD370	07:52:50	Entry	2
4	Jennifer Lawrence	HTY716	08:11:45	Entry	2
5	Snoop	25080	08:17:33	Entry	2
6	Michael Meyers	HUH366	08:33:53	Entry	3
7	John Doe	KDT927	08:35:47	Exit	2
8	Jessica Simpson	HCG166	08:54:06	Entry	2
9	Michael Jackson	KCV414	09:14:56	Entry	2
10	Rihanna	HUK837	10:50:10	Entry	2
11	Jessica Simpson	25193	11:31:03	Exit	3
12	Jessica Simpson	HCG166	12:50:24	Entry	2
13	John Doe	KDT927	13:02:32	Entry	2
14	Jessica Simpson	HCG16	14:04:08	Exit	1
15	Rihanna	HUK837	15:17:25	Exit	2
16	Jessica Simpson	25193	15:36:51	Entry	3
17	Britney Spears	KNR221	15:58:24	Entry	2
18	Alannah Miles	GTD370	16:29:46	Exit	2
19	Michael Meyers	HUH366	17:03:49	Exit	3
20	Michael Meyers	HUH366	17:03:51	Exit	3
21	Justin Timberlake	25101	17:10:59	Exit	1
22	Jennifer Lawrence	HTY716	18:08:04	Exit	2
23	Britney Spears	KNR221	18:24:46	Exit	2
24	Jessica Simpson	25193	18:28:01	Exit	3
25	Snoop	25080	18:31:46	Exit	2
26	Michael Jackson	KCV414	19:42:38	Exit	2
27	Merryl Streep	KVB235	19:53:10	Entry	2
Sheet1

Cell Formulas
Range	Formula
E2:E27	E2	=COUNTIFS(A:A,A2,B:B,B2)

the previous one is wrong,
try this to give unique no of cards for each customers

Book1
	A	B	C	D	E
1	Name	Card#	Time	Action
2	Merryl Streep	KVB235	04:32:49	Exit	1
3	Alannah Miles	GTD370	07:52:50	Entry	1
4	Jennifer Lawrence	HTY716	08:11:45	Entry	1
5	Snoop	25080	08:17:33	Entry	1
6	Michael Meyers	HUH366	08:33:53	Entry	1
7	John Doe	KDT927	08:35:47	Exit	1
8	Jessica Simpson	HCG166	08:54:06	Entry	3
9	Michael Jackson	KCV414	09:14:56	Entry	1
10	Rihanna	HUK837	10:50:10	Entry	1
11	Jessica Simpson	25193	11:31:03	Exit	3
12	Jessica Simpson	HCG166	12:50:24	Entry	3
13	John Doe	KDT927	13:02:32	Entry	1
14	Jessica Simpson	HCG16	14:04:08	Exit	3
15	Rihanna	HUK837	15:17:25	Exit	1
16	Jessica Simpson	25193	15:36:51	Entry	3
17	Britney Spears	KNR221	15:58:24	Entry	1
18	Alannah Miles	GTD370	16:29:46	Exit	1
19	Michael Meyers	HUH366	17:03:49	Exit	1
20	Michael Meyers	HUH366	17:03:51	Exit	1
21	Justin Timberlake	25101	17:10:59	Exit	1
22	Jennifer Lawrence	HTY716	18:08:04	Exit	1
23	Britney Spears	KNR221	18:24:46	Exit	1
24	Jessica Simpson	25193	18:28:01	Exit	3
25	Snoop	25080	18:31:46	Exit	1
26	Michael Jackson	KCV414	19:42:38	Exit	1
27	Merryl Streep	KVB235	19:53:10	Entry	1
Sheet1

Cell Formulas
Range	Formula
E2:E27	E2	=IFERROR(SUM(--(FREQUENCY(IF($A$2:$A$27=A2,MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW($B$2)+1)>0)),0)