Sorting A Range Of Cells
Jan 10, 2008
I am using Excel 2003 and am after a macro that can sort a specific range of cells.
I have a spreadsheet that is used to record details of photos taken during bridge inspections.
•The details of the photos are recorded in column B
•The number of the digital photo is recorded in column C (the number of photos taken per inspection varies)
•After checking by someone else the photos are assigned a number in column A
After two blank rows that process is repeated for another inspection.
I am after a macro that when a cell within a range/block is selected it will sort columns A:C by the numerical order of the range block within column A.
For example when any cell is selected within the range A3:C7, that range and only that range will be sorted.
The same would happen when a cell is selected within range A11:C15, and so on....
View 9 Replies
ADVERTISEMENT
Oct 21, 2013
Im having problems trying to sort through data and linking certain data. I will post example below:
Main 1
Main 2
Main
3
Sublevel 1-4
[Code]....
This is just one part of a row of the workbook but shows the Main data which is made up of 4 different sub levels on the right. I merged 4 cells for the main data so that all the cells lined up but I'm still having problems with being able to sort and link the files together so that when I filter, they all do it together. The example didn't paste well.
View 3 Replies
View Related
May 28, 2014
I'm attempting to create a formula that'll sort a range of numbers and then return the corresponding values based on a specific place within the sorted range.
In this case, I'd like the following numbers sorted smallest to largest:
15, 9, 11, 19, 22, 17, 24, 19, 17, 24
The result would return something like:
9, 11, 15, 17, 17, 19, 19, 22, 24, 24
Then I'd like it to return the value based off of the formula in cell U13 & U14. In this case, it'd return the value in the 2nd and the 9th position (bolded above). I tried to use INDEX(), but it's returning the values in the unsorted list. I'd like it to return values from a sorted list. Also, I'm trying to solve this using a formula (not Sort & Filter) or VBA.
View 1 Replies
View Related
May 16, 2009
I have a values in column C of stuff1, stuff2 and on down.
I have a value in cell A1.
I want to start in C1, and every Nth, where N is the value in cell A1, select and paste into column H.
My problem is that the selected cells don't start in C1, in fact, based on the value in A1, it starts in different cells in column C. AND, if i press the button twice, the returned values are different.
View 14 Replies
View Related
Feb 20, 2013
I am trying to doctor a recorded sort macro to adapt to a changing range.
With the code below I get:
RunTime error 1004
Application-DEfined or Object-Defined error
I have tried with no luck:
Key:=dat
.SetRange dat
Key:=Range("A1").Resize(rw, clm)
.SetRange Range("A1").Resize(rw, clm)
Key:=Range("dat")
.SetRange Range("dat")
Key:=Range(dat)
.SetRange Range(dat)
[code]....
View 1 Replies
View Related
Nov 5, 2013
I am trying to sort a range of data by 1 column (column D), and get the following error:
"The sort reference is not valid. Make sure that its within the data you want to sort, and the first Sort By box isnt the same or blank."
Having googled and also checking some previous posts, I thought the issue may be around qualifying my key to ensure that I used the active sheet, but having done this, I still get the error.
CodeRange("A2:V2", Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Overview").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Overview").Sort.SortFields.Add
[Code]....
View 3 Replies
View Related
Jul 9, 2009
example
1
2
3
1
2
6
4
3
6
7
In the Above range i want excel to show me
how many items are duplicate
If the duplicate item is shown i want the result to be shown in next cell
LIKE in the above example i want excel to show 1 is common , 6 is duplicated twice and therefore result should be shown in next cell
View 14 Replies
View Related
Jul 14, 2006
I've noticed (a bit to late) that if you record with macro recorder a sorting statement that a range is captured. Because my files do vary in recordcounts, how can I sort indepently on the number of records.
View 4 Replies
View Related
Jul 2, 2007
I have Excel 2003 with named cells, for example...
cell cell name data
A1 AAA 3
A2 BBB 1
A3 CCC 2
and so on...
When I autofilter (ascending) these cells, the data in these cells sort correctly, but the cell names stay where they're at.
cell cell name data
A1 AAA 1
A2 BBB 2
A3 CCC 3
I'm not an Excel expert so I'd like to know is there any way I could get the cell names to sort with their data.
cell cell name data
A1 BBB 1
A2 CCC 2
A3 AAA 3
View 6 Replies
View Related
Jun 15, 2008
in the the attached image of excel
i want to see what is the bigger number in the row 49
then sorting these numbers descending and write it in C52
then make a quick equation on in each number ,and this equation is to deduct this figure from each other nine numbers and see what is the zero or above zero result and put it the cell next to this number (column E) starting from E52
example
527.7 is the maximum number so i put it in the c52
and then deduct 527.7from each number ,like the following
41.0-527.7 = negative value (doesnot count)
21.8-527.7= negative value (doesnot count)
....
.... so on
and we will see that the only greater than zero or equal to zero result is only 1 result so we put the figure 1 in the cell E52
and so on on each number
View 11 Replies
View Related
May 30, 2014
I have some data formatted as a table. There are some blank rows at the end that still contain formula.
Every time I sort the data these rows move to the beginning. I have tried only selecting the active rows of data, but the whole lot defaults in each time
How do I sort data with a selected range only (within a table)?
Don't really want a macro as this will need to be used by others
View 1 Replies
View Related
Jul 31, 2014
I have a spreadsheet with column headers like, "Date" "Phone number" "Name" ect., and rows of data below. Each of these rows contains data pertaining to a prospect employee, as the purpose of the spreadsheet is to keep track of job applicants. One column is used as a 'Recruiter Sign-off' area, where the recruiter who processed the application inputs their name:
Example.png
I'm trying to automate a variation of excels sort function. However, I don't want to sort 'From A to Z.' Instead, I want to have the rows of data sorted with the 'Recruiter Sign-off' column used as the sort Key, and a selected name (of one of the recruiters) used as the sort Criteria.
I already have a userform that allows the user to select a name from a listbox. When they press confirm, the name is stored in a Public String variable, (selectedRecruiter). I'd like then like to have the rows below the column headers to be sorted based on the selected name. Example: User selects the name "Jon" from a list. The application then brings all of the applicants that 'Jon' has signed off on (those rows that contain his name) to the top rows of the range.
The workbook is shared, so I cannot use tables.
I found a code that accomplished what I want, however it also leads to a horrible system crash after a few times running it (BEX crash).
[Code] ..........
View 3 Replies
View Related
Aug 25, 2007
Below is a script, graciously provided by Bill Rockenbach, in order to sort a specific word "Text" between two columns.
Sub SpecialSort()
Columns("C:C").Insert
Range("C1") = "=IF(OR(A1=""Text"",B1=""Text""),1,2)"
Range("C1").AutoFill Destination:=Range("C1:C13"), Type:=xlFillDefault
Range("A1:C13").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").Delete
End Sub
Works marvelously, How could I use this formula to sort by a specific color? ie: instead of the word "Text", to be replaced with a background color RED
View 8 Replies
View Related
Mar 29, 2014
I know the formula =large but my problem is the formula for the equivalent value for it. Note: In my xcel file attached you can see there are multiple items with the same total numbers and also blanks
View 3 Replies
View Related
Jun 8, 2009
I'm trying to do is sort cells which are referenced by names in the name box when a button is clicked. All the coding is in VBA.
If a cell is sorted by a macro, I would like the referenced name to move with it on the spreadsheet.
For example if the referenced name box for C5 is Bob and Andrew is the referenced name box in C6, when a button is clicked I would like the Andrew reference to move to C5 and Bob to move to C6. This is sorting in alphabetical order. However, I would like the referenced names to stay the same. Referenced Name refers to the cell name box.
Is there a way to do this in VBA?
View 12 Replies
View Related
Sep 21, 2013
I have this code to sort numbers from smallest to largest but i need it to ignore blank cells.
Sub sort1neg()
'
' sort1pos Macro
'
'
Range("a4:aq174").Select
ActiveWorkbook.Worksheets("sort").Sort.SortFields.Clear
[Code] ........
View 1 Replies
View Related
Mar 13, 2007
the system works by whoever has the most points wins.. i already have my formulas and everything done..
i want the excel program to move the entire row, if possible, by whoever has the most points to the top..
(i.e If ryan d. has the most points he will be is vince v.'s spot)
can this be done automatically in excel or do i have to do it by hand?
View 9 Replies
View Related
Sep 12, 2008
I have a worksheet with a data base type list (16 cols, 10000 rows deep) which contains only 30 records, for testing purposes. Each cell is bordered in all 4 sides.
There will be a bunch or reports associated with this, requiring filtering and/or sorting. The macro instructions I applied so far are:
1) Locate last row.
2) Select the range from first to last row and apply a common row height (say, 24).
3) Sort as required
4) Filter as required.
5) Print
The sorting step has invariably failed.
The funny thing is that the code I used is the one produced by recording the steps. I am able to sort that segment manually, but the macro instructions fail.
here is the code...
Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Key2:=Range("H5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
View 9 Replies
View Related
Jul 31, 2009
I am using Excel 2007.
I have a spreadsheet with about 30 items in 30 rows. I have a picture object which corresponds to the item # and other info in each row. When I sort the rows, the picture objects don't get sorted with the rows and end up no longer corresponding with the item # and other info.
I have right clicked and set the properties for each picture object to "move and size with cells". This does not prevent the problem.
View 9 Replies
View Related
Jun 29, 2006
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue
View 2 Replies
View Related
Nov 25, 2009
I am using Microsoft Excel 2003. I put color in my cells to show contrast with the information in my data list. I used blue and white for my cell colors. When I used sort to put my information in alphabetical order it did not only rearrange the information, it also rearranged the colored cells. My orginal pattern (before using sort) was blue, white, blue, white, blue, white. After I used sort, the pattern was changed to white, white, blue, white, blue, blue. Is there any way I can use sort to arrange my information only in alphabetical order, using sort, without rearranging the color of the cells?
View 5 Replies
View Related
Mar 8, 2008
I’m having problems linking data from various worksheets to one master worksheet, all in the same workbook. The workbook contains swimmers PB’s (personal best times) for each stroke and after each gala the swimmers new time is entered onto the worksheet. The worksheet data is then sorted so that the fastest swimmer is at the top.
The master worksheet contains all swimmers by name (alphabetically) and their best times for each stroke, what I am trying to do is have the master worksheet update automatically when I enter the swimmers new PB’s in the individual stroke worksheets, this works when I use ’Paste Special’, but when I go to sort the data with the fastest swimmer at the top, the data in the master worksheet does not reflect the correct times.
View 9 Replies
View Related
Oct 21, 2007
when I sort data columns that contain drop-down menus. My spreadsheet has several columns: "Patient name", "Room #", and "Phone #". Each cell within the "Phone #" column should have a drop-down validation list containing two possible entries: the room's designated telephone number, or NONE. Whenever I add a new patient to a room, these three columns need to be sorted alphabetically by patient name. But when I sort all three lists simultaneously, it looks great- but then I realize that the drop-down lists associated with each "Phone #" cell haven't sorted! They stay in their original location and don't sort with their cell. So the designated telephone numbers for each room are all screwed up.
View 9 Replies
View Related
May 16, 2014
The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 Replies
View Related
Jul 1, 2014
I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?
View 2 Replies
View Related
Oct 17, 2013
I downloaded a massive dataset in .csv format to work with. My first problem is that I'm having trouble sorting the dataset. When I open the .csv-file every bit of information comes up in the A-column instead of having INFORMATION1 in the A-column INFORMATION2 in the B-column and so on.
View 1 Replies
View Related
Nov 1, 2008
Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
Example (Excel 2003): ..
View 4 Replies
View Related
Feb 5, 2013
I am trying to sort a list of business development leads. When a lead is dead I enter the date in the column entitled "Closed". Therefore, the Closed cell for active leads is blank. Whilst I still wish to retain the information of dead leads, when sorting I would like them listed at the bottom of the list. However, whenever I sort on the Closed column, whether using newest to oldest or oldest to newest, the blank cells are always at the bottom, instead of the top.
How to get the sorted blank cells to the top?
I'm using Excel 2011 on Mac OS X 10.7.
View 2 Replies
View Related
Apr 23, 2013
Here is the original table:
This is with a filter on:
You can see there that some inserted object (in this case, PDF files shown as icons), are moved. I need to find a way to immobilize every inserted object within each cell boundaries. I want to freely sort or filter and avoid this kind of problem that gets worse with more rows, columns and inserted objects.
View 1 Replies
View Related
Sep 20, 2007
Ive spent a few hours adding email hyperlinks to data in column B.
I now want to sort the data in the worksheet into ascending order using the data in column A.
The problem is, none of the hyperlinks from column B sort with the data in column A, they are still in the cell locations I had originally entered them.
View 9 Replies
View Related
