Removing 'active' col from ALL 'col' to get 'inactive' col

[vancey]

Hi Guys,

I have a column that contains list of ALL active and inactive members ids, and a column containing active members ids, i want to use the ALL list to remove the active list to get the inactive members ids. i tried using
=IF(ISERROR(MATCH(R5,$Q$1:$Q$10000,0)),"Active","Inactive")
to compare the active vs the all list, if not found means it is inactive, then i cant filter all inactive together. but it doesnt work, some of the ids listed inactive but they are still found in the ALL column. Please help me.

Minisheet: (There are more than 3k rows, heres some example)

CS.xlsx
	H	I	J	K
1			ALL	SG Active Badges
2	Status	Status	LastName	Last 6 digits
3	Active	Active	V474521	511989
4	Active	Active	SC472895	511352
5	Active	Active	S307113	511305
6	Active	Active	GS80863497	510354
7	Inactive	Inactive	GS7820092	506750
8	Inactive	Inactive	GS68516173	506749
9	Active	Active	GS68113	506008
10	Active	Active	GS68036718	505987
11	Active	Active	GS67443186	504956
12	Active	Active	GS67264515	504955
13	Active	Active	GS67161646	504954
14	Active	Active	GS67131389	504947
15	Active	Active	GS67027836	504945
16	Active	Active	GS66601279	504942
17	Active	Active	GS472957	504938
18	Active	Active	GS471705	504937
19	Active	Active	GS421897	504936
20	Active	Active	GS420288	504934
21	Active	Active	GS333780	504933
22	Active	Active	GS307407	504932
23	Active	Active	GS190783	504924
24	Active	Active	EDWARDBARROW	504922
25	Active	Active	422828/	504917
26	Active	Active	3068/56	504916
27	Active	Active	65220085	504914
28	Active	Active	6368484	504913
29	Active	Active	4733354	504911
30	Active	Active	4720267	504910
31	Active	Active	4321482	504907
32	Active	Active	4227731	504904
33	Active	Active	4225592	504901
34	Active	Active	4221612	504900
35	Active	Active	4221333	504899
36	Active	Active	4211151	504898
37	Active	Active	4203238	504896
38	Active	Active	4094487	504894
39	Active	Active	3999453	504893
40	Active	Active	3607633	504891
41	Active	Active	2999104	504887
42	Active	Active	1677997	504885
43	Active	Active	966726	504877
44	Active	Active	901830	504876
45	Active	Active	897915	504875
46	Active	Active	893137	504874
47	Active	Active	880280	504870
48	Active	Active	865265	504869
49	Active	Active	779148	504867
50	Active	Active	744387	504865
51	Active	Active	744128	504864
52	Active	Active	697745	504862
53	Active	Active	686024	504861
54	Active	Active	672808	504860
55	Active	Active	649849	504858
56	Active	Active	644959	504857
57	Active	Active	642980	504856
58	Active	Active	642902	504854
59	Active	Active	619505	504853
60	Active	Active	521199	504848
61	Active	Active	512964	504843
62	Active	Active	511933	504842
63	Active	Active	506750	504841
64	Active	Active	506749	504840
65	Active	Active	506358	504839
66	Active	Active	506335	504838
67	Active	Active	506201	504837
68	Active	Active	505872	504836
69	Active	Active	504906	504835
70	Active	Active	504155	504834
71	Active	Active	504150	504833
72	Active	Active	504145	504831
73	Active	Active	504142	504830
74	Active	Active	504141	504829
75	Active	Active	504136	504828
76	Active	Active	504135	504827
77	Active	Active	504123	504826
78	Active	Active	504123	504825
79	Active	Active	504122	504823
80	Active	Active	504121	504822
81	Active	Active	504117	504821
82	Active	Active	504116	504817
83	Active	Active	504112	504815
84	Active	Active	504111	504814
85	Active	Active	504110	504808
86	Active	Active	504105	504807
87	Active	Active	504101	504806
88	Active	Active	504100	504805
89	Active	Active	504099	504804
90	Active	Active	504098	504803
91	Active	Active	504096	504802
92	Active	Active	504089	504801
93	Active	Active	504089	504800
94	Active	Active	504088	504799
95	Active	Active	504087	504792
96	Active	Active	504086	504791
97	Active	Active	504086	504790
98	Active	Active	504084	504789
99	Active	Active	504081	504788
100	Active	Active	504080	504784
101	Active	Active	504080	504781
102	Active	Active	504079	504780
103	Active	Active	504077	504779
104	Active	Active	504076	504778
105	Active	Active	504074	504777
106	Active	Active	504073	504775
107	Active	Active	504073	504773
108	Active	Active	504072	504770
109	Active	Active	504071	504769
110	Active	Active	504070	504766
111	Active	Active	504066	504764
112	Active	Active	504066	504763
113	Active	Active	504064	504762
114	Active	Active	504063	504761
115	Active	Active	504060	504346
116	Active	Active	504059	504345
117	Active	Active	504058	504344
118	Active	Active	504053	504343
119	Active	Active	504052	504340
120	Active	Active	504049	504329
121	Active	Active	504048	504327
122	Active	Active	504045	504323
123	Active	Active	504044	504319
124	Active	Active	504043	504318
125	Active	Active	504036	504317
126	Active	Active	504035	504316
127	Active	Active	504034	504310
128	Active	Active	504033	504157
129	Active	Active	504032	504156
130	Active	Active	504031	504151
131	Active	Active	504030	504145
132	Active	Active	504029	504144
133	Active	Active	504028	504143
134	Active	Active	504027	504142
135	Active	Active	504026	504140
136	Active	Active	504023	504139
137	Inactive	Inactive	504022	504135
138	Active	Active	504022	504129
139	Active	Active	504021	504128
140	Active	Active	504020	504127
141	Active	Active	504019	504126
142	Active	Active	504018	504125
143	Active	Active	504017	504124
144	Inactive	Inactive	504016	504123
145	Inactive	Inactive	504015	504121
146	Active	Active	504014	504113
147	Inactive	Inactive	504013	504110
148	Active	Active	504012	504108
149	Active	Active	504011	504106
150	Inactive	Inactive	504001	504105
151	Active	Active	504000	504097
152	Active	Active	504000	504095
153	Active	Active	503999	504094
154	Active	Active	503998	504093
155	Inactive	Inactive	503998	504089
156	Inactive	Inactive	503996	504084
157	Inactive	Inactive	503995	504081
158	Inactive	Inactive	503995	504079
159	Inactive	Inactive	503994	504076
160	Inactive	Inactive	503993	504074
161	Inactive	Inactive	503993	504073
162	Inactive	Inactive	503992	504070
163	Active	Active	503992	504069
164	Active	Active	503991	504068
165	Active	Active	503991	504067
166	Inactive	Inactive	503990	504066
167	Active	Active	503989	504065
168	Inactive	Inactive	503988	504064
169	Inactive	Inactive	503988	504063
170	Inactive	Inactive	503983	504060
171	Inactive	Inactive	503982	504058
172	Active	Active	503981	504057
173	Active	Active	503977	504056
174	Active	Active	503977	504055
175	Active	Active	503976	504053
176	Active	Active	503975	504052
177	Active	Active	503974	504049
178	Inactive	Inactive	503967	504045
179	Inactive	Inactive	503966	504044
180	Active	Active	503963	504042
181	Active	Active	503962	504041
182	Active	Active	503961	504040
183	Active	Active	503959	504039
184	Active	Active	503857	504038
185	Active	Active	503851	504037
186	Inactive	Inactive	503850	504036
187	Inactive	Inactive	503808	504035
188	Inactive	Inactive	503804	504034
189	Inactive	Inactive	503804	504033
190	Inactive	Inactive	503803	504032
191	Inactive	Inactive	503803	504031
192	Inactive	Inactive	503802	504029
193	Inactive	Inactive	503802	504028
194	Inactive	Inactive	503800	504027
Sheet1

