PDA

View Full Version : count unique words in columns



DarReNz
10-27-2005, 09:31 PM
Hi, i have 2 columns Imp and Stat in Out Worksheet. I use SUBTOTAL to count the number of columns for Stat. Now I want to know how am i able to store the total subtotal to a unique Imp which I want to output later to a graph. The graph will show how many stats handled by each imp. Any help is welcomed. Thanks

CCkfm2000
10-28-2005, 01:21 AM
post a example file.

Bob Phillips
10-28-2005, 01:37 AM
Hi, i have 2 columns Imp and Stat in Out Worksheet. I use SUBTOTAL to count the number of columns for Stat. Now I want to know how am i able to store the total subtotal to a unique Imp which I want to output later to a graph. The graph will show how many stats handled by each imp. Any help is welcomed. Thanks

This formula counts unique entries in a1:A20


=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

DarReNz
10-29-2005, 05:52 AM
i am trying to think of an idea how to do it. i want to have a graph that show the whole picture meaning Carol has 1 Pend and Jack has 2 Wait. Besides that, I also want to have individual graphs on Carol and Jack too. The thought i can think of was from the above using SUBTOTAL to count entries when the worksheet is filtered. Now i need to tie those entries to Carol, Jack from Imp column. Any ideas ?

eg.

Imp Stat
Carol Pend
Jack Wait
Jack Pend

Bob Phillips
10-29-2005, 05:55 AM
i am trying to think of an idea how to do it. i want to have a graph that show the whole picture meaning Carol has 1 Pend and Jack has 2 Wait. Besides that, I also want to have individual graphs on Carol and Jack too. The thought i can think of was from the above using SUBTOTAL to count entries when the worksheet is filtered. Now i need to tie those entries to Carol, Jack from Imp column. Any ideas ?

eg.

Imp Stat
Carol Pend
Jack Wait
Jack Pend

=SUMPRODUCT(--(A1:A20="Carol"),--(A1:A20="Pend"))

just extend for all possibilities, or pivot table it

DarReNz
10-29-2005, 05:58 AM
xld, is it possible to collect the entries from another worksheet ?

Bob Phillips
10-29-2005, 06:03 AM
xld, is it possible to collect the entries from another worksheet ?
Yes.

=SUMPRODUCT(--(Sheet2!A1:A20="Carol"),--(Sheet2!A1:A20="Pend"))

DarReNz
10-29-2005, 09:28 AM
xld, I used SUBTOTAL for the number of entries in the column(Bau worksheet) is correct, however when i used SUMPRODUCT it gave me an incorrect count. The formula for SUBTOTAL that I used is =SUBTOTAL(2, A3:A65536). I used A column here because the column is Number and I auto numbered it accordingly, therefore able to count how many entries. I was planning to use column F however the entries are all in words(Pend, Wait, etc).

But i figure i can't use SUBTOTAL as everytime the worksheet is filtered the count gets changed therefore i can't keep track of how many entries by each Imp. Therefore do i still use SUMPRODUCT to count for this ? I am still not sure how you count using it. Thanks

Eg. Param Worksheet
Imp Cases(Number of Entries)
Jack 6
Carol 4
Sam 3

Shazam
10-29-2005, 01:30 PM
Is it possible to post a sample file ?

Bob Phillips
10-29-2005, 04:15 PM
xld, I used SUBTOTAL for the number of entries in the column(Bau worksheet) is correct, however when i used SUMPRODUCT it gave me an incorrect count. The formula for SUBTOTAL that I used is =SUBTOTAL(2, A3:A65536). I used A column here because the column is Number and I auto numbered it accordingly, therefore able to count how many entries. I was planning to use column F however the entries are all in words(Pend, Wait, etc).

But i figure i can't use SUBTOTAL as everytime the worksheet is filtered the count gets changed therefore i can't keep track of how many entries by each Imp. Therefore do i still use SUMPRODUCT to count for this ? I am still not sure how you count using it. Thanks

Eg. Param Worksheet
Imp Cases(Number of Entries)
Jack 6
Carol 4
Sam 3

What SUMPRODUCT formula did you use and what result did you get?

DarReNz
10-29-2005, 08:33 PM
I used this formula


=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

Here is the workbook. As you can see I used the formula in L1 and J1 respectively in Bau worksheet. When you "Search By Imp", the worksheet is filtered and the values in L1 and J1(Total Entries) will change accordingly. I actually want to use this formulas under Cases column in Param Worksheet. Therefore, it shows Jack having 3 entries, and the rest having only 1. Any help ? Thanks

