Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Seperating Information In an Excel Sheet
Ok, I have a report, which enables the user to copy and paste 100 report cards into a sheet, and that allow the main page to fill with information. The problem I am facing, is that in one section of the Report Card, there are MULTIPLE answers. I need to be able to "tally" how many times one issue was marked.
Example : Section 1 is : Agent ID & Scope of Appointmen There are 7 selections in this section. QA Agents will then listen to a call, and mark the appropriate selection, if the Agent is non-compliant. I need to be able to "tally" everytime a selection is made, and display it on the master page. The 7 sections are : 1) Agent did not give their full name. 2) Agent did not state the company name. 3) Agent did not state they were licensed. 4) Agent did not give the scope. 5) Agent covered this section on one or more calls, but not all calls. 6) Agent skipped this section. 7) Other compliance issue. Let's say the QA Rep chooses 1,4 & 7. Qa Rep 2 choose 4, 7 & 2. I need to be able to display on my master page that selection 1, was chosen once. Selection 2, was chosen once. And selection 7 was chosen twice. If anyone can hepl,, if its even possible, I would apprciate it!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating Information In an Excel Sheet
On Thursday, May 10, 2012 9:23:03 AM UTC-5, JPP wrote:
Ok, I have a report, which enables the user to copy and paste 100 report cards into a sheet, and that allow the main page to fill with information. The problem I am facing, is that in one section of the Report Card, there are MULTIPLE answers. I need to be able to "tally" how many times one issue was marked. Example : Section 1 is : Agent ID & Scope of Appointmen There are 7 selections in this section. QA Agents will then listen to a call, and mark the appropriate selection, if the Agent is non-compliant. I need to be able to "tally" everytime a selection is made, and display it on the master page. The 7 sections are : 1) Agent did not give their full name. 2) Agent did not state the company name. 3) Agent did not state they were licensed. 4) Agent did not give the scope. 5) Agent covered this section on one or more calls, but not all calls. 6) Agent skipped this section. 7) Other compliance issue. Let's say the QA Rep chooses 1,4 & 7. Qa Rep 2 choose 4, 7 & 2. I need to be able to display on my master page that selection 1, was chosen once. Selection 2, was chosen once. And selection 7 was chosen twice. If anyone can hepl,, if its even possible, I would apprciate it!! -- JPP Perhaps COUNTIF would help. Send me your file if desired. |
#3
|
|||
|
|||
Unfortunately, a count if statement doesn't work. The only way I have been able to get this to REMOTELY work, is with this code :
=IF('CCI Info'!O12=”1) Agent did not give their full name.,2) Agent did not state the company name.,3) Agent did not state they are licensed.,4) Agent did not give the scope.,5) Agent covered this section on one or more calls, but not all calls., 'CCI Info'!$O$12, “”) But the problem is, you have to repeat this code, over & over. Its become over daunting. The other problem is, there are multiple selections in each section. Section one for example has 8 selections. That means there could be a possible.... What... 100 combination choices. That would make this code PAGES long. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating Information In an Excel Sheet
Don's suggestion of using a CountIf() would work well although, having
said that, it would depend greatly on the layout: Heres a little on-the-fly suggestion: CCI Info Sheet A B C D E F G 1 1 2 3 4 5 6 7 2 1 2 3 5 6 3 3 7 4 1 2 4 6 5 3 4 5 7 Master Sheet: A B C D E F G 1 1 2 3 4 5 6 7 2 A2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=1") B2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=2") C2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=3") D2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=4") E2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=5") F2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=6") G2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=7") The above assumes both sheets'row 1 is header details. The result of the above give you the following: Master Sheet: A B C D E F G 1 1 2 3 4 5 6 7 2 3 3 4 3 3 3 3 You can look at this from other value perspectives, by that I mean you could mark each intersecting row/column with an "X" or just a "1" in which case you would change the countif to the following: A2 = =COUNTIF('CCI Info'!$A$2:$A$30000,"=1") B2 = =COUNTIF('CCI Info'!$B$2:$B$30000,"=1") C2 = =COUNTIF('CCI Info'!$C$2:$C$30000,"=1") D2 = =COUNTIF('CCI Info'!$D$2:$D$30000,"=1") E2 = =COUNTIF('CCI Info'!$E$2:$E$30000,"=1") F2 = =COUNTIF('CCI Info'!$F$2:$F$30000,"=1") G2 = =COUNTIF('CCI Info'!$G$2:$G$30000,"=1") OR A2 = =COUNTIF('CCI Info'!$A$2:$A$30000,"=x") B2 = =COUNTIF('CCI Info'!$B$2:$B$30000,"=x") C2 = =COUNTIF('CCI Info'!$C$2:$C$30000,"=x") D2 = =COUNTIF('CCI Info'!$D$2:$D$30000,"=x") E2 = =COUNTIF('CCI Info'!$E$2:$E$30000,"=x") F2 = =COUNTIF('CCI Info'!$F$2:$F$30000,"=x") G2 = =COUNTIF('CCI Info'!$G$2:$G$30000,"=x") If all this fails to help, you will need to give us a look at the workbook so we can suss it out. HTH Mick. |
#5
|
|||
|
|||
Yea Mick, I have tried that, and unfortunately that doesnt work, because the full selection is 1) Agent did not give their full name. It is not counting the entires when I just do a "1".
|
#6
|
|||
|
|||
Then when someone make MULTIPLE selections from the same list..... Then is gets wacky....
|
#7
|
|||
|
|||
Heres the file if it will help..... It is all done but the "Compliance Hot spots" area. This is where I am having an issue.
|
#8
|
|||
|
|||
Check out the monster code I had to put behind the 100% compliant calls section.
|
#9
|
|||
|
|||
ooooooooooooooooooooooooook.
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating Information In an Excel Sheet
On 22/05/2012 11:23 PM, JPP wrote:
Yea Mick, I have tried that, and unfortunately that doesnt work, because the full selection is 1) Agent did not give their full name. It is not counting the entires when I just do a "1". +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ Did you try expanding it to this: A2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=1) Agent did not give their full name.") B2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=2) Agent did not state the company name.") C2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=3) Agent did not state they were licensed.") D2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=4) Agent did not give the scope.") E2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=5) Agent covered this section on one or more calls, but not all calls.") F2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=6) Agent skipped this section.") G2 = =COUNTIF('CCI Info'!$A$2:$G$30000,"=7) Other compliance issue. ") HTH Mick. |
#11
|
|||
|
|||
Hey Mick, I did try that, actually I have that code in there now. My problem is when they pick more than one choice. I woul like to be able to seperate them. But its not working. In order to count muplitle issues, I have to use this code :
=IF('CCI Info'!O12="2) Agent did not state the company name.,3) Agent did not state they are licensed.", 'CCI Info'!$O$12, “0”) But with this code, I would have A LARGE script, and it doesnt seperate. I would have to script it for ALL combo choices. But when they are bunched together like above, it doesn "register" is as a mistake. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating Information In an Excel Sheet
Hey JPP
Well, I have been on the wrong train-track as to what you actually wanted... In your OP, you had this.. .................................................. ............................ The problem I am facing, is that in one section of the Report Card, there are MULTIPLE answers. I need to be able to "tally" how many times one issue was marked. .................................................. ............................ So my assumption was that you wanted to calculate how many choices were selected in section, in which I must apologize for wasting time. This changes thing somewhat. Ok... Need more details as to layout, so if you can upload it to one of the file sharing sites I can have a look at it. Cheers Mick. |
#13
|
|||
|
|||
I already posted it, can you not see it? And that statement is true, just need it to seperate the answers when an rep choices more than one.
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating Information In an Excel Sheet
On 24/05/2012 7:09 AM, JPP wrote:
I already posted it, can you not see it? And that statement is true, just need it to seperate the answers when an rep choices more than one. +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ You can't attach to newsgroups. You have to upload it to a filesharing website. Copy/Paste the link here and we all can access it. Cheers Mick. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating Information In an Excel Sheet
On Thursday, May 10, 2012 9:23:03 AM UTC-5, JPP wrote:
Ok, I have a report, which enables the user to copy and paste 100 report cards into a sheet, and that allow the main page to fill with information. The problem I am facing, is that in one section of the Report Card, there are MULTIPLE answers. I need to be able to "tally" how many times one issue was marked. Example : Section 1 is : Agent ID & Scope of Appointmen There are 7 selections in this section. QA Agents will then listen to a call, and mark the appropriate selection, if the Agent is non-compliant. I need to be able to "tally" everytime a selection is made, and display it on the master page. The 7 sections are : 1) Agent did not give their full name. 2) Agent did not state the company name. 3) Agent did not state they were licensed. 4) Agent did not give the scope. 5) Agent covered this section on one or more calls, but not all calls. 6) Agent skipped this section. 7) Other compliance issue. Let's say the QA Rep chooses 1,4 & 7. Qa Rep 2 choose 4, 7 & 2. I need to be able to display on my master page that selection 1, was chosen once. Selection 2, was chosen once. And selection 7 was chosen twice. If anyone can hepl,, if its even possible, I would apprciate it!! -- JPP You may send direct to me with a complete explanation. |
#16
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seperating information in a cell | Excel Discussion (Misc queries) | |||
Seperating information in a cell | Excel Worksheet Functions | |||
in excel how to pull only used information to another sheet | Excel Worksheet Functions | |||
How do you take out duplicated information on a excel sheet? | Excel Discussion (Misc queries) | |||
Excel - automatic information from one sheet to another?? | Excel Discussion (Misc queries) |