Bin and sort a large list of data
 

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?

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?

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
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?

DataFilterAdvanced 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
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?