Macro For Sorting A Sheet By Colorization
Nov 26, 2013
I have a sheet with over a thousand part numbers (lines of data) whereby they are uniquely colored.
I am looking for code to allow the Macro to Sort the lines by the color of the line, identical to the Excel provided function. However, in that one- it is tedious to add the levels for the multiple Colors going on and I would like the Macro to perform it all at once.
So, I have five unique colors (Maybe doesn't matter)that remain constant: Red-Color Index:3, Yellow-color Index:6, Lime-Color Index:43, Gold-Color Index:44, and Gray-50%- Color Index:16? (I think).
So- when I run the Macro I would like it to Sort the data and group all the Colored lines (Parts) in series (doesn't matter the color order). In other words if there are 50 part numbers red and 100 part numbers Lime then Rows 1-50 are all the Filtered Red parts and Rows 51-150 are all the Lime and so on for all the other Colors.
View 1 Replies
ADVERTISEMENT
Mar 22, 2014
1- code that will use to show position (1st, 2nd, 3rd....) based on two criterion in the attached sheet
A-the Student must has 49.5%
B-the student must get a "PASS" remark
2- How can i auto sort the position in descending order in another spread sheet.
View 1 Replies
View Related
Mar 19, 2014
I have a continuously growing list of data that I need to sort onto another sheet automatically. The sorting data is always in column A but sometimes is not always in the same format; however the sorting data is always the first 6 digits of the data in the cell. When i add the new data to the first sheet I would like the data to be automatically sorted onto the second sheet with 1 row in between each group of data.
View 2 Replies
View Related
Feb 24, 2006
I have a database being exported to a spreadsheet with over 40 fields (columns) and 1000+ clients (rows). One of the fields (column C) incidates that referral source. I would like to copy & paste the list into sheet 1 and have 5 seperate sheets for each of the 5 different referral sources. So that when I go to sheet 2 the only thing I will see are the clients and their 40 associated fields that are referred (column C) by say "John Smith"
Is there a function or script that will search column C for "John Smith" and then fill in all the 40 fields on sheet 2.
View 11 Replies
View Related
Mar 4, 2008
A B
1 List 2 things that went well during the meeting
2 Election of Chair Election of Vice Chair
3 Powerpoint Presentation
4 Presentations were informative Everyone at the meeting took part in Q&A
5
6 Group discussed Gardens Upkeep We were mostly in agreement
7
For each meeting attendee (row) they will give their answer to the question (A1).
Some will write no answer, some 1 answer, some 2 answers.
I need to list these answers in another sheet on seperate rows, thus seperate cells, in one column. Without having a gap between each of the answers on that sheet.
I have been wracking my brains and came upon this site searching via that best loved search engine we all know.
I would be very greatful if anyone could help me here.
I'll be watching this closely, nothing else to do.
so I'll be giving quick replies.
View 9 Replies
View Related
May 24, 2014
We are needing to do a two tier sort in a protected spreadsheet. Is this possible?
The two columns that need to be sorted have protected cells between them.
If there a way to ad a button that will automatically do this sort for the user... as in can I add a button some how in one of the empty cells that will do it?
Here is the sheet. [URL] ..........
Looks like I need to sort column A thru Z, except W.
View 1 Replies
View Related
Feb 10, 2009
I have a dorm roster on one sheet and the other sheet is an Alpha Roster. I want it to automatically sort alphabetically (column B) . Any time I change the roster, the Alpha Roster sheet doesn't automatically sort, I have to hilight and re-sort it again.
I updated the file, there are 4 tabs at the bottom. 1st Floor, 2nd Floor, and 3rd Floor. I want to have all the names, room numbers, and phases (ph) in alphabetical order on the Alpha roster tab. And when ever I make changes to one of the Floors, it will automatically update it the Alpha Roster.
View 14 Replies
View Related
Mar 11, 2009
I have a list of movie titles A3-A9999(Infinity), their disc numbers in B3-B9999(Infinity). The titles being written in text, of course.
All I want to do, is have another list, in the same sheet in D3-D9999 that will be the same list as the original list that I input movies onto, except alphabetical and automatic from when I type in the movie title in the original list. When i just use "=A1, =A2, =A3 ~~~ =A999" in the D cells, then try to sort, it only sorts by the cell numbers, not by the actual text that I want it to.
Is there a way to do this? I know excel relatively well, but I'm definitely a noob compared to experts.
View 4 Replies
View Related
Jul 12, 2013
So I have started working for a medical supply company and sorting through the prices and item numbers of thousands of line items is difficult to do manually. We have a computer guy who can take two files and match certain columns up.
For example, I have a file with the Item Code and Our Costs, and a different file with Item Code and a different companies prices (so I know what prices to beat while maintaining a higher margin). Every time I need to do this sorting I need to go to my computer guy and give him the two files to merge them together so he can match the thousands of items of our competitors prices to line them up with our costs for the proper item.
Example is attached as an excel file, although it is a hundredth of the size of many files I work with.
merging and sorting files.xlsx
View 3 Replies
View Related
Jan 29, 2007
I have tried to use absolute references (perhaps I didn't do it right) but it didn't work. Below is the code that does work but I need to first make the sheet visible then activate it.
Sub Row_Sort(tmpsheet As String)
' Sort the first 4 columns of the tmpsheet sheet
Dim tmprng As Range
Dim OldActiveSheet As Worksheet
' Activate the desired workbook (in this case Linewkbk)
Workbooks(LineWkbk.Name).Activate
' Save old active sheet
Set OldActiveSheet = ActiveSheet.............
View 9 Replies
View Related
Jul 23, 2013
I have excel sheet which is having following format
sr no | bank | branch | chk no | amount | party | bill no
1 | combobox1 | combobox1 | 123456 | 12000 | combobox1 | 123456
2 | combobox2 | combobox2 | 12346 | 2000 | combobox2 | 123456
3 | combobox3 | combobox3 | 12345 | 900 | combobox3 | 123456
4 | combobox4 | combobox4 | 13456 | 7000 | combobox4 | 123456
5 | combobox5 | combobox5 | 3456 | 18000 | combobox5 | 123456
When i try to sort amount column it need to sort and change with corresponding row but it is not happening so what should i need to do for that ?
View 2 Replies
View Related
Jan 30, 2014
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
View 3 Replies
View Related
Nov 27, 2006
I NEED A MACRO THAT CAN SORT SPREAD SHEET ALPHABATICALLY AND BY DATE BY CLICKING ON THE RELEVANT BUTTON BUT I DO'NT KNOW THE SYNTAX AND FORMULA FOR SORTING. IF SOME ONE GIVE ME A FAVOUR AND HELP ME IN HAVING THE RIGHT MACRO.
View 5 Replies
View Related
Feb 9, 2009
I have Column A where user inputs data. Whenever a user enters a new value in Column A, I need to copy that data into Column B and then sort the whole column. Any suggestions on how to do this using a macro?
View 9 Replies
View Related
May 9, 2006
I am a mechanical engineer by trade and am working in Excel to manipulate data. I have written macros in the past, but have come to a stumbling block. For simplicity say I have a column of timing information such as:
Time Sequence
1
9
15
23
Then I have another column of sequential time:
Time actual
0
1
2
3
4
5
etc.
I want to be able to take the first two values of the first column, search the second solumn for any values between those numbers, then create a new spreadsheet and copy those rows to a new spreadsheet, then loop back to search for the next two sets of numbers. So I want to search my actual time for values between 1 and 9 and put them all in a new sheet, then search for values between 9 and 15 and put those in another new sheet, etc.
View 9 Replies
View Related
Apr 15, 2014
I have two columns, left is called OD right is called ID. In each OD there are multiple ID's but this is shown for example as:
OD-----------ID
254000----0
254000----127000
285750----0
285750----127000
285750----158750
304800----0
304800----127000
Therefore, showing duplicates for the OD. The required format is:
254000---0---127000
285750---0---127000---158750
304800---0---127000
This shows each OD in the first column (no duplicates) and the corresponding ID's in the columns on the right. The example is done with small amount of numbers however, the data I am working with consists of thousands of rows therefore is not practical to do by hand. Please see attached spread sheet for example template with both unsorted and sorted data shown.
I attempted to do this by using the following code:
[Code] .....
Attached File : testing.xlsx
View 3 Replies
View Related
Feb 16, 2009
I used the macros found at http://www.contextures.com/xlSort02.html to sort columns. This solution works fine, the invisible rectangle is really good to my interests. Here's how it works: macro #1 creates invisible rectangles at the top of the columns used, at row 1 where the headers are, and assigns a second macro to the rectangles. This second macro sorts the data table based on the column whose rectangle is clicked. The problem is that this solution is for data tables, it sorts the entire table and I want to sort the one, single and only column where I click the invisible rectangle.
View 2 Replies
View Related
Jan 5, 2013
Created the following macro, but doesn't seem to work.
Sub SortTable()
With Range("TC_Table")
.Sort key1:=Range("TC_Table").Cells(1, 1), Order1:=xlAscending
.Sort key2:=Range("TC_Table").Cells(1, 2), Order2:=xlAscending
.Header = xlYes
[Code] .....
View 7 Replies
View Related
Dec 1, 2006
I am trying to find a way to sort this info by column D but within the Groups shown. So, I want to sort rows 5 to 13, then rows 15 to 19 and then rows 21 to 29.
However I need to a macro to do this because the figures are updated weekly and the order will change. Furthermore, there could be new depts added when the figures are updated, so the row numbers may also change....
View 9 Replies
View Related
May 11, 2007
I regularly get a sheet that contains about 20 columns. I need to delete about 14 of them, and re-order the columns to a fixed order.
The columns always have the same headers, but are ofter in different positions (so I cannot hardcode the column location).
I have tried using the following, but fail on trying to use the result of the find in the selection afterwards.
I also do not know how to loop this through a list of column names (as I said, the input always has the same column names, and I always have to delete the same columns, and sort the remaining).
Sub Sort_My_Columns()
Cells.Find(What:="name1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
myRow = ActiveCell.Row
MyCol = ActiveCell.Column
Columns("myRow:myRow").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
End Sub
View 9 Replies
View Related
Jul 28, 2009
here i have results from a race. the racers have numbers in the format of a20,c51 etc
then the name of the competitor and car make model.
then some times that they run. in the format of 11.80 secs the following times have either thousands or hundredthousths of a second which i only want to comma seperate the 2 decimals before and after so a 12.3456 will be ,12.34,ignoring the 56 then the next result and so on.
so i want to comma seperate the a20,andrewspeck,vauxhalltigra,11.27,13.36,12.87,12.91 etc etc
now the name and vehicle will cause huge sorting head ache so leave those together i can seperate them manually.
.
A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128
2A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.80120
A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112
A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119
1A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.18111
A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113
1A8Gareth BirchVauxhall Nova12.1412.1411612.769912.18119
A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114
1A7Andrew SpeckVauxhall Tigra12.2612.26131
A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652
1A14Westan FrickerFiat Coupe Turbo12.7812.78110
A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109
View 9 Replies
View Related
Apr 11, 2014
I'm having an issue where I have a macro (see attached file) that is supposed to auto-fill and sort. Twhen it tries to run the auto-fill on a sheet in its blank/template state. It will take my formulas in columns AC:AZ and fill them up to the header row.
[Code]....
This code was hobbled together using record macro and my minor understanding of coding.
(CK) Macro issue.xlsm
View 7 Replies
View Related
Nov 21, 2008
In column A I have alpha-numeric data similar to:
2
A1
3
A20
A186
B4
B13
1
C5
C3
And I need to sort them so that they are like:
1
2
3
A1
A20
A186
B4
B13
C3
C5
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
View 6 Replies
View Related
Apr 11, 2007
I'm using the excel built in function to sort columns with my macro. However since my columns have 64,000 entries it takes a while for the computer to sort it.
View 14 Replies
View Related
Nov 21, 2008
In column A I have alpha-numeric data similar to:
2
A1
3
A20
A186
B4
B13
1
C5
C3
And I need to sort them so that they are like:
1
2
3
A1
A20
A186
B4
B13
C3
C5
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
View 5 Replies
View Related
Jan 10, 2007
I need a macro that will take a list of 42 names and 27 locations. The names are broken down into group of shifts, 1 shift, 2nd shift and 3rd shift. We need to perform audits on the 27 locations in groups of 2 people at a time. What I'd like is a macro that would take all the names by shift and assign 2 random people to a location. All 27 locations have to be audited each week (safety), and a report filled out and turned into our safety committee. I would like to post the audit sheet with the macro on our shared drive. Can anyone help me with this.
View 9 Replies
View Related
Jun 17, 2014
I have a table of 5 columns (each of 50 length) that is automatically refreshed (i am getting the data from web)after an interval of 5 minutes. I want the first column to be sorted alphabetically after every 'refresh' command, that has been executed by the excel. i have tried recording the macro and then running it on keystroke of 'ctrl + m'...the table surely gets sorted out but i can't be pressing ctrl+m every 5 minutes. auto-sort that is required to be executed after the auto refresh command..
View 6 Replies
View Related
Jul 12, 2009
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
View 12 Replies
View Related
Jul 26, 2014
I'm using macros to apply some complex sorting criteria. It is also applying some cell formatting specific to the sort criteria being applied.
At the start of each macro, I'm resetting all cell formatting to remove any formerly applied formatting that is not correct for the new sort criteria. Basic stuff.... works fine.
Now I need to ensure all formatting created by any macro-generated sort gets removed if a user applies a sort using any sort function from the Ribbon. (Reset all formatting prior to any 'manually applied' sort).
I can create the macro to reset the formatting, just not sure how to trigger it upon user action.
Is it possible to trigger a short macro to reset cell formatting when a user applies a sort using the Data>Sort, Data>Sort Ascending, or Data>Sort Descending commands from the Ribbon?
View 9 Replies
View Related
Feb 3, 2014
I recorded a simple Macro in Excel 2010 to sort data by the first Column, heading "Ref". I have assigned CTRL + e as the keyboard shortcut for this.
My sheet contains a number of hidden rows (as I hide rows when I have finished working on that issue).
When I run the macro, it does sort by the number of Column A, but also, the hidden rows are then shown again.
I realise I could just click on the A-Z sort button to stop this happening, but I really like being able to use keyboard shortcuts, which is why I tried the macro route.
I wondered if it is possible for the macro to ignore hidden rows?
Code:
This is the macro:
Sub SortByRef()
'
' SortByRef Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
[Code]....
View 1 Replies
View Related