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

```
 0
bsears (2)
1/25/2005 6:25:03 PM
excel.misc 78881 articles. 5 followers.

4 Replies
665 Views

Similar Articles

[PageSpeed] 34

```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?
>
```
 0
1/25/2005 6:39:03 PM
```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?
> >
```
 0
bsears (2)
1/25/2005 6:57:06 PM
```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?
>> >

```
 0
anonymous (74722)
1/25/2005 7:53:06 PM
```Data>Filter>Advanced Filter.

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

For details on this see Debra Dalgleish's site.

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

```
 0
Gord
1/25/2005 8:58:56 PM

Similar Artilces:

Public Contact List and E-Mail Errors
I am using Exchange Server 2003 When I right click on a contact in my Public Folder's Contact Folder, I click on "New Message to Contact" and it tells me: Error creating new mail! This may be caused due to an incorrect e-mail address for a contact or an invalid name for a distribution list Furthermore, when I type a name in the To field of an e-mail, it doesn't not go to autocomplete any name in the Public Folder's Contact Folder. It only autocompletes Users that are setup under Active Directory. (This is with "Show Names from the:" option set to &quo...

sorting order
How could I define a series of results in a sheet if I wanted to do like a first place second place third place and so on from data with names in one column and data in the next column. I am not wanting to use the sort function to move the data around I merely want a column, say to the left of the names, stating their position in the table. Using the list below I would have Stan as 1, Sue and Bill as 2, Steve as 4 and so on. Tom 1 Bill 10 Kev 8 Andy 4 Fred 5 Stan 16 Rob 7 Eddy 8 Steve 9 Sue 10 Is this at all possible or am I using a hard rout...

End-User-Recovery with the Data Protection Manager Client
I have DPM 2010 Release Candidate running on a Windows 2008 Server. I will use DPM for protecting user data from Notebooks. In my test enviroment I have two Clients with Windows XP and Windows 7. The Data Protection Manager Client is installed on both PC's by the administrator console of DPM. Protection Groups are arranged for Windows XP and Windows 7. The recovery points are running at the scheduled time. Now my question (problem): When I want to restore a previous version of a file on the Client PC with the Data Protection Manager Client no previous versions are shown... ...

best way to create a list of stringpairs?
I want to create a list of String pairs. I was thinking CMapStringToString would not work since I will have non-unique keys. For example: typedef CPair<CString,CString> CStringPair; CList<CStringPair> myStringPairList; CStringPair cp; //then add cp to myStringPairList; except CPair<CString,CString> does not work. How would I do that? Thanks! class CStringPair { public: CString a; CString b; }; The documentation of CPair is pretty useless in figuring out what it will accept. So why bother, given how trivial it is to replace it with the above decl...

Hello All, For all of our users in the company we want to change the first address list in the Outlook 2000 address book, but because it is for more then 100 users we want to change it at once for everyone by login-script/registry/administrative template or whatever. We want to change it because from a lot of sister-company's also the address lists are in the address book and when a user want to search someone in the (local) company it is searching the global address list. Normally you should change it like this: "ctrl-shift-B", change "show this address list first". B...

List Control with flashing rows
Has anyone done anything with a list control and flashing? I need to make my list control flash the row when an update has been performed and when they click on the row stop the flashing. The flashing needs to be independant for each row - meaning row 1 might be flashing but row 2 not. I found notes in codguru (or maybe code project - can't recall) for flashing a field label. My fear is if I put in a timer and tell it to flash the rows that all of the cpu cycles will get stuck there and the list control will not respond when I need it to. Any thoughts or suggestions? Unfortunately I ...

Is there anyway to move a trendline behind the data series?
Hi, I'm using XL2002 with Windows 2000. I have a chart that I added a trendline to, but it obscures part of my data series, is there a way to move it behind the data series? If so, how? I can't figure it out. Thanks! Norm nOrM - You can use a thinner line, which would cut down on the obscurity of the data. Or you could get the parameters of the curve fit, either from LINEST (see Bernard Liengme's site for assistance if needed), or by extracting the coefficients from the trendline equation (see Dave Braden's technique). Use these parameters to determine X-Y data for ...

SendInput lose data
Hello everybody, I'm writing an "Word-macro"-like application. I would like to allow a user to store a sequence of key presses and mouse movements and to send them on command to a specified software. I need to send mouse movements too because I want to be able to draw on graphical area of applications like Paint or Photoshop. My program uses SendInput to transmit key presses and mouse movements to Windows. Unfortunately, I'm loosing some of my data. For sample, I send "Middle Button Down", then "Mouse Move", then "Middle Button Up". >From ...

How to sort my mail as Gmail does?
How to sort my email in 2003 as Gmail does? (They are grouped into 'conversations' so that all follow-ups and responses appear together in my inbox.) It is not the simple sort by subject by alphabet. thanks. Click on the Arrange by header in a view and choose 'Conversation' to get the closest approximation "Enchanter" <ensoul.magazine@gmail.com> wrote in message news:1174373837.332802.281480@o5g2000hsb.googlegroups.com... > How to sort my email in 2003 as Gmail does? (They are grouped into > 'conversations' so that all follow-ups and response...

sorting #23
I have a spreadsheet that I did not create. Every time I sort it the row heights change back to "auto." Anybody know what I can do to make that stop happening? ...

Invoicing with Multiple Bins
Does the invoicing module work with multiple bins enabled ? A client of ours is receiving an error message when trying to enter invoices: "You can't create or post invoices with the multiple bin functionality enabled" Any ideas? If using mulit bins you'll have to use Sales Transaction Entry for Invoicing. The Invoice Entry window does not provide for selection of the bin, only a Site. Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www.gp2themax.blogspot.com Thank you, Frank. I had to figure this out the hard wa...

Contact list problem:Is it possible to use 'local' e-mail address that links to an external e-mail ?
I need to send alerts from MOM server to my cell@mobile.att.net. If try to enter this address on MOM directly, it doesn't work because it is an external address. Then I created a contact on the Exchange server. Alias is EmailAdmins. SMTP:cell@mobile.att.net. The only information I have on my local domain is my EmailAdmins (alias). If I enter cell@mobile.att.net that is still an external address. The problem here is that MOM app would not resolve the *alias* to the e-mail address on my contact list ( cell@mobile.att.net.) Any input on how I can establish a local e-mail address on my cont...

Any thoughts? Application error .data not place into memory because of an I/O error
Folks The following problem has been reported twice on two separate systems at one particular client The instruction at 0x7344de72 referenced memory at 004bac40. The required data was not placed into memory because of an I/O error status of 0000000c4 Given that the VB6 exe resides on the server I'm thinking this is a network corrupt packet issue of some sort given the IO error. Or maybe bad RAM. There are a total of three hits for "data not placed into memory because of an I/O error". So this is exceedingly rare. Tony -- Tony Toews, Microsoft Access M...

\wwwroot\bin\Microsoft.CRM.Proxy.dll
This is a multi-part message in MIME format. ------=_NextPart_000_014B_01C418A0.1A6272B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am not sure if I am using the right dll file. In the path it says=20 Microsoft.CRM.Proxy.dll but in the CD there is=20 Microsoft.CRM.Platform.Proxy.dll Which is the right one? ------=_NextPart_000_014B_01C418A0.1A6272B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">...

sorting worksheets
Hallo In several Excel files I would like to sort the worksheets by name. In some files there are almost 100 worksheets and I would like to cut them up into smaller pieces/files. Sorted workshets makes the task easier Hope somebody knows what to do!? Thanks Hein -- Hein ------------------------------------------------------------------------ Hein's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24744 View this thread: http://www.excelforum.com/showthread.php?threadid=495168 Hein, have a look here at Chip's site for a way to do it, http://www.cpearson...

Title listing...
Hi! Is there a way to list the spreadsheets which were a certain cell contains a certain value? f.ex. List the spreadsheet name if cell D4 contains 2004. This should result in a list where all spreadsheets that have the value 2004 in D4. Is this possible? -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- Hi Zadig, Try: '=======================>> Public Sub Tester05() Dim WB As Workbook Dim SH As Worksheet Dim ws As Worksheet Dim i As Long Const sStr As String = "MyList" Const strControlValue As String = "2004" ...

how do I set up a excel spreadsheet to auto sort data in order
I have a spreadsheet linked to a count which will be changing as numbers are added in. I wish to have the source data automatically update the list and sort that list in descending order with names attached to the figures that are changing. ...

Sorting Alpha Numeric
I have a list of alpha numerics I wish to sort but they are coming up as the Excel 'default'. I prefer the desired result. Any thoughts? Thanks Original Sorted Desired D10 D07 D07 D09 D09 D09 D07 D10 D10 D11 D105 D11 D12 D11 D12 D20 D117 D20 D117 D12 D105 D105 D20 D117 your data isn't sorting properly because it is type text, which always sorts alphabetically left->right (so it won't distinguish between a 1 in the tens or hundreds place). I have two suggestions: 1. If...

conditional data label
I have a chart that calculates control limits for electrical test data. I dont want all the data to have data labels turned on, just the ones that exceed the control limits. I was planning on experimenting with comparing the series objects of the data to that of the control limits and if the test is true turn on the dataLabel object. Has anyone done this? Is there a better way? Thanks in advance Write formulas in a range of cells to produce the conditional labels. Then use one of these utilities to apply the labels to the points. They link the labels to the cells, so when conditions (a...

edit drop down list
I have an excel 2003 worksheet that was designed by my predecessor. She used data validation to create a drop down list with a number of names. However we need to add a couple of extra names to the list and I can't seem to figure out how to edit the list. I could just erase the list and create a new one, I suppose, but I was hoping for an alternate route. Is there a way to edit this list and add the new names? Just click on the cell and: Data > Validation > Settings and the source field will tell you where the list is. You can then edit that list. -- Gary''s Student -...

Cell data prefixed with unknown character #2
Thanks for the quick response. Option 1 and 2 did not work. I used th macro and it's just what I needed. Thanks again -- ingine ----------------------------------------------------------------------- ingineu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1486 View this thread: http://www.excelforum.com/showthread.php?threadid=26491 ...