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 -