| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
| validation list--list depends on the selection of first list | New Users to Excel | |||
| list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
| find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions | |||
| sort list of players by team from player list on separate sheet | Excel Worksheet Functions | |||
Linear Mode