Cell Formulas
Range	Formula
H3:H194	H3	=IF(ISERROR(VLOOKUP(K3,J:J,1,0)),"Active","Inactive")
I3:I194	I3	=IF(ISERROR(MATCH(K3,$J$1:$J$10000,0)),"Active","Inactive")

 

[DanteAmor]

You must look for the id of the All List in the active ids, if it is 0 it is inactive, if it is 1 then it is active.

Dante Amor
	I	J	K
1		ALL	SG Active Badges
2	Status	LastName	Last 6 digits
3	0	V474521	511989
4	0	SC472895	511352
5	0	S307113	511305
6	0	GS80863497	510354
7	0	GS7820092	506750
8	0	GS68516173	506749
9	0	GS68113	506008
10	0	GS68036718	505987
11	0	GS67443186	504956
12	0	GS67264515	504955
13	0	GS67161646	504954
14	0	GS67131389	504947
15	0	GS67027836	504945
16	0	GS66601279	504942
17	0	GS472957	504938
18	0	GS471705	504937
19	0	GS421897	504936
20	0	GS420288	504934
21	0	GS333780	504933
22	0	GS307407	504932
23	0	GS190783	504924
24	0	EDWARDBARROW	504922
25	0	422828/	504917
26	0	3068/56	504916
27	0	65220085	504914
28	0	6368484	504913
29	0	4733354	504911
30	0	4720267	504910
31	0	4321482	504907
32	0	4227731	504904
33	0	4225592	504901
34	0	4221612	504900
35	0	4221333	504899
36	0	4211151	504898
37	0	4203238	504896
38	0	4094487	504894
39	0	3999453	504893
40	0	3607633	504891
41	0	2999104	504887
42	0	1677997	504885
43	0	966726	504877
44	0	901830	504876
45	0	897915	504875
46	0	893137	504874
47	0	880280	504870
48	0	865265	504869
49	0	779148	504867
50	0	744387	504865
51	0	744128	504864
52	0	697745	504862
53	0	686024	504861
54	0	672808	504860
55	0	649849	504858
56	0	644959	504857
57	0	642980	504856
58	0	642902	504854
59	0	619505	504853
60	0	521199	504848
61	0	512964	504843
62	0	511933	504842
63	1	506750	504841
64	1	506749	504840
65	0	506358	504839
66	0	506335	504838
67	0	506201	504837
68	0	505872	504836
69	0	504906	504835
70	0	504155	504834
71	0	504150	504833
72	1	504145	504831
73	1	504142	504830
74	0	504141	504829
75	0	504136	504828
76	1	504135	504827
77	1	504123	504826
78	1	504123	504825
79	0	504122	504823
80	1	504121	504822
81	0	504117	504821
82	0	504116	504817
83	0	504112	504815
84	0	504111	504814
85	1	504110	504808
86	1	504105	504807
87	0	504101	504806
88	0	504100	504805
89	0	504099	504804
90	0	504098	504803
91	0	504096	504802
92	1	504089	504801
93	1	504089	504800
94	0	504088	504799
95	0	504087	504792
96	0	504086	504791
97	0	504086	504790
98	1	504084	504789
99	1	504081	504788
100	0	504080	504784
101	0	504080	504781
102	1	504079	504780
103	0	504077	504779
104	1	504076	504778
105	1	504074	504777
106	1	504073	504775
107	1	504073	504773
108	0	504072	504770
109	0	504071	504769
110	1	504070	504766
111	1	504066	504764
112	1	504066	504763
113	1	504064	504762
114	1	504063	504761
115	1	504060	504346
116	0	504059	504345
117	1	504058	504344
118	1	504053	504343
119	1	504052	504340
120	1	504049	504329
121	0	504048	504327
122	1	504045	504323
123	1	504044	504319
124	0	504043	504318
125	1	504036	504317
126	1	504035	504316
127	1	504034	504310
128	1	504033	504157
129	1	504032	504156
130	1	504031	504151
131	0	504030	504145
132	1	504029	504144
133	1	504028	504143
134	1	504027	504142
135	0	504026	504140
136	0	504023	504139
137	0	504022	504135
138	0	504022	504129
139	0	504021	504128
140	0	504020	504127
141	0	504019	504126
142	0	504018	504125
143	0	504017	504124
144	0	504016	504123
145	0	504015	504121
146	0	504014	504113
147	0	504013	504110
148	0	504012	504108
149	0	504011	504106
150	0	504001	504105
151	0	504000	504097
152	0	504000	504095
153	0	503999	504094
154	0	503998	504093
155	0	503998	504089
156	0	503996	504084
157	0	503995	504081
158	0	503995	504079
159	0	503994	504076
160	0	503993	504074
161	0	503993	504073
162	0	503992	504070
163	0	503992	504069
164	0	503991	504068
165	0	503991	504067
166	0	503990	504066
167	0	503989	504065
168	0	503988	504064
169	0	503988	504063
170	0	503983	504060
171	0	503982	504058
172	0	503981	504057
173	0	503977	504056
174	0	503977	504055
175	0	503976	504053
176	0	503975	504052
177	0	503974	504049
178	0	503967	504045
179	0	503966	504044
180	0	503963	504042
181	0	503962	504041
182	0	503961	504040
183	0	503959	504039
184	0	503857	504038
185	0	503851	504037
186	0	503850	504036
187	0	503808	504035
188	0	503804	504034
189	0	503804	504033
190	0	503803	504032
191	0	503803	504031
192	0	503802	504029
193	0	503802	504028
194	0	503800	504027
Hoja1

Cell Formulas
Range	Formula
I3:I194	I3	=COUNTIF(K:K,J3)

 

[vancey]

Hi DanteAmor,

Thanks for replying, i tried your method, wondering why id 504840 is active but i manually find by column in ALL list i cant find 504840. (See below row highlighted in orange)

CS.xlsx
	Q	R	S
1
2	UNIQUE
3	ALL	SG Active Badges
4	LastName	Last 6 digits	1 = Active 0 = Inactive
5	V474521	511989	0
6	SC472895	511352	0
7	S307113	511305	0
8	80863497	510354	0
9	7820092	506750	0
10	68516173	506749	0
11	68113	506008	0
12	68036718	505987	0
13	67443186	504956	0
14	67264515	504955	0
15	67161646	504954	0
16	67131389	504947	0
17	67027836	504945	0
18	66601279	504942	0
19	472957	504938	0
20	471705	504937	0
21	421897	504936	0
22	420288	504934	0
23	333780	504933	0
24	307407	504932	0
25	190783	504924	0
26	EDWARDBARROW	504922	0
27	422828/	504917	0
28	3068/56	504916	0
29	65220085	504914	0
30	6368484	504913	0
31	4733354	504911	0
32	4720267	504910	0
33	4321482	504907	0
34	4227731	504904	0
35	4225592	504901	0
36	4221612	504900	0
37	4221333	504899	0
38	4211151	504898	0
39	4203238	504896	0
40	4094487	504894	0
41	3999453	504893	0
42	3607633	504891	0
43	2999104	504887	0
44	1677997	504885	0
45	966726	504877	0
46	901830	504876	0
47	897915	504875	0
48	893137	504874	0
49	880280	504870	0
50	865265	504869	0
51	779148	504867	0
52	744387	504865	0
53	744128	504864	0
54	697745	504862	0
55	686024	504861	0
56	672808	504860	0
57	649849	504858	0
58	644959	504857	0
59	642980	504856	0
60	642902	504854	0
61	619505	504853	0
62	521199	504848	0
63	512964	504843	0
64	511933	504842	0
65	506750	504841	0
66	506749	504840	1
67	506358	504839	1
68	506335	504838	0
69	506201	504837	0
70	505872	504836	0
71	504906	504835	0
72	504155	504834	0
73	504150	504833	0
74	504145	504831	0
75	504142	504830	1
76	504141	504829	1
77	504136	504828	0
78	504135	504827	0
79	504123	504826	1
80	504123	504825	1
Sheet1

