I need help with my list
 

I have a list that I need to flag for errors. The error would be an ID (Col.
A), that has more than 1 Competency (col. B) coded as 1 in the prof column
(Col C.). Ultimately I would like to filter the list for the ID number along
with the data on the row. My data is approximately 27,000 rows. Looking at
the sample provided below, I can visually see that ID # 4021445 should be
flagged because security consultant and admin support are both flagged as a 1
in col. C.


Col. A Col. B Col. C
ID Competency Prof
1605875 Admin Support 1
4022044 Admin Support 1
4022044 Graphics Desinger 2
4027165 Admin Support 1
4021445 Admin Support 1
4021445 Test Engineer 2
4021445 Security Consultant 1
4021445 Manager 5
4021445 IT Specialist 1

Any suggestions would be very helpful. I have tried running pivots and it
gives me the data, however, I need to isolate the errors only and not include
the entire list in the report. I have also tried the filters, to no avail.

Thanks in advance
reneets

I need help with my list
 

one method in D2 enter
=sumproduct(--(A$1:A$27000=A2),--(C$1:C$27000=1))
copy and paste down
use autofilter on column D for 1

"reneets" wrote:

I have a list that I need to flag for errors. The error would be an ID (Col.
A), that has more than 1 Competency (col. B) coded as 1 in the prof column
(Col C.). Ultimately I would like to filter the list for the ID number along
with the data on the row. My data is approximately 27,000 rows. Looking at
the sample provided below, I can visually see that ID # 4021445 should be
flagged because security consultant and admin support are both flagged as a 1
in col. C.


Col. A Col. B Col. C
ID Competency Prof
1605875 Admin Support 1
4022044 Admin Support 1
4022044 Graphics Desinger 2
4027165 Admin Support 1
4021445 Admin Support 1
4021445 Test Engineer 2
4021445 Security Consultant 1
4021445 Manager 5
4021445 IT Specialist 1

Any suggestions would be very helpful. I have tried running pivots and it
gives me the data, however, I need to isolate the errors only and not include
the entire list in the report. I have also tried the filters, to no avail.

Thanks in advance
reneets

I need help with my list
 

Try this on D2:

IF(SUMPRODUCT(--($A$2:$A$10=A2),--($C$2:$C$10=1) ) 1, "X","")

--

Rodrigo Ferreira


"reneets" escreveu na mensagem
...
I have a list that I need to flag for errors. The error would be an ID
(Col.
A), that has more than 1 Competency (col. B) coded as 1 in the prof column
(Col C.). Ultimately I would like to filter the list for the ID number
along
with the data on the row. My data is approximately 27,000 rows. Looking
at
the sample provided below, I can visually see that ID # 4021445 should be
flagged because security consultant and admin support are both flagged as
a 1
in col. C.


Col. A Col. B Col. C
ID Competency Prof
1605875 Admin Support 1
4022044 Admin Support 1
4022044 Graphics Desinger 2
4027165 Admin Support 1
4021445 Admin Support 1
4021445 Test Engineer 2
4021445 Security Consultant 1
4021445 Manager 5
4021445 IT Specialist 1

Any suggestions would be very helpful. I have tried running pivots and it
gives me the data, however, I need to isolate the errors only and not
include
the entire list in the report. I have also tried the filters, to no
avail.

Thanks in advance
reneets

I need help with my list
 

What does the "--" mean inside the paren and before the paren??

"Rodrigo Ferreira" wrote:

Try this on D2:

IF(SUMPRODUCT(--($A$2:$A$10=A2),--($C$2:$C$10=1) ) 1, "X","")

--

Rodrigo Ferreira


"reneets" escreveu na mensagem
...
I have a list that I need to flag for errors. The error would be an ID
(Col.
A), that has more than 1 Competency (col. B) coded as 1 in the prof column
(Col C.). Ultimately I would like to filter the list for the ID number
along
with the data on the row. My data is approximately 27,000 rows. Looking
at
the sample provided below, I can visually see that ID # 4021445 should be
flagged because security consultant and admin support are both flagged as
a 1
in col. C.


Col. A Col. B Col. C
ID Competency Prof
1605875 Admin Support 1
4022044 Admin Support 1
4022044 Graphics Desinger 2
4027165 Admin Support 1
4021445 Admin Support 1
4021445 Test Engineer 2
4021445 Security Consultant 1
4021445 Manager 5
4021445 IT Specialist 1

Any suggestions would be very helpful. I have tried running pivots and it
gives me the data, however, I need to isolate the errors only and not
include
the entire list in the report. I have also tried the filters, to no
avail.

Thanks in advance
reneets

I need help with my list
 

-- converts a TRUE/FALSE to 1/0 which allows SUMPRODUCT to calculate/count
the results.

"reneets" wrote:

What does the "--" mean inside the paren and before the paren??

"Rodrigo Ferreira" wrote:

Try this on D2:

IF(SUMPRODUCT(--($A$2:$A$10=A2),--($C$2:$C$10=1) ) 1, "X","")

--

Rodrigo Ferreira


"reneets" escreveu na mensagem
...
I have a list that I need to flag for errors. The error would be an ID
(Col.
A), that has more than 1 Competency (col. B) coded as 1 in the prof column
(Col C.). Ultimately I would like to filter the list for the ID number
along
with the data on the row. My data is approximately 27,000 rows. Looking
at
the sample provided below, I can visually see that ID # 4021445 should be
flagged because security consultant and admin support are both flagged as
a 1
in col. C.


Col. A Col. B Col. C
ID Competency Prof
1605875 Admin Support 1
4022044 Admin Support 1
4022044 Graphics Desinger 2
4027165 Admin Support 1
4021445 Admin Support 1
4021445 Test Engineer 2
4021445 Security Consultant 1
4021445 Manager 5
4021445 IT Specialist 1

Any suggestions would be very helpful. I have tried running pivots and it
gives me the data, however, I need to isolate the errors only and not
include
the entire list in the report. I have also tried the filters, to no
avail.

Thanks in advance
reneets

I need help with my list
 

Thank you all so much!

"Toppers" wrote:

-- converts a TRUE/FALSE to 1/0 which allows SUMPRODUCT to calculate/count
the results.

"reneets" wrote:

What does the "--" mean inside the paren and before the paren??

"Rodrigo Ferreira" wrote:

Try this on D2:

IF(SUMPRODUCT(--($A$2:$A$10=A2),--($C$2:$C$10=1) ) 1, "X","")

--

Rodrigo Ferreira


"reneets" escreveu na mensagem
...
I have a list that I need to flag for errors. The error would be an ID
(Col.
A), that has more than 1 Competency (col. B) coded as 1 in the prof column
(Col C.). Ultimately I would like to filter the list for the ID number
along
with the data on the row. My data is approximately 27,000 rows. Looking
at
the sample provided below, I can visually see that ID # 4021445 should be
flagged because security consultant and admin support are both flagged as
a 1
in col. C.


Col. A Col. B Col. C
ID Competency Prof
1605875 Admin Support 1
4022044 Admin Support 1
4022044 Graphics Desinger 2
4027165 Admin Support 1
4021445 Admin Support 1
4021445 Test Engineer 2
4021445 Security Consultant 1
4021445 Manager 5
4021445 IT Specialist 1

Any suggestions would be very helpful. I have tried running pivots and it
gives me the data, however, I need to isolate the errors only and not
include
the entire list in the report. I have also tried the filters, to no
avail.

Thanks in advance
reneets