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
ADVERTISEMENT
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
Jan 14, 2010
I've been trying to rank by groups within one list of data; I can sort by two columns and rank with a simple "if" statement, but this is no longer practical for how often the data is updated and re-sorted. Trying to find a dynamic formula that creates ranks by the groups in a selected column. Example attached.
View 5 Replies
View Related
Oct 14, 2012
I've located the following color sort VBA that sorts worksheets by color and it is working well. I'm trying to modify the code to sort each of the "color grouped" sheets alphabetically. That is, sort by color and then each color group sorted alphabetically.
VB:
Sub SortWorksheetsByColor(Optional ByVal SortByAsc As Boolean = True)
Dim i As Long
Dim j As Long
Dim ShtC() As Long
Dim ShtN() As String
Dim t, n As Long
Dim lngSU As Long
[Code] .....
View 5 Replies
View Related
Oct 21, 2007
I am trying to find a way to sort a string of numbers within a cell in numerical order as they are entered,regardless in what order they are entered. Example 123,45,478,12 entered cell, and want it to sort in numerical order as it gets entered in the cell...12,45,123,478,4564
View 2 Replies
View Related
May 21, 2014
How do I create a macro to sort rows into groups, add break rows in between & insert a product sourced from another excel workbook onto the break line?
I receive a spreadsheet each day with a series of requests, most of the information is the addresses & names etc with the important data in columns K,M & O. Data in each of these columns is a code that refers to a product and information sheet to be sent out to the person requesting it. They may want just one in which case data will only be in column K, if they want two there will also be data in M and if they require three there will be data in column O as well. The bit I do know is how to sort the important data into groups of the same by the country (column I) and the product sku's.
Whilst I can do the sort as a recorded macro I really need it to be part of a larger macro that does the following bit I can't get my head around. I need to insert a break row above each group then by cross referencing the data in the product sku columns to a separate workbook insert the appropriate item / letter combination onto the break line row, telling the volunteer which items to send out. This is a v look up?
I have attached an example of the files we work with, the first tab is with the data simply sorted and the second is what I need it to look like. I normally receive it as a CSV file, there are around 8000 possible combinations of product and instructions and the files are normally around 4000 records long. At present we insert the break lines and copy/paste the information manually, seriously slowing down the process.
Orders Example EUR RM.xlsx
View 12 Replies
View Related
May 20, 2014
I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.
Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then
[Code]....
View 1 Replies
View Related
Feb 9, 2013
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Aug 1, 2014
My spreadsheet has rows with inspection numbers in column A, but there are varying numbers of rows associated with an inspection number. I'd like to change the color of the rows for every other inspection number to yellow using a formula. I think it has to involve an if/then statement but I can't figure out how to word the formula to just choose every other group of inspection numbers.
Example:
inspection numbers
date
description
[Code].....
View 5 Replies
View Related
Jan 18, 2014
I'm looking for a way to count groups of alternating rows of "TRUE" values across 3 columns. There will never be an occurrence of more than one "TRUE" value per row. In the sample below, alternating TRUE values occur in rows 4 through 7, so this would be counted with the groups of 4.
1Column 1Column 2Column 3
2FALSEFALSETRUE
3FALSEFALSETRUE
4FALSE TRUEFALSE
5 TRUEFALSEFALSE
6FALSE TRUEFALSE
7 TRUEFALSEFALSE
8FALSEFALSETRUE
9FALSEFALSETRUE
10FALSEFALSETRUE
View 4 Replies
View Related
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
Oct 9, 2008
How do I divide a range (number of rows will vary) into groups for calculation purposes?
I have a spreadsheet with too many dependencies to calculate properly (over 400,000). After I calculate I can delete the empty rows, and then there will only be a few hundred rows left and it will calculate just fine.
I think all I need is a loop that will break the range into groups and calculate the groups separately, but I'm very bad at loops.
This is what I need to break into workable parts.
Sub FilterTrue()
ActiveSheet.Calculate
Range(Range("AC16"), Range("AC16").End(xlDown)).Select
Selection.AutoFilter Field:=1, Criteria1:="TRUE"
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Selection.AutoFilter
End Sub
View 9 Replies
View Related
Mar 9, 2009
How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):
GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I
I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):
1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls
View 5 Replies
View Related
Mar 30, 2007
I realize that the easier resolve to this request is to use Access & we are moving to that application.
I have included a worksheet as an example.
We have a 'work in progress' (WIP) worksheet that contains 12 rows of data per job. Need to know how (or if) you can sort by the "Job #:" value while maintaining the group of information necessary for each individual job.
Not a normal sort because the values aren't entered in typical side-by-side row & column format.
View 10 Replies
View Related
Feb 14, 2013
I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.
The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"
View 8 Replies
View Related
Dec 1, 2007
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
View 10 Replies
View Related
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
Oct 2, 2013
How to autosort table after a row change.
View 1 Replies
View Related