Bob Phillips
10-30-2005, 03:07 AM
I used this formula


=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

Here is the workbook. As you can see I used the formula in L1 and J1 respectively in Bau worksheet. When you "Search By Imp", the worksheet is filtered and the values in L1 and J1(Total Entries) will change accordingly. I actually want to use this formulas under Cases column in Param Worksheet. Therefore, it shows Jack having 3 entries, and the rest having only 1. Any help ? Thanks

I don't see what is wrong with the SUBTOTAL formula you are using, it seems to provide the correct result to me.

DarReNz
10-30-2005, 09:05 AM
hi i use this formula =SUMPRODUCT(--(Bau!A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) on Param worksheet but it doesn't seems to count on Bau worksheet and instead it still counts on Param worksheet. How do i correct this ?

Bob Phillips
10-30-2005, 10:32 AM
hi i use this formula =SUMPRODUCT(--(Bau!A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) on Param worksheet but it doesn't seems to count on Bau worksheet and instead it still counts on Param worksheet. How do i correct this ?

Qualify all ranges

=SUMPRODUCT(--(Bau!A2:A20<>"")/COUNTIF(Bau!A2:A20,Bau!A2:A20&""))

Shazam
10-30-2005, 01:54 PM
I think I know what are you trying to do
For the Input worksheet in cell B13 try using this formula:


=SUMPRODUCT(--(Bau!G2:G7=B11)*(Bau!F2:F7=Input!E5),Bau!H2:H7)


and in Param worksheet use this formula in cells C2:C7:

=COUNTIF(Bau!F$2:F$7,B2)

Also I notice that when I tried to use the formulas it was not calculationg properly so I use this code to clean up the data that you have in the worksheet. When that happens when you import data from somewhere sometimes it will bring unseen junk data on to your worksheet. Also I advise for you to use Data Validation Drop-down list the formulas responds better.




Sub Clean_Trim() Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))
Next

End Sub

DarReNz
10-30-2005, 06:50 PM
Hi Shazam i couldn't run the Clean_Trim(), it kept giving me a syntax error for this line Sub Clean_Trim() Dim CleanTrimRg As Range.

Just wondering can i collect entries from another worksheet using SUBTOTAL like this
=SUBTOTAL(--Bau!(2, A3:A65536)) ? It doesn't seem to work.

And the SUMPRODUCT still doesn't give me the value I want. I just want an equilavent of =SUBTOTAL(2, A3:A65536) for SUMPRODUCT.

Just wanted to make sure does SUMPRODUCT only counts unique entries based on words ? This is because there are columns i need to count consists of words and they may be similar, therefore I don't want to count unique for them.

Shazam
10-30-2005, 08:02 PM
Sorry for the code I pasted it worng.

Ok the SUBTOTAL Formula works only if you have use the Subtotal function on your worksheet in the tool bar and go to the Data menue. The Subtotal Formula only reads other Subtotal Formulas.
There is one thing I could think that it might give you the flexibilty you want. Look at the attachment below. But its only availible in Excel 2003. If you go to Data menue and choose create List it will apply Filters for each columns and drop down list of Subtotals formulas.

This formula will count how many jacks in a column.

=COUNTIF(Bau!F$2:F$7,"jack")

Look at the Attachment below and see if this works for you.









Sub Clean_Trim()



Dim CleanTrimRg As Range




Dim oCell As Range

Dim Func As WorksheetFunction


Set Func = Application.WorksheetFunction


On Error Resume Next

Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)

If Err Then MsgBox "No data to clean and Trim!": Exit Sub


For Each oCell In CleanTrimRg

oCell = Func.Clean(Func.Trim(oCell))

Next


End Sub

DarReNz
10-31-2005, 12:16 AM
hi Shazam

I will try to give u a clear picture of what i want. As you can see in the Param worksheet under Cases column, I have these

Implementer Cases
Jack 3
Eric 1
Sam 1
Cool 1
King 0

How do i get these values from Cases ? Firstly, I select any value from the drop down list(Search By Imp) at Input worksheet. Then at Bau worksheet you can see how many entries are there. If I select Jack, it will show you 3 entries. I want a formula or code that can store or keep track of these entries under Cases. I don't think SUBTOTAL and COUNTIF will work because I tried them. As for SUMPRODUCT, I am not sure how do i achieve those Cases using this formula. I hope you understand what I am trying to do. Need help .....