I have a spreadsheet on lets say 10 columns, now col 1 and 9 are locked so they cant be selected. Col 2-8 can be sorted by selecting them, but information in col 10 does not sort.
Question is there a way of connecting the cells in col 10 to the cell in lets say col 2, so thay when you sort the rows and cols between 2 and 8, the cells on col 10 are sorted as well As i have noticed you cant sort two differenr selections...???
I have a workbook with a tab for each day of the month. This workbook is placed in a shared file and used by various yards in different locations. A new yard beginning the process is refusing to use it because due to formulas we must lock the workbook (contains many formulas and a live clock macro to track trip time) and they cannot sort A-Z. The sheets are labeled 1,2,3,4,5,6,7,8,9 and so on to 31. I know that you can write a Macro to allow sorting, but I cannot get it to work. the users CANNOT know the password due to hidden revenue etc. and formulas.
View 6 Replies View RelatedI have an issue with some data that I need to sort into several columns. Basically, I have a column that has data listed in each cell like this; A 567 T 1 D3. What I want to be able to do is sort that data into several columns. I am not sure how to do this at all. I have attached my data to be looked at. I have already started the process but manually, and I don't feel like having to do this manually as this is very time consuming considering I am trying to sort the data into ~1927 rows and 5 columns.
View 2 Replies View RelatedI need help with either a formula or macro for sorting data into specific columns. I need the entries under the headings Ar,Bj... to be sorted into the correct columns. To add to the problem, the data may not be exactly the same as the heading.
Attached is an simple example of a spreadsheet where the top is the original and the bottom is what I need the final outcome to be. The data is pasted from a different spreadsheet and will be changing each time.
I have to filter data in one column and have it align with data in another column. I need to do this because I have hundreds of product descriptions that I need to match up with the appropriate images. All of the image names will be like the first row (1244-?????.jpg) The problem I am running into the text data I am downloading is not matching up with the images. I usually have more images than text and sometimes I will have text that has no corresponding image.
1244-540299.jpg Golden Poinsettia Triple Candleabrum 1244-540299.jpg
http://img.auctiva.com/imgdata/1/3/2/1/0/4/0/webimg/539293145_o.jpg
1244-540300.jpg Poinsettia and Berry Triple Candleabrum 1244-540300.jpg
[Code] ...........
This is driving me crazy. I have a sheet with 5 columns, and 13k + rows of data.
I attached an example of how its is formatted...
The problem is, in column A "DBA" and D, "Address", some of the data contain 2 rows, and others contain 3 rows.
When I try to sort by DATE, for example, it ends up splitting up the addresses alphabetically...
How can I sort this data, by any of the columns, while still keeping the ADDRESS together?
I hope this makes sense to someone because it is driving me NUTS.
I am working on a new budget spreadsheet that I am creating. I want to accurately calculate credit card expenses. As everyone who has a credit card knows, charges made this month are not due to be paid until next month. To make matters more complex, charges made from (for example) January 20 until February 19 are not due to be paid until March 20. With that in mind, I created a spreadsheet with a different tab for each month. Each tab has a place to put in all expenses. Here is an example:
date
Category
Expenses
Remarks
[Code].....
Obviously, The above example is from January. IN the February tab, I want to calculate everything in the "Expense" column that has a date in the "Date" Column before Jan 20 and where it says "Kohls - Frank" in the Category column. Of course, in the March tab, I would need to grab all of the "Kohls - Frank" expenses after Jan 20 from the January tab, and everything before Feb 20 from the February tab.
I figured out how to add up everything with the following formula: =SUMIF(Jan!A:A,("<="&DATE(2013,1,20)&E32),Jan!C:C) How can I filter on the Category tab?
I have hundreds of rows of data which I need to sort into headings in a new tab.
E.g Column D has about 40 names which occur many times. Column G has 4 digit numbers which are unique.
I want to create in a new tab a column for each of these 40 names, using that name as the heading and then list the 4 digit numbers unders that heading. So if the name name "John" appears 20 times in Column D, it will get a heading in the new sheet and there will be 20 unique numbers listed below it from the data in Column G.
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 sheet with 56 columns with locked columns every 6 or 7 columns.
The rest of the sheet is unlocked for data entry.
What I would like the cursor to do is when it reaches a locked column, drop to next row instead of moving to the next free cell in the row.
Data entry is from left to right first six columns starting at C8 to H8
Next is L8 To Q8 and so on for 56 columns
When cursor reaches H8 I want it to drop to C9 or whatever column I'm entering into Q8 to L9
I was sent a file via email and when I opened it the data option on the top on the ribbon was locked and I need to ungroup rows.
View 2 Replies View RelatedI have researched and used some code that allows the user to select mutliple items from a list. This is using the Data Validation tool with a list and some code in the relavent sheet. It all works well and as described.
However, when I lock the Worksheet, the multi selection no longer works. Selection cells are ofcourse unlocked when sheet is protected.
I have also trialed unlocking the whole row that the multi selection exists in, no effect.
My understanding of this code is at about 50%.
Is there a way of being able to lock the sheet and retain this ability of multi selection. Maybe there is some other code I could refer too?
Code:
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
[Code].....
Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
View 14 Replies View RelatedIm not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.
View 4 Replies View RelatedI have a spreadsheet that is populated my Concatinating data from other worksheets. Some of the results are numbers, some is data while others are blank spaces and othersare set by the concactenating default of "" when the criteria is not met. I need to sort these colums. However it seems that the default "" from the formula is not a BLANK or a ZERO or an empty cell.
Those cells will place themselves at the top of the sort. I need to eliniate them. I have copied and pasted as values but that has no effect. Ironically I can identify the cells with an if statement so what I need help with is generating a macro that will cycle thru the range of results and delete the approriate cells.
I have tried the following:
lr = Range("A1").End(xlDown).Row ' Last Row
lc = Range("A1").End(xlToRight).Column ' Last Column
For Each cell In Range(Cells(1, 1), Cells(lc, lr))
cell.Select
AC = ActiveCell
If AC = "" Then Selection.Delete Shift:=xlUp
Next
But this does not work as it cycles top to bottom and leaves behind 1/2 the problem cells.
I have a potential of 5 columns of numerical data (simple number entries) which are entered manually in no particular order.
Is there any way of sorting the data so that it is presented in numerical order (smallest to largest) starting with the smallest figure at the start of column 1 up to the largest figure at the end of column 5.
I have three columns, one column is time every two seconds with data associated that time, and one is time every minute with an associated tidal height.
I want to sort my data so that for every 2 seconds I have an associated tidal height for that minute.
eg:
what I have:
Time Time Tidal Height
(2 sec intervals) (min) (meters)
1:15:00 1:15 1.342
1:15:02 1:16 1.221
1:15:04 1:17 1.115
...
1:15:58 1:43 0.024
1:15:60 1:44 0.012
1:16:00 1:45 0.008 ....................
I am trying to 'stagger sort' multiple columns, but am having some difficulties.Ex.
Say A and B are lists of part no and C is data corresponding to column B. I have:
A B C
1 2 .
2 3 ,
4 4 ;
5 7 '
8 9 "
I want:
A B C
1 _ _
2 2 .
_ 3 ,
4 4 ;
5 _ _
_ 7 '
8 _ _
_ 9 "
(the _ are just supposed to be empty place holders, without them I have formatting issues)
Is there a 'quick' way to sort them this way?I am attaching the actual file that I am working with.
How do I sort multiple columns at once? In other words, I have a chart that is a series of 1s and 2s, and I need all of the 1s to drop to the bottom, so that I can do a rudimentary chart in spreadsheet form. My chart has dozens of columns like so:
1 2 1 1 1 2 2 2 2
1 2 1 1 2 1 1 2 2
2 1 2 2 2 2 2 2 2
1 1 1 2 1 1 1 1 2
How do I get the entire range sorted to look like this, without having to do each column individually (hours of work)?:
2 2 2 2 2 2 2 2 2
1 2 1 2 2 2 2 2 2
1 1 1 1 1 1 1 2 2
1 1 1 1 1 1 1 1 2
I have some data that is 300 rows x 43 columns.
I'd like to sort the 43 colums by using some of the rows. Is this possible using normal excel?
Alternatively is there a simple way to sort the colums in VBA?
I'm managing a World Cup Prediction League and have been trying to create a spreadsheet that will eventually feed in to a league, positioning each player in descending order. However, I would like to use three columns to determine how to position the players.
EG.
Priority 1 - Column D - Total Points (Rank from Highest to Lowest)
(if there are players with equal Total Points in column D, then)
Priority 2 - Column F - Correct Scores (Rank from Highest to Lowest
(if there are still players with equal value after Total Points and Correct Scores, then)
Priority 3 - Column F - Incorrect Scores (Lowest to Highest)
I've tried for hours to work this out but can't. It would save me hours of work each day
I have attached the document : WC Prediction League EXAMPLE.xlsx
I have two sets of 2 columns. One column in one set is identical to a column in the other set, but they are arranged in different orders. I would like arrange those two columns side by (matching their numbers) so that I can find the average of the two non-identical columns.
View 2 Replies View RelatedI'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
View 4 Replies View RelatedIn Column A there are randomly assigned numbers using the RANDBETWEEN function.
In Column B there are three possible values X, Y, and Z.
I want to create a sort order where "X" is always at the top but then the rest of the rows are randomly sorted by column A (Y and Z values are intermixed).
Currently I tried to use a Custom List and the value is "X" to sort first, and then a seconday sort of Column A. The problem is that column B is always sorted by X then Y then Z where I am trying to get the Y and Z values to be randomly intermixed based on the sort of the random numbers in Column A.
I know I can manually do 2 seperate sorts each time, but I would like to save a sort list to the file so I recreate that sort over and over again.
I need sorting a contact list of 3000 + so the emails match with the company name, and first and last name of the contacts
Column A is Email and Matches column C & D. Column B matches with Column E. I need A to match with B-E.
I've attached an example : Example Spreadsheet.xls
I have a worksheet with columns of Unique Words and corresponding Frequency of Occurrence for several years. I would like to sort the data so the rows match up on same Unique Words across years.
I am using Excel 2007.
Excel 2007 - Sorting across columns
Need to sort part of an Excel worksheet across multiple columns.
For example data is:
ABCDEF1
Machine
4394264394294394434399834399842
Model
TD860TD400TD860TD620TD6203
Pgm
OPTABCOPTEP2EP24
Start
5/31/119/1/115/1/096/15/092/1/085
End
2/28/158/31/125/31/114/30/108/31/09
I need to sort it first by Pgm, within Pgm by Model, and within Model by Machine. I do not need to sort on Start or End
The results should look like:
ABCDEF1
Machine
4394294399834399844394264394432
Model
TD400TD620TD700TD860TD8603
Pgm
ABCEP2EP2OPTOPT4
Start
9/1/116/15/092/1/085/31/115/1/095
End
8/31/124/30/108/31/092/28/155/31/11
After selecting the data to be sorted (columns B,C,D,E,F), I tried using "Sort Left to Right" under DATA-SORT-OPTIONS but it doesn't seem to work.
How do I sort multiple columns alphabetically with the last name first, then the first name as the 2nd sorting option?
Here is an example of what I have:
year; first; last
2011 chrisBell
2010 chrisBell
2009 chrisBell
2008 chrisBell
[Code] .......
As you can see, the first names aren't sorted 100% alphabetically. I think issue stems from the years 2001 n-2006 n. I have to use the 2001 n since its a different data than the normal 2001.
The default sorting was by year:
2012
2011
2010
2009
[Code] .......
So I want it to sort by last first, then first name. Then it should sort by the default sort I already had in the left column (years).
I want to sort columns A,C:P and use column C as the column that I sort and the other rows will move with column C. I want column B to stay and not move with the sort. How can I do this.
View 2 Replies View RelatedI'm trying to sort an array which is of variable height/rows when put on a worksheet. I need to sort by column C ascending (smallest to largest) and then by column D ascending (oldest to newest). I tried recording a macro but the code is just junk.
Code:
Dim BookSPSortingWorksheet As Worksheet
Dim TR As Range
Set BookSPSortingWorksheet = ThisWorkbook.Worksheets.Add
Set TR = BookSPSortingWorksheet.Range("A1").Resize(UBound(array_book_SPNames), UBound(array_book_SPNames, 2))
TR = array_book_SPNames
Is there a way I can use the sort function like the below by indexing my TR range for columns 3 and 4?
Code:
TR.Sort key1:=TR, order1:=xlAscending, MatchCase:=False
