| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting patters
Hi,
I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
If you just had 1's and 0's you could treat each row as a binary
number and combine the digits with powers of 2 to form a number. As you have 2's as well, though, then you would have to treat each row as a tertiary number (is that the right term? ternary? base-3?) and use powers of 3 to convert the 32 digits into an equivalent number. A UDF could do this for you. However, if you did this for a 32-digit base-3 number you would lose some precision, as Excel can only handle 15 digits accurately. So, it might be better to split the number and to deal with, say, 11 digits at a time (10 digits for the last group), such that you will then end up with 3 numbers per row in extra columns. You could then sort the block of data (plus the three extra columns) using the three columns as keys. A variation of this is to take 4 digits at a time in your UDF (81 possible values) and to convert this into a single ASCI character and then to concatenate the 8 characters together - these can then be sorted. It is then relatively easy to identify duplicates - either in the same sheet with a formula like: =IF(A2=A1,"dupl","unique") entered on row 2 and copied down, or you might make use of Advanced Filter by copying the derived numbers/text to a separate sheet and filtering to exclude duplicates. As for the "right solution", your question implies that there is only one, but there is usually a variety of ways of achieving something in Excel - this is just one way. Hope this helps. Pete On Mar 1, 3:21 pm, wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Further to this:
you can do this without a UDF by copying this formula into AG1: =CHAR(A1*27+B1*9+C1*3+D1+32)& CHAR(E1*27+F1*9+G1*3+H1+32)& CHAR(I1*27+J1*9+K1*3+L1+32)& CHAR(M1*27+N1*9+O1*3+P1+32)& CHAR(Q1*27+R1*9+S1*3+T1+32)& CHAR(U1*27+V1*9+W1*3+X1+32)& CHAR(Y1*27+Z1*9+AA1*3+AB1+32)& CHAR(AC1*27+AD1*9+AE1*3+AF1+32) (all one formula - I've split it to avoid awkward line-breaks). This assumes that your data occupies columns A to AF (32 of them) and starts on row 1. This will give you 8 characters (strange-looking combinations), and you can now sort all 33 columns using AG as the key field. Once sorted, you can enter this formula in cell AH2: =IF(AG2=AG1,"duplicate","unique") and copy down. Filter this column for "duplicate", then highlight all the rows that are visible and Edit | Delete Row. Select All in the filter pull-down and delete columns AG and AH to leave you with unique patterns of your data. Hope this helps. Pete On Mar 1, 3:52 pm, "Pete_UK" wrote: If you just had 1's and 0's you could treat each row as a binary number and combine the digits with powers of 2 to form a number. As you have 2's as well, though, then you would have to treat each row as a tertiary number (is that the right term? ternary? base-3?) and use powers of 3 to convert the 32 digits into an equivalent number. A UDF could do this for you. However, if you did this for a 32-digit base-3 number you would lose some precision, as Excel can only handle 15 digits accurately. So, it might be better to split the number and to deal with, say, 11 digits at a time (10 digits for the last group), such that you will then end up with 3 numbers per row in extra columns. You could then sort the block of data (plus the three extra columns) using the three columns as keys. A variation of this is to take 4 digits at a time in your UDF (81 possible values) and to convert this into a single ASCI character and then to concatenate the 8 characters together - these can then be sorted. It is then relatively easy to identify duplicates - either in the same sheet with a formula like: =IF(A2=A1,"dupl","unique") entered on row 2 and copied down, or you might make use of Advanced Filter by copying the derived numbers/text to a separate sheet and filtering to exclude duplicates. As for the "right solution", your question implies that there is only one, but there is usually a variety of ways of achieving something in Excel - this is just one way. Hope this helps. Pete On Mar 1, 3:21 pm, wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Try this:
With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
There must be a difference in the data you have versus what I copied from
your post. In my testing all duplicates were flagged as such. If you find and correct the data anomaly, try this modified formula: AJ1: =SUMPRODUCT(($AG$1:$AG1=$AG1)*($AH$1:$AH1=AH1)*($A I$1:$AI1=AI1)) When copied down, the formula returns "OK" for the first occurrence of a pattern and "DUPE" for all duplicates. Here are my first 18 calculated values: 111,111,111,111,111 111,111,111,111,111 10 OK 111,111,110,111,101 111,111,111,011,111 10 OK 111,111,111,011,011 111,111,111,101,111 10 OK 1,111,111,122,111 111,111,111,112,211 1 OK 111,111,111,111,111 111,111,111,111,111 11 OK 110,101,112,111,110 112,111,111,111,011 10 OK 111,010,111,211,111 21,111,111,110,111 10 OK 111,111,110,111,101 111,111,111,111,111 10 OK 111,111,111,011,011 111,111,111,111,111 10 OK 1,111,111,111,111 111,111,111,111,110 11 OK 1,110,012,111,111 111,210,110,011,111 1 OK 1,101,101,211,111 111,121,001,101,111 1 OK 111,011,110,111,101 101,211,111,111,111 10 OK 110,111,111,011,011 110,121,111,111,111 10 OK 111,111,110,011,101 101,011,111,111,111 10 OK 111,111,110,011,011 110,101,111,111,111 10 OK 1,111,111,111,111 111,111,111,111,100 11 OK 1,111,111,111,111 111,111,111,111,100 11 Dupe Does that help? *********** Regards, Ron XL2002, WinXP " wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Hi Swamy,
sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Hi, I would like to send my file so that you can make changes and
present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Ok. It is:
pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
You may want to munge your email address (in the header), too.
Pete_UK wrote: Ok. It is: pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Thanks, Dave - I've thought about it, but my spam filter seems to do
its job. Just to report back on this: I applied my method to the file that Swamy sent to me and discovered that the COUNTIF formula was double-counting as it couldn't distinguish between upper case and lower case letters in the 8- character codes, and also it was treating the symbols "?" and "*" as wildcard characters. Consequently, I changed the formula to: =COUNT(IF(EXACT(Codes,AI1),1)) which was array-entered, and copied this down. This gave the correct total (982) compared with the number of records in the original file, so the method seems to work (for anyone else who's interested). Pete On Mar 2, 8:14 pm, Dave Peterson wrote: You may want to munge your email address (in the header), too. Pete_UK wrote: Ok. It is: pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Completely off-topic.
But if you don't munge it in the header, you're not buying much by doing it in the body with this: pashurst then the <at thingy, followed by auditel.net Pete_UK wrote: Thanks, Dave - I've thought about it, but my spam filter seems to do its job. Just to report back on this: I applied my method to the file that Swamy sent to me and discovered that the COUNTIF formula was double-counting as it couldn't distinguish between upper case and lower case letters in the 8- character codes, and also it was treating the symbols "?" and "*" as wildcard characters. Consequently, I changed the formula to: =COUNT(IF(EXACT(Codes,AI1),1)) which was array-entered, and copied this down. This gave the correct total (982) compared with the number of records in the original file, so the method seems to work (for anyone else who's interested). Pete On Mar 2, 8:14 pm, Dave Peterson wrote: You may want to munge your email address (in the header), too. Pete_UK wrote: Ok. It is: pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
I don't mind people emailing me directly, and many do - presumably
Swamy couldn't read my address or didn't know how to email direct (or maybe he was being polite by asking first). I've noticed in other posts that a full email address is often disguised like and I didn't know if this is done by the newsgroups - if it is, then by putting my proper address in the posting, Swamy still wouldn't be able to see what it was. Well, that was my logic, anyway !! <bg Pete On Mar 2, 10:24 pm, Dave Peterson wrote: Completely off-topic. But if you don't munge it in the header, you're not buying much by doing it in the body with this: pashurst then the <at thingy, followed by auditel.net Pete_UK wrote: Thanks, Dave - I've thought about it, but my spam filter seems to do its job. Just to report back on this: I applied my method to the file that Swamy sent to me and discovered that the COUNTIF formula was double-counting as it couldn't distinguish between upper case and lower case letters in the 8- character codes, and also it was treating the symbols "?" and "*" as wildcard characters. Consequently, I changed the formula to: =COUNT(IF(EXACT(Codes,AI1),1)) which was array-entered, and copied this down. This gave the correct total (982) compared with the number of records in the original file, so the method seems to work (for anyone else who's interested). Pete On Mar 2, 8:14 pm, Dave Peterson wrote: You may want to munge your email address (in the header), too. Pete_UK wrote: Ok. It is: pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
I getcha.
Google does a nice job trying to protect email addresses. (I could see your email address in my newsreader with no problems. I just figured everyone used the same interface as me <bg.) (And the OP was using google, too.) In the words of Emily Litella: Never mind. Just in case... http://en.wikipedia.org/wiki/Emily_Litella Pete_UK wrote: I don't mind people emailing me directly, and many do - presumably Swamy couldn't read my address or didn't know how to email direct (or maybe he was being polite by asking first). I've noticed in other posts that a full email address is often disguised like and I didn't know if this is done by the newsgroups - if it is, then by putting my proper address in the posting, Swamy still wouldn't be able to see what it was. Well, that was my logic, anyway !! <bg Pete On Mar 2, 10:24 pm, Dave Peterson wrote: Completely off-topic. But if you don't munge it in the header, you're not buying much by doing it in the body with this: pashurst then the <at thingy, followed by auditel.net Pete_UK wrote: Thanks, Dave - I've thought about it, but my spam filter seems to do its job. Just to report back on this: I applied my method to the file that Swamy sent to me and discovered that the COUNTIF formula was double-counting as it couldn't distinguish between upper case and lower case letters in the 8- character codes, and also it was treating the symbols "?" and "*" as wildcard characters. Consequently, I changed the formula to: =COUNT(IF(EXACT(Codes,AI1),1)) which was array-entered, and copied this down. This gave the correct total (982) compared with the number of records in the original file, so the method seems to work (for anyone else who's interested). Pete On Mar 2, 8:14 pm, Dave Peterson wrote: You may want to munge your email address (in the header), too. Pete_UK wrote: Ok. It is: pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
Well, actually I can find out the email address as Google asks me to
enter some characters to view email address but I had this experience before as I emailed 2 or 3 people and I got back my email saying that my message failed to reach the recipient. So I make it a point to ask people here before I send them an email. Thanks a lot Pete for doing the great work on the excel file. Swamy On Mar 2, 6:09 pm, "Pete_UK" wrote: I don't mind people emailing me directly, and many do - presumably Swamy couldn't read my address or didn't know how to email direct (or maybe he was being polite by asking first). I've noticed in other posts that a full email address is often disguised like and I didn't know if this is done by the newsgroups - if it is, then by putting my proper address in the posting, Swamy still wouldn't be able to see what it was. Well, that was my logic, anyway !! <bg Pete On Mar 2, 10:24 pm, Dave Peterson wrote: Completely off-topic. But if you don't munge it in the header, you're not buying much by doing it in the body with this: pashurst then the <at thingy, followed by auditel.net Pete_UK wrote: Thanks, Dave - I've thought about it, but my spam filter seems to do its job. Just to report back on this: I applied my method to the file that Swamy sent to me and discovered that the COUNTIF formula was double-counting as it couldn't distinguish between upper case and lower case letters in the 8- character codes, and also it was treating the symbols "?" and "*" as wildcard characters. Consequently, I changed the formula to: =COUNT(IF(EXACT(Codes,AI1),1)) which was array-entered, and copied this down. This gave the correct total (982) compared with the number of records in the original file, so the method seems to work (for anyone else who's interested). Pete On Mar 2, 8:14 pm, Dave Peterson wrote: You may want to munge your email address (in the header), too. Pete_UK wrote: Ok. It is: pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 00111111111111111111111111110011 00111111111111111111111111110011 The last two rows matches. Therefore, I want it to be counted and sorted. Thanks Swamy Is there a right solution for this problem- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting patters
You're welcome - thanks for feeding back.
Pete On Mar 2, 11:47 pm, wrote: Well, actually I can find out the email address as Google asks me to enter some characters to view email address but I had this experience before as I emailed 2 or 3 people and I got back my email saying that my message failed to reach the recipient. So I make it a point to ask people here before I send them an email. Thanks a lot Pete for doing the great work on the excel file. Swamy On Mar 2, 6:09 pm, "Pete_UK" wrote: I don't mind people emailing me directly, and many do - presumably Swamy couldn't read my address or didn't know how to email direct (or maybe he was being polite by asking first). I've noticed in other posts that a full email address is often disguised like and I didn't know if this is done by the newsgroups - if it is, then by putting my proper address in the posting, Swamy still wouldn't be able to see what it was. Well, that was my logic, anyway !! <bg Pete On Mar 2, 10:24 pm, Dave Peterson wrote: Completely off-topic. But if you don't munge it in the header, you're not buying much by doing it in the body with this: pashurst then the <at thingy, followed by auditel.net Pete_UK wrote: Thanks, Dave - I've thought about it, but my spam filter seems to do its job. Just to report back on this: I applied my method to the file that Swamy sent to me and discovered that the COUNTIF formula was double-counting as it couldn't distinguish between upper case and lower case letters in the 8- character codes, and also it was treating the symbols "?" and "*" as wildcard characters. Consequently, I changed the formula to: =COUNT(IF(EXACT(Codes,AI1),1)) which was array-entered, and copied this down. This gave the correct total (982) compared with the number of records in the original file, so the method seems to work (for anyone else who's interested). Pete On Mar 2, 8:14 pm, Dave Peterson wrote: You may want to munge your email address (in the header), too. Pete_UK wrote: Ok. It is: pashurst then the <at thingy, followed by auditel.net change the obvious. Pete On Mar 2, 6:00 pm, wrote: Hi, I would like to send my file so that you can make changes and present me your solution. I would like to know your e-mail address so that I can send you my file. Thanks Swamy On Mar 1, 8:23 pm, "Pete_UK" wrote: Hi Swamy, sorry, I missed the bit in your post that said you want to know the number of duplicates - I concentrated on the part that said you want to get rid of the duplicates. If you do have 0, 1 and X as values, then you can copy the data to a second sheet (or just copy the sheet by CTRL-drag of the sheet tab) and then work on the copy. Highlight all the data in the 32 columns and use Find & Replace (CTRL-H) to search for X and replace it with 2 - then my long formula will work for this copy of your data, and you will still have the original data. Before sorting, though, fix the values in column AG by highlighting all the cells with the formula in and clicking <copy, then Edit | Paste Special | Values (check) | OK then <Enter. Then copy these fixed values back to the original sheet, which will be in the same order, in column AG. It will also help to give a name to this range of codes in the first sheet - highlight the codes in the original sheet and Insert | Name | Define and call it something like "Codes". Now you can sort the data in the second sheet as I described above, and by using the formula in column AH you can reduce the list of codes to uniques. Then in cell AH1 of the second sheet (to overwrite then IF formula which is already there) you can enter this formula: =COUNTIF(Codes,AG1) and copy this formula down. Now in your second sheet you will have a complete set of unique patterns (you can apply Find and Replace again on the first 32 columns to change the 2's back to X's if you wish), and in column AH you will have a count of the number of each of them in the first sheet. I think this gives you everything that you asked for. By the way, UDF stands for User-Defined Function. You can define your own functions in Excel and then use them in a worksheet like any other functions, eg =my_function(A1:AF1), but I did point out in my second post that you could achieve your outcome without using one. Hope this helps further. Pete On Mar 1, 8:49 pm, wrote: Ron, Thank your for your help. I did exactly as you told me and the number of pattern occurences is not properly counted. When I see a repeat in the pattern, it displays 0 for both of them. Moreover, I have about 1000 rows of data and it would be hectic to for me to each and every single row and delete data from three cells as you described. I would like to have one column cells to bring the numbers together and the other column cells to count the occurences. This would be a quick check. Also this occurences is an important number because I need to know how many patterns of this type are there. Pete, You are right about performing binary conversion on the 0's and 1's. I forgot to mention that the number 2 that you see was actually "X" because I was trying to manipulate the text as a number. Basically, there are 32 sensors that reads a part and if the part covers the sensors, it displays a 1, if the part has some holes in it and uncover the sensors, it displays a 0 and if the part partially blocks the sensor, it displays an "X". I tried your solution which you posted in your second post and it does give me weird characters and I would like to know how many times it is occuring. So i used the countif statement and i tested it. It returns me a wrong number for some patterns. You were referring to UDF in your first post. What is UDF? Thanks for your help Swamy On Mar 1, 12:35 pm, Ron Coderre wrote: Try this: With your posted numeric table in cells A1:AF18 Then AG1: =--(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) AH1: =--(P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1) AI1: =--(AE1&AF1) Copy those formulas down through row 18. Next AJ1: =SUMPRODUCT(($AG$1:$AG$18=AG1)*($AH$1:$AH$18=AH1)* ($AI$1:$AI$18=AI1)) copy that formuls down through row 18 (That formula counts the occurrences of each pattern.) Also, you can sort the patterns numericly by selecting A1:J18 and sorting on Col_AG......ascending Col_AH......ascending Col_AI......ascending Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, I have 32 columns which consists of 0's, 1's and 2's and i have many rows that also consists of the same numbers but in a different order. Now what I want to do is to count how many rows consists of the same exact pattern of 0, 1 and 2. I tried using CONCATENATE function to bring all the info from the 32 columns together and used countif statement to count how many times this pattern is occuring. But this doesnt seem right. I also wanted to sort these numbers so that I can bring these patterns together and eliminate the repeated ones. But the sort function is reading this concatenated number as text. How do i convert this text string into a number? A sample data: 11111111111111111111111111111110 11111111011110111111111101111110 11111111101101111111111110111110 00111111112211111111111111221101 11111111111111111111111111111111 11010111211111011211111111101110 11101011121111102111111111011110 11111111011110111111111111111110 11111111101101111111111111111110 00111111111111111111111111111011 00111001211111111121011001111101 00110110121111111112100110111101 11101111011110110121111111111110 11011111101101111012111111111110 11111111001110110101111111111110 11111111001101111010111111111110 ... read more »- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Grouping patters of numbers in cell | New Users to Excel | |||
| counting if | Excel Worksheet Functions | |||
| Detecting patters in data | Excel Worksheet Functions | |||
| Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
| Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Linear Mode
