Sort Data Over Multiple Columns
Nov 6, 2009
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?
View 7 Replies
ADVERTISEMENT
Feb 15, 2010
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.
View 2 Replies
View Related
May 19, 2014
Why my vba sort re-arranges columns instead of just sorting them.
I have four columns of data: T = department, U = director, V = client, W = product.
The number of rows varies. The data needs to be sorted by T, U, V and then W. I tried to create a macro that would perform the sort in two steps:
Sub test()
With Sheet19
.Activate
.Range("T2:W" & Cells(Sheet19.Rows.Count, 20).End(xlUp).Row).Sort _
Key1:=Range("W2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlSortRows
[Code] ........
The macro sorts the data in the correct order, but it also puts column W before columns T, U and V.
So I end up with T = product, U = department, V = director, W = client.
View 1 Replies
View Related
Oct 29, 2008
I have a Name in Column A
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.
View 9 Replies
View Related
Oct 23, 2009
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'
View 9 Replies
View Related
May 14, 2012
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.
sort>sort>sort>sort>VEHICLECOLOURSMAX SIZESPEEDA9brown50100PRONTObrown100110VELICITOgreen200120
SPECIALpurple50155ZEROpink2585GREENVgreen5155MIDEOblack2000135DELVOpurple300155ASPIROblack6155
View 2 Replies
View Related
Aug 6, 2008
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.
View 9 Replies
View Related
Feb 14, 2014
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
View 14 Replies
View Related
Nov 14, 2009
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.
View 8 Replies
View Related
Jan 3, 2012
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).
delete
EFGHIJKLMNOPQ8BallFrqPct
BallFrqPct
[Code]....
View 2 Replies
View Related
Sep 27, 2008
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?
View 2 Replies
View Related
Jul 2, 2008
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.
View 9 Replies
View Related
Jan 6, 2014
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.)
View 2 Replies
View Related
Dec 19, 2012
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.
I want to be able to turn this:
HTML Code:
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3">
<tr>
<td>A</td>
[Code].....
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.
View 3 Replies
View Related
Apr 4, 2014
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.
View 2 Replies
View Related
Jul 19, 2012
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
View 2 Replies
View Related
Jul 2, 2014
how to sort data pasted from word into specific columns e.g.
I paste: 1,2,3,4,8 into the ref row, and it poulates as such, leaving out the columns for which there is no corresponding value:
Competency: 1------2--------3-------4------5------6------7------8
Ref: -------WR1----WR1----WR1-----------WR5------------------WR1
View 2 Replies
View Related
Jun 23, 2006
I have a column of repetitive data:
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?
View 9 Replies
View Related
Feb 12, 2014
I am new to VB Macro creation and I am creating VB Macro which will:
1. Sort data in Columns within an active spreadsheet
2. Create new Worksheets
3. Delete Values in rows based on value in Column.
I have listed my "Step by Step" instructions in the tblTest Excel file on attachment. The instructions are clear and straight to the point.
Below are some VB Macros I was experimenting with but it is not complete.
Sub Sort_Ascending_With_Header()
'Sorts a worksheet in ascending order and assumes there are headers on the data
Range("A1:DZ20000").Sort _
[Code].....
View 2 Replies
View Related
Jan 13, 2014
Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
View 2 Replies
View Related
Jun 6, 2014
I have a workbook has a "Summary" worksheet. I would like to be able to do the following:
(1) Sort columns D, G, J, M,... (the number of columns are different each time I run the macro that I have).
(2) create a combo box to be able to the data (that sorted in 1) by month (1,2,..., 12).
see the attached file
View 5 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
Aug 16, 2006
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 ..........................
View 9 Replies
View Related
Dec 14, 2006
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.
View 9 Replies
View Related
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
May 2, 2008
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?
View 2 Replies
View Related
Feb 23, 2010
Once again, I'm seeking some further assistance to an issue that I have yet to resolve.
I have several rows of data which I'd like to be able to sort by the info in column "A".
The data rows are not all consecutive. The range for which the data resides are:
Range("A25:A73,A85:A105,A145:A193"), When I sort I want my data to stay within these limits/ranges.
View 8 Replies
View Related
Jul 9, 2014
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
View 1 Replies
View Related
Feb 2, 2008
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:..............
View 2 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
