Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|