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 (
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.
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.
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
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
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.
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
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:
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.
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.
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.
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
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):
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
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.
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"
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
