I'm trying to figure out a way ( excel 2000) how to sort data over a range of columns.
Attached is a sheet.
So what I am looking for is this:
Bottom 10 for target 1, target 2, target 3.
I can sort them indervidually, but is there a way to sort the all?
Or would I need a agent column for each target to sort?
Or maybe there is a way to sort the data so it would work out that if they are in the bottom 10 of lets say 2 of the targets but not all 3 they would still show in the bottom 10?
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:
Sub CopyToA() Do While ActiveCell <> "" Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0) ActiveCell.Offset(0, 1).Select Loop End Sub
However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.
I have a Number total(using a formula) in Column B
Each week I am copying from another source Names and Numbers, let's say in Column C and D.
I want to match the names in A with the Names in C so they are in the same row. The number in Column D needs to go with name from Column C.
Right now I am having to highlight the name and number in Column C & D and then drag it down to the row with the exact name in Column A. If there is a new name it adds it at the bottom of Column A and C.
My columns are labeled A B C D etc... If the product I am creating the table for falls into one of those categories I place a 1 in the cell. For instance, ProductA falls into the A and B categories, so column A will get a 1 and column B will get a 1. This is so I can sort all of category A's Products etc...
I am looking for a formula to put in a column that would Say what categories ProductA falls into. ie Since ProductA falls into the A and B catergories but not the C and D catergories, This final column would say something like AB, or A,B or something.
Alternatively, I could make a single column entiltled 'categories' and put something like 'A B' in ProductA's column. In this case I would need a way to sort all A's or sort all B's and not just sort all 'A B'
I need to put a worksheet together that has multiple columns that I can then sort in order (on any one columns that affects each row together) in "vehicle type".
I want to set it up so the full listing can be sorted top to bottom in colour or then click sort to sort in MAX SIZE and or again sort in SPEED. (So sort in Alpabetical order or by value).
I havent used pivot tables before and assume its the best way. I dont want to use the filter system to sort as its too clumsy for the end user.
In row 1 I want to have the names of servers, so we would have A C D E
Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.
Then B would have under it Start, End, Data, Time
And so on for C D ...
I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be
A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.
I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.
In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.
This is the array formula I am using at the moment: AliGW - Example.xlsx
I have a row of sub-headings at row 12 that require Sort Filters. My problem is that I need the filters on selected columns only. It seems that with the standard filter button I get all or nothing.
I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).
I have a spreadsheet with multiple columns. The first column defines a "route", and the next two list "start" and "end" cities for that route. The fourth column lists the length of each route. There are only a limited number of cities, so the same entries appear in both "start" and "end" several times. I would like to use Autofilter to sort the list for every appearance of a given entry in either "start" or "end". Is there a way to make Autofilter sort mutiple columns simultaneously?
I could achieve the desired end result with Advanced Filter, but I want something with the ease-of-use and immediate update/response of Autofilter. Advanced Filter requires explanation (as well as lots of clicking and typing) whereas Autofilter is self-evident. I also want to avoid VBA Macros as they are not well-understood by the users who will use this spreadsheet (and any VBA Macro will require very specific input to work properly.) Is it possible to do what I want? Or is Advanced Filter / VBA the only way to do it?
I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.
Column A has donor name, column B has corresponding donation data for 2011. Column C has donor name, and column D has donation data for 2012. Column E has donor name, and column F has corresponding donation data for 2013.
Although Columns A and C and E all have donor names, there are many changes from year to year with the addition and deletion of certain donors so the rows do not match up exactly. Is there a quick way to have the rows match up according to donor names in column each of the three columns? Secondarily, I need to sort by donation amount from highest to lowest (it can either be the total over the three years or based on any single year.
Currently my list looks like this: 2011 2012 2013 A A A B C B D D C F E D H F I L G K M H M
I would like it to look like this: 2011 2012 2013 A A A B - B C C - - D- - E- F F F G G G H - - L - L - - M
Basically, I want to see the year over year giving for each donor. I've attached a sample for refrence. (I can't post the original due to confidentiality requirements.)
So I have several columns of data that have a location and then some numbers after it in additional columns. I need to be able to sort it so that all locations that are in both location columns are sorted first, and then any locations that are in one column but not the other column follows. Also the data associated with each column that follows the location needs to remain next to the location. Many times the columns will be of highly disportionate lengths too.
I've attached an HTML table so you can get an idea of what I am looking at, except what I'm dealing with is like.... hundreds of cells long.
And in this 6 row example, I'd actually only be interested in the 4 letters that were in both columns. If it makes it easier, I would be okay with discarding the last rows, but would rather just have them sorted at the end, so I could check if....for example there were two different spellings of the same thing, and they should actually be included.
i have a data entry tab where we put names and button to sort and hide blank rows from other tabs. Problem i want to move down the number entered also. something like extend sorting but not affecting the formula on the other columns.
I have 3 columns of data and 30,000+ rows. Column A & B are related and column C contains some of the same values as column B but not an exact match. I'm looking for a way to sort columns A & B so that columns B & C match up and the data from column A is moved as well. For any data that has no match, a blank space should be input. Please see below.
A B C
red dog cat blue cat dog green mouse pig yellow horse kitten
A B C
blue cat cat red dog dog green mouse pig yellow horse kitten
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'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 am using the code below to transfer data from a single sheet to approx' 200 sheets. These sheets are staff training sheets, one per staff member. This code works great. What I would like to know is, is there a way to then sort the data on these sheets in decending order? I have tried on sheet change but this seems to stop the transfer to other pages.
Sub Tranfser() Dim shtTemp As Worksheet Dim lngOutRow As Long Dim rngData As Range For Each rngData In Range("A5", Range("A5").End(xlDown)) Set shtTemp = GetWorksheet(rngData.Offset(0, 1).Value) If Not shtTemp Is Nothing Then ..........................
We are trying to sort a spreadsheet by the data in column I. This column refers to a state. I need help creating a macro that can sort column I so that different states go into different worksheets.
States ME, NH, MA, RI, CT, VT go to a worksheet titled 357899, states NY, NJ would go into worksheet 351835, states MI, IN, OH would go into worksheet 351857, and everything else would go into worksheet 351836. The main data worksheet where the info is being sorted from is named All_Accounts. Column I has a header labeled State, so data actually starts in Row 2. I need the full rows copied to the new worksheets while leaving the main All_Accounts worksheet in tact.
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 been asked to create an attendance worksheet where employee names and data are entered on a "main" worksheet and hours are entered on monthly worksheets. The names on the monthly worksheets are referenced from the main worksheet. Therefore, if I add a name and do a sort, the names on all pages will move, but the data will not. I imagine I will need an ID column to help sort. How do I make a macro to do the sort?
I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.
My Source excel file looks like:
ABC A-101 B - 202 DEF B-203 C - 100
The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.
The output I want is:
A B C D ABC 101 202 DEF 203 100
There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.
In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
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