Cell Formulas
Range	Formula
S5	S5	=COUNTIF(Q:Q,R5)
S6:S80	S6	=COUNTIF(R:R,Q5)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$Q$4:$S$11372	S5

Thanks.
VanCe

 

[vancey]

Im sorry i made a mistake in above post, im unable to find edit post button.

the forumla u provided seemed to work, but why empty cells are also counted as active? and why is there a 2?

CS.xlsx
	Q	R	S
3423	407591	20793	1
3424	407590	20151	1
3425	407589	20076	0
3426	407588	19444	1
3427	407587	19396	1
3428	407586	15802	1
3429	407585	15764	2
3430	407584	11934	0
3431	407582	0	1
3432	407581		1
3433	407580		1
3434	407579		1
3435	407578		1
3436	407577		1
3437	407576		1
3438	407575		1
3439	407574		1
3440	407573		1
3441	407571		1
3442	407570		1
3443	407569		1
3444	407568		1
3445	407567		1
3446	407566		1
Sheet1

Cell Formulas
Range	Formula
S3423:S3446	S3423	=COUNTIF(Q$5:Q$11372,R3423)

 

[vancey]

I got it why its still considered 1, becoz theres a '0' in my All list.

 

[DanteAmor]

but why empty cells are also counted as active?

You must put the formula up to cell S3430. You are looking for the active value (column R) in ALL list. It no longer makes sense to search for an empty cell.

and why is there a 2?

That means that the active value (column R) is 2 times in ALL list.

__________
Perhaps, the following formula you like more.

Dante Amor
	Q	R	S
3	ALL	SG Active Badges
4	LastName	Last 6 digits	Active / Inactive
5	V474521	511989	Inactive
6	SC472895	511352	Inactive
7	S307113	511305	Inactive
8	GS80863497	510354	Inactive
9	GS67443186	506750	Active
10	619505	506749	Active
11	511933	506008	Inactive
12	506750	505987	Active
13	506749	504956	Inactive
14	506358	504858	Inactive
15	505987		Inactive
16	506201		Inactive
17	505987		Inactive
18	504906
19	504155
20	504150
21	504145
Hoja1

Cell Formulas
Range	Formula
S5:S17	S5	=IF(COUNTIF(Q:Q,R5),"Active", "Inactive")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Hoja1!$Q$4:$T$153	S5:S17

 

[vancey]

Thank you DanteAmor!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.