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.
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'm going to attempt to explain the problem I am having with a project in Excel. I have tried to search using several different terms but have not been successful in finding an answer to my problem. I came close with a MS help document entitled "HOW TO: Sort Cells Without Sorting Linked Cells in Excel 2000". But that document does not directly address the solution. I think there is a clue there but I can't figure it out.
What I'm trying to do: I am trying to sort a data list that is one worksheet without affecting the links I have created to that data in a second worksheet. I have two worksheets; one holds the database, or list data; the other is a formatted price list where I have created links to the data in the first sheet. The idea is that I want to be able to manipulate the data in the data list and make changes to it while keeping the price list in a fixed format but with updated prices. I am using relative links and I have had some success in the past with these links automatically updating when inserting rows or cells, or moving the cell containing the formula. But this time I am trying to sort the data. When I sort the data the formulas in the linked cells simply point to the same cell reference as they did before the sort, producing the wrong results. I want the links to follow the referenced cells so that the results remain the same on the price list even when the rows in the data change position. For example:
The data on the worksheet named "Database" is in a list from A10:P65. I sort this list by two columns, first by Categories then by ManuPartNo. After I sorted the list I went to the worksheet named "Price List" and set up links to the cells in the data list that contained the correct information for my items. Some of my items are a combination of two or more rows from the data list. This works fine. If I make a change to the data in the list the price list automatically updates. If I insert a row or column to the data list the formulas on the Price List continue to work just fine. But, if I sort the data again, the formulas on the Price List no longer point to the correct cells in the data list. The formulas remain unchanged, but the rows of data move. I expected the formulas to update just like they do when I insert a row or move a block of data with cut and paste. Is there a way to force Excel to follow the rows as they move after a sort? Excel does this automatically when inserting rows. Why not with sorts? Where can I go to learn more about how to do this?
I am trying to create a Karaoke Song List in excel, which would show four columns on each page with Artist, Track, Artist, Track. I am importing a text file to do this. The problem I have is that I can import the file so that it puts Artist and Track going down, however, it would then need to go back to the top of the page to fill in the right hand side of the current page. Is there any code that retrieves the current print page number of the current cell (so that I can initiate the code to go to the top right of the current page and continue importing)? Or is there a better way of doing this?
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 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'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 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'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'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.
Check out this simple table as an example >
A B C D E F G H
1 TEXT QTY TEXT QTY TEXT QTY TEXT QTY
2 and 3 and 2 and 10 and 5
[Code] .........
Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.
Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.
Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.
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:
I have some source data which is used to generate a pivot table. The data is conditionally formatted to color certain cells based on some other values. Once the data is transferred to the pivot table this formatting (as well as the number formatting) is lost.
I have found some code to fix the number formatting issue but can't seem to locate something similar to set the interior color of the cells in the pivot table to match that of the original source data.
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.
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.
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.
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...
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK CELL
I would like to be able to resort the dats so that each line becomes a column BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK CELL
I know I can do it 3 cells/rows at a time using the paste special and transpose command but I have a 1000 names and addresses. How do I do it in one operation instead of a thousand?
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".
I have company asset data listed by item on wksht 1. Example:
Company A Computer 200 Company C Coffee Machine 21 Company A Car A123 Company B Computer 70 Company A Chair B14 Company B Desk C56 Company C Computer 59 Company C Desk C789
I want to sort and covert the column data on wksht1 to row data on wksht2. Example:
Company A Computer 200, Car A123, Chair B14 Company B Computer 70, Desk C56 Company C Computer 59, Coffee Machine 21, Desk C789
I'd like to do this via VBA/Macro(s) if possible.
I have to do this every month, so it would be nice to append the existing data to an "archive" file on wksht3 and then create this months data.
The data is imported each month via a text file with comma delimited fields which become the column data in the top example.
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
