Excel 2010 :: Sorting Different Sections Of The Same Worksheet Differently?
Aug 14, 2013
I am using Excel 2010 64-bit (with SP2 and all other updates installed).
I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)
I need to sort this worksheet as follows:
1. By background color, so yellow is on top, white is on bottom.
2. Then, within the yellow rows, data in column J.
3. Then, within the white rows, data in column A.
Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.
Is there a way to combine these steps, perhaps with a macro?
I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.
View 2 Replies
ADVERTISEMENT
Mar 2, 2012
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
View 5 Replies
View Related
Aug 27, 2013
I have a sorting question in Excel 2010. Attached is an example workbook with a simplified version of the situation.
I want to sort a table multiple times. I have a table with part numbers and alphanumeric locations (Row, Shelf, Bay, Slot). I have formulas that divide up the location into 4 separate columns to be able to sort.
The first sort I do is by location, which I can easily achieve. The issue I have is sorting AGAIN by part number, while keeping the original sort somewhat intact. If a part number shows up multiple times (i.e. in two DIFFERENT locations), the Nth instance might show somewhere down the list.
Is there a way to sort my table to where you keep it in location order WHILE accounting for duplicates, which I would want grouped together in location order? Please see attached file : SortExample.xlsx
View 8 Replies
View Related
Mar 25, 2014
I'm trying to add a sorting filter dropdown on some columns in a sheet. If I select the column, then use Data -> Filter, it adds the filter. on that column. But then, if I select another column, the "Filter" button in the tool menu is already selected, even though there is no filter for that column. If I click it, the filter on the other column disappears. Then, clicking it again adds a filter for every single column. It seems I'm not able to select f.ex. 3 different columns and add a filter to only those. It's either all or none. Is this how it's supposed to work?
View 2 Replies
View Related
Apr 10, 2013
Using Excel 2010
I have a workbook with multiple sheets.Sheet1 is named "UPC" and is a giant database for my workbook containing 80,000+ rows and 12 columns.
Sheet2 is named "Scan" and uses a bunch of index-match formulas. Basically, you scan a barcode and it auto-populates across the row all the information it pulls with that particular UPC from the "UPC" sheet. One of my columns, "QTY Scanned", in the "UPC" sheet has a countif formula to keep track of how many times that item was scanned on the "Scan" sheet.
Sheet3 is named "PSlip" and has a big button on it that you press and multiple macros go into overdrive. First macro takes all the rows from "UPC" with a quantity greater than 0 in the "QTY Scanned" column and copies it to the "PSlip" sheet.
Second macro takes that data and makes a pivot table.This is all clothing, pants, shirts, jackets, etc...When my pivot table is created, my Column Label is Size. When the pivot table is created, it automatically sorts it in ascending order, so it goes from numbers to letters.
This is great for pants because the size is in reference to a waist size; 24,25,26,27, etc...
HOWEVER, shirts, jackets, and other tops are not in a numeric size: they are in a Text format. XS, S, M, L, etc...
Sometimes we only scan pants for an order, sometimes only tops, sometimes both.
Is there a way for the pivot table to recognize when tops are in included and automatically sort the sizes?
I still want the numeric values at the beginning, but once the text starts it automatically sorts in this order:
XXS, XS, S, M, L, XL, XXL, MT, LT, XLT, XXLT, 1X, 2X, 3X, 4X, 5X
View 2 Replies
View Related
Jun 19, 2014
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 Related
Dec 29, 2012
I am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).
View 14 Replies
View Related
Sep 10, 2013
I have a database of names in one column in excel 2010 and over time I have coloured the text of some of the names green to show that they have been added to a new online database. I want to be able to count how many of the names have been coloured so I can see if it matches with the total number already added on to the online database. I have over 800 entries so I don't want to have to manually count them unless I find I have missed any. Conditional Formatting is greyed out (it is a Shared document so that might be why).
in creating an IF (text is a certain colour) THEN 1, 0 or any others ways I can find the total in that colour. I only have two text colours in my spread sheet, black and light green.
Example of Table.JPG
View 1 Replies
View Related
Oct 25, 2011
Find the sorting method of dates in excel 2010. I have dates in one column and i want to sort it, but I am not able to sort in ascending or descending order.
View 1 Replies
View Related
Jul 1, 2013
I wanna sort these numbers in descending order and related to each others in excel 2010.
H
G
F
E
D
C
B
A
1383.99
1533.954
831.197
1533.954
1533.954
1383.99
700
1533.954
1
[Code] .........
About descending and related to each others, I mean for example : #700 in column A,B,C,D,E,F,G,H places in the same row and the same happen to #1533.954 and others. About the numbers that are The One ( like 549.894), I need to put them in its column and a new row with empty cells in its other columns. In the end i want a table like this:
H
G
F
E
D
C
B
A
1533.954
1533.954
1533.954
[Code] .........
View 5 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
Dec 3, 2008
When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?
Original
Sub sessions()
Dim a As Integer
Dim x As Long
x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
Sheets(1).Unprotect Password:="password"
For a = 2 To x
If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = True
ElseIf Cells(a, "m") Cells(a, "d") Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = False
End If
Next a
View 9 Replies
View Related
Feb 25, 2014
I need a macro to sort on five columns (Column A, L, P, X, and Y) out of about 33 columns. Sort is all lowest to highest.
The code I have so far is:
Sub Macro1()
Range("A2:AG").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("L2") _
, Order2:=xlAscending, Key3:=Range("P2") _
, Order3:=xlAscending, Key4:=Range("X2") _
, Order4:=xlAscending, Key5:=Range("Y2") _
, Order5:=xlAscending
End Sub
However, I get an error "runtime error 1004 - method range of object global failed".
View 1 Replies
View Related
Aug 14, 2014
I have one master excel file (masterexcel.xlsx) and 100 small excel files. The small excel files are saved as M30.xlsx, M31.xlsx, M32.xlsx, M33.xlsx, M34.xlsx....
I want to open the M30 file, copy a certain section and paste it into the master excel file, close the M30 file whilst saving the new information in a file called recording. Then open the next file which is M31, copy a certain section and paste it into the master excel file, close the M31 file and then open m32 and so on....
How do I create a loop for the files so it automatically opens the files in the order M30, M31, M32.......and performs the aforementioned actions.
I have attempted the following for m30 but i need to make it a loop for m31, m32, m33, m34, 35 and so on....
[Code] .....
View 1 Replies
View Related
Nov 20, 2012
How can I rank with 4 criteria that are ranked differently? (Greater/ lesser etc)
Ranking - Online Spreadsheets - EditGrid
I had a look into this, but I got a bit confused with how isnumber, big, small and rank actually work
Using Excel 2003.
View 2 Replies
View Related
Jul 5, 2014
I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.
How do I count the number of candidates in the spreadsheet?
In Cells A6:B5, I have "Submitted"
In Cells A7-?? I have dates for those candidates that have been submitted.
Then a new section titled, "In Process"
A list of candidates are contained within that section.
Then a new section titled, "For Review"
etc.
There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.
I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.
(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)
View 4 Replies
View Related
Feb 13, 2014
When I publishing from Excel document to PDF it produces differently sized pages as shown in attached. Is there a way I can achieve a same size page view without having to change the font size and page layout of the source Excel document?
View 4 Replies
View Related
Apr 11, 2013
Scenario: Two computers running Windows 7 (64-bit Professional) with Excel 2007, same processor, same hard drive, same memory, same everything except the monitors - see below
Computer 1: HP LA2006x monitor (20" viewable diagonal) @ 1600x900 resolution, 91.79 PPI
Computer 2: HP LA2206x monitor (21.5" viewable diagonal) @ 1920x1080 resolution, 102.46 PPI
We have an Excel spreadsheet that the column widths on columns A through K must equal exactly 6, 1, 6, 1, 8, 1, 13, 2, 8, 1, and 35 respectively in order for the data to be imported into another program. These exact column widths are set on Computer 1 and the .xslx file is saved to a network location.
Computer 2 opens the .xslx file from the exact same network location. The column widths display as 5.86, 0.92, 5.86, 0.92, 12.84; not the exact columns listed above. So if anyone needs to export the data from Computer 2, they have to manually adjust the column widths or run a macro to auto adjust the column widths.
My question, is there anything I can do on Computer 1 or Computer 2 to make it so the column widths are saved and open correctly no matter which one is saving and which one is opening? The 20" monitor on computer 1 cannot display the 1920x1080 resolution... already tried that.
View 3 Replies
View Related
Dec 20, 2012
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies
View Related
Dec 11, 2012
Excel 2010 / Win 7.
I have some code that imports a csv file into a workbook. This works fine when i use a file named .csv. I was hoping to use a random file name (in this case .bmhs) so that we can determine which files we need to import.
The problem i have is when importing a '.bmhs' file the data comes in column A and is a comma seperated list. If i use .csv then the commas are used as the column seperators (which i need).
impFle = Application.GetOpenFilename(filefilter:="BMHS Files, *.bmhs", Title:="Select Import File")
would give me 1,2,3,4 all in column A
impFle = Application.GetOpenFilename(filefilter:="CSV Files, *.csv", Title:="Select Import File")
would give me 1 in column A, 2 in column B, 3 in column C and 4 in column D.
Is there any way that i can maintain using my own file name (.bmhs) but have excel treat it as a csv file.
View 2 Replies
View Related
Jul 30, 2014
I am using lesson 33 of Excel VBA and Macros with Mr Excel as my guideline. The coding is as follows:
Dim WBO As Workbook 'original workbook
Dim WBN As Workbook 'individual data workbooks
Dim WSL As Worksheet 'List of files worksheet
Dim WSN As Worksheet
Set WBO = ThisWorkbook
Set WSL = WBO.Worksheets("List")
Set WSD = WBO.Worksheets("Data")
[Code] ......
I have indicated above with ( ) the beginning of my problem.
From the workbook with the list of workbooks to open, in each case I want to refer to a worksheet in each workbook
named "Report" and grab all the rows with data exept heading (rows 1 & 2) and 9 columns.
How do I get WSN in the code above to include the worksheet named "Report" or do I need different coding. Using Windows 7 & Excel 2010.
View 1 Replies
View Related
Sep 30, 2012
I am trying to set up a simple sports picking list using Excel 2010. I would like to be able to carry out several filers and then extract results to a new sheet to allow printing.
For example, there are four available terms and I would like to be able to filter by term/sport and student. Ideally I would like to be able to add a command button once this is working.
View 5 Replies
View Related
Oct 31, 2011
I use Excel 2010, 64-bit-version with Windows 7. I have one workbook, where whenever I create a formula that references to one specific sheet, the Excel crashes and cannot be quitted even with Task Manager. I have to restart the whole system.
I do not have any worksheet events in either sheet. This is the same workbook that crashes if all cells are selected from the top-left corner of the worksheet.
I tried by deleting unnecessary COM add-ins but that didn't work.
View 1 Replies
View Related
Nov 18, 2011
I am using excel 2010, windows 7. When i make a new worksheet or update it randomly blanks out lines that already have information in them. It blanks out the row number also.
View 1 Replies
View Related
Apr 24, 2014
I need to create 3 or 4 detailed reports from an excel 2010 worksheet.
My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.
Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.
Same for any other consultants with a grand total at the end.
My understanding is that excel 2010 makes this process very simple .
View 3 Replies
View Related
Mar 27, 2013
I have a workbook with worksheet like 2a, 2b, 2c, 2d, 2e, 3a, 3b...etc Is it anyway i can make a tab which will hide a group of worksheet (like 2a,2b....2e) together and unhide all the worksheet with another click on it? becasue i got too many worksheet to show in the bottom..
View 2 Replies
View Related
Feb 16, 2014
I have an excel file with three worksheets in it. I can happily protect each sheet with a different password but what i want to be able to do is the following:
a. have a global administrator password for all the sheets - so i assume set the same password to protect the whole sheet
b. have individual passwords that only allow the user to insert rows and change data within restricted columns, i.e. can only edit columns A:Z and cannot manipulate any formulas within that range A:Z
I have excel 2010 if that makes any difference.
View 2 Replies
View Related
Mar 29, 2014
I need to create a separate PDF file for each Excel Worksheet, each worksheet has a different name.
I am using Excel 2010
View 1 Replies
View Related
Feb 21, 2014
where to find a spreadsheet which has a macro to perform the FFT on data with > 4096 points? It seems Excel's internal FFT is limited to this amount. I searched the forum and elsewhere on web and have not found what I am looking for. I wish to enter the data in a column of the spreadsheet, preferably in a named range such as "Data" and have a macro perform the FFT.
View 3 Replies
View Related
Oct 7, 2011
I am using excel 2010.
I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.
However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...
How can I update each sheet individually, without compromising the other sheets?
Excel is not responding to Shift - F9.
View 3 Replies
View Related