Re: 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" <pashu...@xxxxxxxxxxx> 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
*** 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 *** 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, mahadevan.sw...@xxxxxxxxx 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 -


.

Loading