Bin and sort a large list of data

This works for data in one column, i have 3 columns of data that needs to be
organized, for example:
1 2 3
1 4 7
5 8 9
1 2 3
1 2 3
5 8 9

organize to give
1 2 3
1 4 7
5 8 9

Thanks, i'm in a bind, i have about 8000 rows of numbers i need to sort
though.
"David Jessop" wrote:

> Hi,
>
> Sort the data into the number order. Let's say that is in column A. Then
> in the first free column, make the first cell 0, and then do in row 2
> =IF (A1=A2,1,0)
> This will then equal zero on the first line of a new number.
>
> Then Copy, Paste Special, Values on this column of ones and zeros, and then
> sort on it. The data at the top will be a unique list of numbers. You can
> just then delete all the 1s.
>
> HTH,
>
> David Jessop
> "bsears" wrote:
>
> > I have a large list of data that i need to go through for my job. what i
> > want to do is organize the numbers into bins, then elimintate any numbers
> > that are identical. does anyone have any ideas?
> >

I assume you have headers in row 1.

You can use 2 "helper" columns on the right.

Put this formula in the first one, say D2:

=A2&" "&B2&" "&C2

and copy that down.

In E2 write this formula:

=COUNTIF(E$2:E2,E2)

and copy it down.

Then you can use Data/AutoFilter to show only rows with a 1 in column E. Copy
those rows to another location.

On Tue, 25 Jan 2005 10:57:06 -0800, bsears <bsears@discussions.microsoft.com>
wrote:

>This works for data in one column, i have 3 columns of data that needs to be
>organized, for example:
>1 2 3
>1 4 7
>5 8 9
>1 2 3
>1 2 3
>5 8 9
>
>organize to give
>1 2 3
>1 4 7
>5 8 9
>
>Thanks, i'm in a bind, i have about 8000 rows of numbers i need to sort
>though.
>"David Jessop" wrote:
>
>> Hi,
>>
>> Sort the data into the number order. Let's say that is in column A. Then
>> in the first free column, make the first cell 0, and then do in row 2
>> =IF (A1=A2,1,0)
>> This will then equal zero on the first line of a new number.
>>
>> Then Copy, Paste Special, Values on this column of ones and zeros, and then
>> sort on it. The data at the top will be a unique list of numbers. You can
>> just then delete all the 1s.
>>
>> HTH,
>>
>> David Jessop
>> "bsears" wrote:
>>
>> > I have a large list of data that i need to go through for my job. what i
>> > want to do is organize the numbers into bins, then elimintate any numbers
>> > that are identical. does anyone have any ideas?
>> >

Data>Filter>Advanced Filter.

Check "unique records only" and "copy" to another spot or sheet.

For details on this see Debra Dalgleish's site.

http://www.contextures.on.ca/xladvfilter01.html


Gord Dibben Excel MVP

On Tue, 25 Jan 2005 10:57:06 -0800, bsears <bsears@discussions.microsoft.com>
wrote:

>This works for data in one column, i have 3 columns of data that needs to be
>organized, for example:
>1 2 3
>1 4 7
>5 8 9
>1 2 3
>1 2 3
>5 8 9
>
>organize to give
>1 2 3
>1 4 7
>5 8 9
>
>Thanks, i'm in a bind, i have about 8000 rows of numbers i need to sort
>though.
>"David Jessop" wrote:
>
>> Hi,
>>
>> Sort the data into the number order. Let's say that is in column A. Then
>> in the first free column, make the first cell 0, and then do in row 2
>> =IF (A1=A2,1,0)
>> This will then equal zero on the first line of a new number.
>>
>> Then Copy, Paste Special, Values on this column of ones and zeros, and then
>> sort on it. The data at the top will be a unique list of numbers. You can
>> just then delete all the 1s.
>>
>> HTH,
>>
>> David Jessop
>> "bsears" wrote:
>>
>> > I have a large list of data that i need to go through for my job. what i
>> > want to do is organize the numbers into bins, then elimintate any numbers
>> > that are identical. does anyone have any ideas?
>> >