Help filtering a list

megdawoman

New Member
Joined
Apr 15, 2009
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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’)
 
Upvote 0
try this

Book1
ABCD
1CustomerCard N0
2Customer011>1 card
3Customer023>1 card
4Customer035 
5Customer024>1 card
6Customer056 
7Customer012>1 card
8Customer077 
9Customer088>1 card
10Customer088>1 card
11Customer089>1 card
Sheet3
Cell Formulas
RangeFormula
D2:D11D2=IF(AVERAGEIF($A$2:$A$11,A2,$B$2:$B$11)=B2,"",">1 card")
 
Upvote 0
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
ABCD
1NameCard#TimeAction
2Merryl StreepKVB23504:32:49Exit
3Alannah MilesGTD37007:52:50Entry
4Jennifer LawrenceHTY71608:11:45Entry
5Snoop2508008:17:33Entry
6Michael MeyersHUH36608:33:53Entry
7John DoeKDT92708:35:47Exit
8Jessica SimpsonHCG16608:54:06Entry
9Michael JacksonKCV41409:14:56Entry
10RihannaHUK83710:50:10Entry
11Jessica Simpson2519311:31:03Exit
12Jessica SimpsonHCG16612:50:24Entry
13John DoeKDT92713:02:32Entry
14Jessica SimpsonHCG1614:04:08Exit
15RihannaHUK83715:17:25Exit
16Jessica Simpson2519315:36:51Entry
17Britney SpearsKNR22115:58:24Entry
18Alannah MilesGTD37016:29:46Exit
19Michael MeyersHUH36617:03:49Exit
20Michael MeyersHUH36617:03:51Exit
21Justin Timberlake2510117:10:59Exit
22Jennifer LawrenceHTY71618:08:04Exit
23Britney SpearsKNR22118:24:46Exit
24Jessica Simpson2519318:28:01Exit
25Snoop2508018:31:46Exit
26Michael JacksonKCV41419:42:38Exit
27Merryl StreepKVB23519:53:10Entry
RAW
 
Upvote 0
Please show us your desired/expected results based upon your example.
 
Upvote 0
Column E shows no of cards of each customer

Book1
ABCDE
1NameCard#TimeActionNo of cards
2Merryl StreepKVB23504:32:49Exit2
3Alannah MilesGTD37007:52:50Entry2
4Jennifer LawrenceHTY71608:11:45Entry2
5Snoop2508008:17:33Entry2
6Michael MeyersHUH36608:33:53Entry3
7John DoeKDT92708:35:47Exit2
8Jessica SimpsonHCG16608:54:06Entry2
9Michael JacksonKCV41409:14:56Entry2
10RihannaHUK83710:50:10Entry2
11Jessica Simpson2519311:31:03Exit3
12Jessica SimpsonHCG16612:50:24Entry2
13John DoeKDT92713:02:32Entry2
14Jessica SimpsonHCG1614:04:08Exit1
15RihannaHUK83715:17:25Exit2
16Jessica Simpson2519315:36:51Entry3
17Britney SpearsKNR22115:58:24Entry2
18Alannah MilesGTD37016:29:46Exit2
19Michael MeyersHUH36617:03:49Exit3
20Michael MeyersHUH36617:03:51Exit3
21Justin Timberlake2510117:10:59Exit1
22Jennifer LawrenceHTY71618:08:04Exit2
23Britney SpearsKNR22118:24:46Exit2
24Jessica Simpson2519318:28:01Exit3
25Snoop2508018:31:46Exit2
26Michael JacksonKCV41419:42:38Exit2
27Merryl StreepKVB23519:53:10Entry2
Sheet1
Cell Formulas
RangeFormula
E2:E27E2=COUNTIFS(A:A,A2,B:B,B2)
 
Upvote 0
Column E shows no of cards of each customer

Book1
ABCDE
1NameCard#TimeActionNo of cards
2Merryl StreepKVB23504:32:49Exit2
3Alannah MilesGTD37007:52:50Entry2
4Jennifer LawrenceHTY71608:11:45Entry2
5Snoop2508008:17:33Entry2
6Michael MeyersHUH36608:33:53Entry3
7John DoeKDT92708:35:47Exit2
8Jessica SimpsonHCG16608:54:06Entry2
9Michael JacksonKCV41409:14:56Entry2
10RihannaHUK83710:50:10Entry2
11Jessica Simpson2519311:31:03Exit3
12Jessica SimpsonHCG16612:50:24Entry2
13John DoeKDT92713:02:32Entry2
14Jessica SimpsonHCG1614:04:08Exit1
15RihannaHUK83715:17:25Exit2
16Jessica Simpson2519315:36:51Entry3
17Britney SpearsKNR22115:58:24Entry2
18Alannah MilesGTD37016:29:46Exit2
19Michael MeyersHUH36617:03:49Exit3
20Michael MeyersHUH36617:03:51Exit3
21Justin Timberlake2510117:10:59Exit1
22Jennifer LawrenceHTY71618:08:04Exit2
23Britney SpearsKNR22118:24:46Exit2
24Jessica Simpson2519318:28:01Exit3
25Snoop2508018:31:46Exit2
26Michael JacksonKCV41419:42:38Exit2
27Merryl StreepKVB23519:53:10Entry2
Sheet1
Cell Formulas
RangeFormula
E2:E27E2=COUNTIFS(A:A,A2,B:B,B2)

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

Book1
ABCDE
1NameCard#TimeAction
2Merryl StreepKVB23504:32:49Exit1
3Alannah MilesGTD37007:52:50Entry1
4Jennifer LawrenceHTY71608:11:45Entry1
5Snoop2508008:17:33Entry1
6Michael MeyersHUH36608:33:53Entry1
7John DoeKDT92708:35:47Exit1
8Jessica SimpsonHCG16608:54:06Entry3
9Michael JacksonKCV41409:14:56Entry1
10RihannaHUK83710:50:10Entry1
11Jessica Simpson2519311:31:03Exit3
12Jessica SimpsonHCG16612:50:24Entry3
13John DoeKDT92713:02:32Entry1
14Jessica SimpsonHCG1614:04:08Exit3
15RihannaHUK83715:17:25Exit1
16Jessica Simpson2519315:36:51Entry3
17Britney SpearsKNR22115:58:24Entry1
18Alannah MilesGTD37016:29:46Exit1
19Michael MeyersHUH36617:03:49Exit1
20Michael MeyersHUH36617:03:51Exit1
21Justin Timberlake2510117:10:59Exit1
22Jennifer LawrenceHTY71618:08:04Exit1
23Britney SpearsKNR22118:24:46Exit1
24Jessica Simpson2519318:28:01Exit3
25Snoop2508018:31:46Exit1
26Michael JacksonKCV41419:42:38Exit1
27Merryl StreepKVB23519:53:10Entry1
Sheet1
Cell Formulas
RangeFormula
E2:E27E2=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)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top