Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
TRYING TO SORT OR FILTER DUPLICATE DATA
I have two columns, one with numbers and another with yes or no. In the
number column I will have 5 digit numbers that may repeat 2 to three times in that column and along side in the other column it will say either yes or no. For any particular number it will only say yes or no, it wont be yes in one row and no in another. This information gets uplinked from another sheet and hence is that way. heres a sample Warranty W.O. YES 66888 YES 66888 NO 67471 NO 67471 YES 66992 YES 66992 YES 66992 YES 66948 YES 66948 NO 66945 Now what I need to do is count the number of W.O's that were waranty and which were not, without couting twice or thrice for the same W.O. So in this example it would need to be a small table like this Warranty Yes NO Number of WO's 3 2 would really appreciate any help. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
TRYING TO SORT OR FILTER DUPLICATE DATA
Just wondering if I can adapt the formula for counting unique entries
=SUM(N(FREQUENCY(A1:A10,A1:A10)0)) to be able to count the unique entries only if there is a yes in the column to its left. "areezm" wrote: I have two columns, one with numbers and another with yes or no. In the number column I will have 5 digit numbers that may repeat 2 to three times in that column and along side in the other column it will say either yes or no. For any particular number it will only say yes or no, it wont be yes in one row and no in another. This information gets uplinked from another sheet and hence is that way. heres a sample Warranty W.O. YES 66888 YES 66888 NO 67471 NO 67471 YES 66992 YES 66992 YES 66992 YES 66948 YES 66948 NO 66945 Now what I need to do is count the number of W.O's that were waranty and which were not, without couting twice or thrice for the same W.O. So in this example it would need to be a small table like this Warranty Yes NO Number of WO's 3 2 would really appreciate any help. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
TRYING TO SORT OR FILTER DUPLICATE DATA
Try Data -- Filter -- Advanced Filter...
Select Copy to another location List range is your list of two columns Criteria range leave blank Copy to cell D1 or something Check Unique records only Then you can do: =COUNTIF(D:D,"YES") and =COUNTIF(D:D,"NO") to count your two values -Simon "areezm" wrote: Just wondering if I can adapt the formula for counting unique entries =SUM(N(FREQUENCY(A1:A10,A1:A10)0)) to be able to count the unique entries only if there is a yes in the column to its left. "areezm" wrote: I have two columns, one with numbers and another with yes or no. In the number column I will have 5 digit numbers that may repeat 2 to three times in that column and along side in the other column it will say either yes or no. For any particular number it will only say yes or no, it wont be yes in one row and no in another. This information gets uplinked from another sheet and hence is that way. heres a sample Warranty W.O. YES 66888 YES 66888 NO 67471 NO 67471 YES 66992 YES 66992 YES 66992 YES 66948 YES 66948 NO 66945 Now what I need to do is count the number of W.O's that were waranty and which were not, without couting twice or thrice for the same W.O. So in this example it would need to be a small table like this Warranty Yes NO Number of WO's 3 2 would really appreciate any help. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
TRYING TO SORT OR FILTER DUPLICATE DATA
Using data filter will not work automatically the next time I need to use
this template. This is a template to be used by various people who may not know how to data sort. for now I have set up another column where it will copy the work order number to this column if it says yes in the left column. I used this command, =IF(A2="YES",B2,) Then from this column I used the command to identify number of unique data entries. This is the command, =(SUM(N(FREQUENCY(D2:D100,D2:D100)0)))-1 This is working for most of my sheets, but the problem is that I have one sheet in which there are over 50 categories instead of the two YES or NO. This method will be too confusing and time consuming so is there any command or formula I can use instead.??? Thanks though Simon "SimonCC" wrote: Try Data -- Filter -- Advanced Filter... Select Copy to another location List range is your list of two columns Criteria range leave blank Copy to cell D1 or something Check Unique records only Then you can do: =COUNTIF(D:D,"YES") and =COUNTIF(D:D,"NO") to count your two values -Simon "areezm" wrote: Just wondering if I can adapt the formula for counting unique entries =SUM(N(FREQUENCY(A1:A10,A1:A10)0)) to be able to count the unique entries only if there is a yes in the column to its left. "areezm" wrote: I have two columns, one with numbers and another with yes or no. In the number column I will have 5 digit numbers that may repeat 2 to three times in that column and along side in the other column it will say either yes or no. For any particular number it will only say yes or no, it wont be yes in one row and no in another. This information gets uplinked from another sheet and hence is that way. heres a sample Warranty W.O. YES 66888 YES 66888 NO 67471 NO 67471 YES 66992 YES 66992 YES 66992 YES 66948 YES 66948 NO 66945 Now what I need to do is count the number of W.O's that were waranty and which were not, without couting twice or thrice for the same W.O. So in this example it would need to be a small table like this Warranty Yes NO Number of WO's 3 2 would really appreciate any help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Match and Sort two range of data? | New Users to Excel | |||
Pivot Table Data Filter Problem | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |