Macro To Sort 100 000 Rows

Sep 21, 2008

I've got up to 150000 rows in excel (2007) and i need to sort the data into 2 sheets.
Sheet1 contains all the data. Sample:

1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409
2000999999027020300189640900000000000005740200070063

As you can see, the first row starts with 1000 and second with 2000. That would be the criteria. All other rows goes exactly the same.

I need macro that copies all the rows starting with 1000 onto sheet2 and all the rows starting with 2000 onto sheet3.

Filtering and copy/paste won't help, because the list is toooooo big. Thatswhy only hope is to find macro that loops threw the list.

View 14 Replies


ADVERTISEMENT

Macro To Sort The Rows

Aug 24, 2009

I have a spreadsheet that I would like to create a sort macro for. The number of rows will be variable but will always start in the same row. (i.e. ... it might be from A2:E200, A2:E300, A2:244. etc.) Some of the data fields will be blank.

How can I assure I am always getting the correct data selected to sort. I generally create my macros by recording and then sometimes tweaking them manually when necessary but I really don't know VB code very well. When I record a macro using the end/down and end/right combination, I get this
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

This may or may not always work depending upon how many blanks I have and where they are located. Here is a simple example of what my sheet may look like. Note column F is blank and column G has formulas that looks at the cells A:E in that particular row for evaluation. The sort only needs to go to row E although if it went further, I guess it wouldn't matter. I'm sure this is an easy problem to resolve but I just don't know what code to use.

******** ******************** ************************************************************************>Microsoft Excel - sort example.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFG1FIELD 1FIELD 2FIELD 3FIELD 4FIELD 5 FIELD 62AAA     FORMULA3SSS11121010 FORMULA4DDD     FORMULA5FFF 12   FORMULA6GGG26  9 FORMULA7HHH  7  FORMULA8JJJ 8   FORMULA9KKK14  8 FORMULA10LLL 16   FORMULASheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Macro To Delete Rows And Sort

Sep 27, 2009

I have attached a worksheet to illustrate a "Before" (raw data) and "After" (desired results).

Each row contains data on a forex trade. The variables which must be assessed in each row are:

Currency Pair (e.g. GBP/USD).
Opening Time
Closing Time

What I need to do is delete many rows in the raw data because they reflect a condition I do not want in my analysis, which is having multiple open trades of a single currency pair during the same time period.

To perform my analysis I need to delete all trades (rows) which meet the above condition.

The worksheet provides examples, along with a rudimentary tool I've used to help me sort/delete rows manually.

Since I am looking at thousands of trades, a manual sort/delete is impractical. (my eyes don't like this kind of work!)

The rudimentary tool I've used in manual sorts/deletes is simply to have a column which returns a "1" if a trade opens before an older trade has closed. I do this by first sorting the data by (a) currency pair then (b) date opened. Then I must manually go through multiple iterations of deleting trades until there all of the "1's" have disappeared.

I'm hoping someone can show me a macro which might do this sorting/testing/deleting automatically, at least to the point where all I have to do is repeatedly press a "macro" button until there are no "1's"

View 8 Replies View Related

Macro Sort Rows In Multiple Sheets

Jan 31, 2014

Sorting.xlsx

I am trying to figure out how to make a macro that can sort some numbers from Largest to Smallest within a specific range on multiple sheets. The range is only within column D starting with cell D11: (until the data ends) on all the sheets in my workbook (the number of sheets may change with time) except for Sheets: "A", "B" and "C".

In other words I want the sort to work on all sheets except the first 3 sheets which are named Sheet A, Sheet B, and Sheet C.

I have attached a spreadsheet for an example of what I am saying.

View 5 Replies View Related

Macro To Sort Multiple Rows Of Data

Sep 16, 2008

I have a report that I drop down into Excel that is a transaction report for securities. The data for each security is in two rows. I would like to write a macro that would sort each two-line group by a certain cell in the group. If I could make the macro request the number of rows and columns in each range and the cell address of the cell to sort by, I would be able to use this macro for many different reports. Also, there has to be some way of telling the macro where to begin and where to end. I have some experience with macros although generally I "Frankenstein".

View 9 Replies View Related

Macro To Sort Rows By Date Order

Feb 21, 2007

Starting at E8 and going down the E column there is a list of non sequential dates that could potentially be very small or extremely large. These dates must be sorted in ascending order together with all the data in their respective rows so for example

John Ball 20/2/07
Mark Dunn 19/2/07
Tim Jones 18/2/07

should be sorted as

Tim Jones 18/2/07
Mark Dunn 19/2/07
John Ball 20/2/07

If anyone can write such a piece of code it'd be very time saving.

View 5 Replies View Related

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View 3 Replies View Related

Macro To Sort Colums And Protect After Sort

Nov 5, 2009

I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.

If possible i would also like a macro to protect this range after the sort,

View 3 Replies View Related

Sort Multiple Rows Without Affecting Others Rows

Aug 10, 2006

I want to sort each row in an excel sheet so all calls are in alphabetical order without affecting any other row. I need to do this for about 500 rows. Is there any quick way of doing this without having to sort each row individually.

View 3 Replies View Related

Sort Without Using The Top 2 Rows

Dec 16, 2009

I have a sheet that has some hidden calculations in the top 2 rows. My actual headers start in row 3 (the amount of data always changes so I chose to put the hidden constants at the top rather than the bottom). I want to sort by the headers in row 3 but I can't figure out how.

View 2 Replies View Related

Macro Coding: Add A Code To The Sort And Paste Macro That Will Open The Second Spread Sheet

Jul 21, 2007

I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the

Windows("estimate sheet one.xls").Activate

View 2 Replies View Related

Sort The Rows Of Data

Apr 10, 2009

We have an issue with sorting rows of data that have formulas in them.

---------------
These formulas are using data from a row which is above the column headers.

For example cell H2 has 160 manually entered in it. It's the number of work hours available in January 2009.

The column header for that column is in Q4 and has a value of 'Jan'

Below this in cell H5 is the formula '=+H2' because the resource in this row is available for 100% of the month.

Another resource in row 22 has the formula '=+H2*0.15' in cell H22 because they are only available 15% of the time that month.

---------------
The problem occurs when we try to sort this data. Most of the cells displays #REF!. Some show a value, but the calculation is wrong.

View 8 Replies View Related

Sort Rows According To A Variable

Dec 7, 2008

I have the following code which sorts rows accoridng to a variable retrieved from a work sheet. When I plug in "SPY" (name of one of the variables in column B), it works fine, but with variable stock_symbol, I'm given an error for some reason.

View 8 Replies View Related

Keeping Rows Together For Sort?

Sep 20, 2012

I have dumped main frame data into excel. It is customer info that is in three rows of data each. I need to know if its possible to group or somehow keep three rows of data together for multiple sorts. The first row is the customer name, the second row is their first line of address, the third row is the city, state and zip. I need to add columns to this dumped data, but I will need to do a number of sorts for different views of the data. But as I've stated every 3 rows go together. I've tried different ways to group (for lack of a better word) the three rows, but nothing I've tried works. I've also done multiple searches, but either I'm not searching on the right word or this can't be done.

View 9 Replies View Related

Sort Rows Into Another Sheet?

Jan 4, 2013

I am trying to 'sort' rows of data into another sheet in the same excel file. For instance, if i have a row that contains the string "SDMH" then it should be sorted to "Sheet2". In the original sheet, the data does not need to be removed, just copied.

View 4 Replies View Related

Trying To Sort Groups Of Rows

Dec 31, 2007

Range("C"&((h-1)*6+2)"G"&((h-1)*6+7)).Select

What is the proper name convention if I want to sort a group of 6 rows at a time. For example if h =1 then I want to select from C2:G7 to sort but I am getting an error when I try to input it as above. I have the sorting code, just need it selected first.

Sorry, I don't know how to turn off smiley faces because the options list isn't available to me, but before the G is a : and a (

Trying to make it Range("C2:G7").Select

View 9 Replies View Related

Sort Rows Without Sorting

May 18, 2009

I have data being fed from an application where it's difficult to sort the rows into ascending order. So is there a way that VBA can sort them and produce a value according to the order?

For example, Column A contains 5 rows.
Row 1's value is 4
Row 2's value is 1
Row 3's value is 2
Row 4's value is 3
Row 5's value is 5

If I were to sort these into ascending order, I'd get 1, 2, 3, 4, 5 in Column A. But I can't sort the rows so I need code to tell me which is the lowest number (1), which is the second lowest number (2), etc.

The purpose of the code will be to put values in Column B according to what's found in Column A. For the the lowest number, 1, the value in the cell next to it in Column B would be "a". For number 2, it would be "b", etc.

View 9 Replies View Related

Sort And Hide Rows As Typed?

May 3, 2014

Is there a way to imitate the search box in access where it highlights any matching text in the entire database. BUT, instead of hitting the next arrow to go to the next matching thing, I want it to simply hide all rows that contain nothing that matches. I don't want it to just hide them though, I want them to be shown from the topmost cell down rather than simply hiding cells and having a ton of gaps to look through. If this can be done in access then that will work as well.

View 1 Replies View Related

Sort Data Into Multiple Rows

Dec 2, 2008

I'm trying to merge 2 spreadsheets togeather but befor I can do this I need to resolve the following issue - my Excel worksheet has 2 columns with data that looks like this

Value1X1,X2,X5
I need it to change to this
Value1X1
Value1X2
Value1X5

View 5 Replies View Related

How To Randomly Sort Rows Of Data

Dec 5, 2012

I have three columns of data that I want to randomly sort by row. It's important that each of the three adjacent cells in each row move together, i.e.

Before assortment:

A1 B1 C1
A2 B2 C2
A3 B3 C3

After random assortment:

A2 B2 C2
A1 B1 C1
A3 B3 C3

View 2 Replies View Related

Sort Data In Grouped Rows

May 22, 2013

I have a workbook (Sort Test.xlsx) where multiple rows belong to the same "group". Is there a way to sort by a value (e.g. name of the test, date of the test, etc.) while maintaining the formatting and keeping the "groups" together? There are 3 sheets in the work book. The first shows the sheet as is, the other two are examples of how I would like to be able to sort the data.

View 2 Replies View Related

How To Sort Table After Change Of Rows

Oct 2, 2013

How to autosort table after a row change.

View 1 Replies View Related

Sort All Rows Individually With Data Containing BML

Oct 8, 2013

Any way to sort each row individually to move all data starting with letters "BML-" to first three columns? I have a document with data all over spreadsheet multiple rows and columns. But would like to move all cells that start with these characters "BML-" into first second and third column. I have been trying to accomplish it for over two weeks now without any success.

View 7 Replies View Related

Sort Data In Rows By Colour

Aug 13, 2012

I have a big database, each row is having it's colour. How can I sort by colour? I mean, data with one colour comes first, then data with another colour comes after etc.

View 1 Replies View Related

Sort Rows Depending On Value In Only One Of Columns

Aug 16, 2012

I wish to sort all of my rows depending on the value in only one of the columns. I do not know how to set this up, my data starts in row 7 and is in columns B:F, needs to be sorted by descending in column B.

View 1 Replies View Related

VBA To Select Rows And Sort Contents

Dec 21, 2008

I'm trying to sort a whole list of data that's been separated into blocks with a row containing the value "a" like:

row 9 a
row 10 455
row 11 8892
row 12 34
row 13 a
row 14 578
row 15 92
row 16 a

etc..

I want to sort each block into ascending order using VBA code and then add in some extra code to do some more analysis.

The data to be sorted is in Column D, and there are also values in Column C and B that need to be sorted with the Column D values (so that they stay in the same rows).

View 9 Replies View Related

Sort Unknown Number Of Rows

Sep 30, 2009

I am working with 2 workbooks the first one "LH Enrollment" is used to enter information for new students being enrolled in the program. After the information is entered the macro is then copying the information to the second workbook "LH Children Records". The part I'm stuck on is that I now need to sort alphabetically by last name which is entered on Sheet 1, Column B of "LH Children Records". The number of columns will stay the same but the number of rows will change each time a new child is added. Eventually all this information gets copied over to several other workbooks, but I'm stuck trying to sort. I have attached both workbooks

View 5 Replies View Related

Sort Data With Two Header Rows

Apr 30, 2008

I have two header rows on a sheet that I need to sort starting on Row 3, Column A.

The sheet is A-K and rows 1 and 2 are headers

Row 1 has merged cells of A-F which says "Information" and then G-K " Tracking Section"
Row 2 is broken up into individual sub-headers in columns, A-K each one having a bit more info for instance:

Under the "Information" merged header in row 1 there is
A2 Name
B2 Work Center
C2 C/O date
D2 Due CC
E2 Due MX
F2 Date Received

and G-K in row 2 has different sub-headings.

I have tried to use my normal code, which is great for one header, but it ends up sorting the sub-header as I call it...

View 5 Replies View Related

Excel 2010 :: Sort Rows By Column

Sep 25, 2013

I am looking for some to create a simple macro to sort multiple rows in ascending order based on the values in a particular column in the selected range. I want to sort A4:AI1004 in ascending order by column D. I recorded the following macro in Excel 2010:

VB:

Sub sort()
'
' sort Macro
' sort by column D (Sequence #)
'
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select

[Code]...

First of all, I think this code can probably be simplified. Secondly, it does not work in older versions of Excel. In 2007 it always ends in a runtime error. How can I clean this up so that it will work in both versions of Excel?

View 3 Replies View Related

Sort Groups Of Data And Maintain Rows?

Jan 3, 2014

I need to sort groups of data by a single cell in the group, compared to other single cells in other groups of data. The groups are always the same number of rows but vary in the number of rows. There is an example file below. The red is the company name and the green is what I need to sort the group by, as compared to the other groups. This example only has a few, the real file has thousands of groups. I want to sort the companies by the totals in green...they are currently in alphabetical order. There must be a way to do this without going the VBA option.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved