Excel 2010 :: Sorting Multiple Times
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
ADVERTISEMENT
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
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
Apr 11, 2014
I am using excel 2010 and I am trying to find a formula whats going to give me added break with a set time but anything over a set time doesn't. I want it over 10:58:00 and under 11:32:00. I've got the numbers and tried with a formula but all whats happening is that all the data is adding 30mins break to the end time.
The sheet is using live times and data so the start times vary as do the due time.
View 1 Replies
View Related
Apr 2, 2014
When I run macros usually they're fast, but occasionally will run very slow. An example macro I have is pasted below. It's just a simple macro that pastes months. If I am pasting on top of a cell that contains a month it pastes the rest of the months that follow.
Most of the time this macro runs very fast, but occasionally it takes about 1-2 seconds to run, and it is a very simple macro! It is not macro specific, as all my macros will be slow. I''m using Windows 7 with Excel 2010. I'm only using 23% of my CPU and 4.5GB of my 16GB of RAM, so I don't think it's a hardware thing.
Is there some kind of condition that occurs that will make excel run macros slower?
[Code] .....
View 4 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
Jul 2, 2014
I work for a freight company and have been trying to create a macro for a spreadsheet that I have to create every single day. I pull a report from a program called the AS400 and it sends me an excel spreadsheet with information about certain shipments such as close time and arrive time (which are in military time such as 16:00) along with driver names.
Anyways what I need to accomplish is to highlight the entire rows font in red if the driver arrived an hour or more past there close time. When I get the spreadsheet the military times are not showing in the HH:MM format under format cells. So I select the HH:MM format and try to do a formula such as Arrive time cell (M1) - close time cell (L1) to get the time difference it shows as #VALUE!. I've tried to convert it to different time formats and nothing works. The values just stay the same and will not allow me to do calculations with them.
At first I thought this was because the program was sending those values as text. I tried to use the date>text to columns>finish button on the columns but did not work. I've tried to remove the":" then use the HH:MM cell format but still wont work. I've even tried a time value formula to try to convert the values such as =TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).
But nothing works it will not allow me to add or subtract from the time values no matter what I do. I am running excel 2010 on Windows XP the english version.
View 9 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
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
View Related
May 1, 2012
I have a sheet that contains the following columns:
Invoice, Document #, Date, PO #, Part #, Part Description, Quantity, Net Amount
Based on the quantity in the row I need to copy the row, and insert it n-1 times. So if the quantity is 5, I need to copy and insert the data below the original row 4 times for a total of 5 rows of data.
I plan on firing the macro with a button as the data will change month to month.
Using Windows 7 and Excel 2010
View 2 Replies
View Related
Jun 18, 2012
I need excel 2010 to automatically save at 09:30 and 19:45 every day monday-friday. Also need the file name saved to reflect the time and date it was saved - if possible.
My PC locks after 10 minutes of inactivity - unfortunately this has to happen because of where my PC is situated.
View 1 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 19, 2013
I have a single work book with 8 sheets (I am using Excel 2010 BTW) and I am trying to find a total of times a word appears across all the sheets in column "C"
I found this formula on another thread. =SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C1000"),"="&H3)) with an example. I made the changes that I needed for my purposes
This worked but only after I renamed the sheets to Sheet1, Sheet2, etc.
Is there a way get the same results from the above formula if all the sheets are named after our reps? Example: sheet1 is named Dan, Sheet2 is Nick, etc?
View 2 Replies
View Related
Mar 20, 2014
Excel 2010 - I need to create a graph that shows a week's use of a sportsground showing what sports were played, on which days and between which times. I thought I could show the times on the vertical axis, days on the horizontal axis and then the sports played in those times, however I'm unsure as to how to get the graph to recognise the times.
I've attached an example of some data and how I'd like to see it, I'm just not sure how I can do this using the chart set up!
Attached File : Graphs.xlsx
View 10 Replies
View Related
Feb 9, 2014
How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
298
2/9/2014 12:28:02 PM
37
77
[Code]....
View 6 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
May 6, 2014
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
View 5 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Jun 17, 2014
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
View 3 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
May 28, 2014
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
View 2 Replies
View Related
Aug 13, 2009
I have a column of different "finish" times (hh:mm:ss). I would like to have the adjacent column look at all of the "finish times" and post their ranking from 1st place to last.
View 9 Replies
View Related
Apr 8, 2014
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
View 7 Replies
View Related
Jul 23, 2014
I am trying to develope a userform in EXCEL 2010 that has a couple of comboboxes that pulls data from a specific worksheet. The first combobox is initialized with data from column "A" of the worksheet, which I can get to work perfectly. But what I am having trouble with is the second combobox, the data that needs to populate combobox2 is dependent on combobox1 results which are the column headings ("B" thru "E"). What I want to do is search row 2 find the result from combobox1 and populate combobox2 with the data below that result. Below is my data that I am working from.
TableData.jpg
View 9 Replies
View Related
May 21, 2014
I'm trying to create a formula that states. If tank 1 maximum capacity is less than 500,000 and the difference in inventory is greater than the absolute value of 10% or 20,000 or if the tanks maximum capacity is between 500,000 and 1,000,000 and the difference in inventory is greater than the absolute value of 20% or 100,000 or if the maximum tank capacity is greater than 1,000,000 or the difference in inventory is greater than the absolute value of 30% or 300,000 then Y OR N.
I figured out the absolute value portion of the formula but I'm not really sure how to combine that with the tank maximum capacity piece.
=IF(OR(ABS(T14)>10%,S14>20000),"Y","N")
View 7 Replies
View Related
Aug 20, 2014
I am using excel 2010.
I have a spreadsheet with the following:
Column E is a product. If that product is ordered, any character is entered in that cell
Column F has a due date
Column I has the received date
What I want is to count the number of cells that have any character in column E AND the received date is later than the due date
These two formulas are working fine alone but I cannot get them to work together.
=SUMPRODUCT(--(F:F<I:I))
=SUMPRODUCT(--ISTEXT(E2:E1000))
I have tried all kinds of tweeks to the following to no avail:
=SUMPRODUCT(--(F:F<I:I),--(ISTEXT(E2:E1000)))
View 4 Replies
View Related
