I have a formula which sorts data so all data that has EUSTON in column C will be shown first and then then all data which has EUSTON in column D once its been sorted.
=IF(C1="EUS",0,1)
What im trying to do is to be able to add BLETCHLEY so it does the same thing.
I have a macro built that sorts my data by column in descending order depending on what column of information is selected from the drop down menu.
However I need the macro to sort column F in ascending order, and continue to sort the other colulmns in descending order when selected.
I can't get my macro to switch the sort from descending to ascending order for this one column. Looking to see if this is possible, and if so what macro formula I would use.
Here is what the macro i am currently using:
Sub ddSort_Click()
Dim iDdSortVal As Integer
Dim CurrSheet As String
Dim lFirstSectionRow As Long
Dim lLastSectionRow As Long
Dim strCol As String
Dim lRptHeaderRow As Long
Dim iRptMeasColumn As Integer
CurrSheet = ActiveSheet.Name
[Code] .......
I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows:
I have a template with formulas that cannot be changed (I cannot paste special values, or clear the cells). Column A includes HLOOKUP formulas for cells A14:A120. Column B includes VLOOKUP formulas for cells B14:B120. The different data sets I pull in have different numbers of lines, which is why I need to maintain the formulas. However, I cannot seem to find a way to accurately sort column B first from smallest to largest and then column A from smallest to largest. In all data sets there will be at least a few blank lines that I need to move to the bottom. However, due to the formulas within the cells (column A returns a value of 0 and column B returns a value of “ “), excel does not recognize them as being blank. Here is a sample data set.
Branch
S&C Group
77770000583-DOWNERS GROVE BRANCH
MWBF2
77770000585-CHICAGO RIVER BRANCH
MWBF1
77770000587-WEST TOWN BRANCH
MWBF1
[code].....
In this case, I would like St. Charles & Algonquin to be at the bottom because column B is blank.
Below is an extensive macro that basically writes data from a user's template to a certain data sheet (in the form of rows) depending on their "service group." There have been 3 separate instances of a user saving data and instead of adding rows onto the bottom of the entire list of data, the sheet only has their data and no headers/filters. I believe it is somehow deleting all the previously written data that was on that sheet.
I should also mention that this is a shared workbook. I specifically created separate sheets for each "service group" to eliminate the possibility of users overwriting each other's data (Perviously 60+ users were saving to the same sheet and running into conflicting changes errors. Now the max users writing to one sheet is 10-12). Since it is not occurring every time, I am not sure where the error is. Could it possibly be in the sort or delete sections in bold below?
I am using Excel 2003.
Code:
Sub SaveData_Test()
'---------------------------------------------------------------------------------------
' Procedure : SaveData_Test
' Author : Julie/Cecil
' Date : Fri, 1/13/12
' Purpose : Modified Julies code to capture date entries
' associated with hours logged per category.
' Check notes attached to "sOp" string comment
' for additional details.
[Code] ..........
I am generating sets of data in Sheet2 to be used for Sparklines in Sheet1. These Sparklines will sit in Column D alongside other pieces of data. The data needs to be sortable by Column A which is a list of Cities.
When sorting my data, Z-A for example, all the relevant data sorts just fine but the Sparklines do not move with the rest of the data.
Is there a way to make Sparklines move with Column sorts?
I have a file called "registrantExport" with "_great north", "_new england", "_new england", etc. I'm trying to sort them by Division and then by Last Name. Here is the macro I've tried to make, however it doesn't work properly, as it only applies to great north. How could I make so that it works for any worksheet I apply the macro to?
Code:
Sub macro1()
'
' macro1 Macro
'
'
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Add Key:=Range( _
[Code]...
I have a connection to an SQL database using microsoft query.
The query definition includes: ORDER BY AAStocktrans.Code But I need a two level sort along the lines of:
ORDER BY AAStocktrans.Code THEN ORDER BY AAStocktrans.Date but obviously that is not correct - cannot use THEN. what I do use to make this work?
I'm trying to create a very simple map.
I've got three columns.
Member, X, Y
Naturally, the first column contains someone's name.
The other columns contain XY coordinates.
These coordinates start at 375 and end at 499 on both axes.
I'm needing the points to show the member name and xy coordinates in the popup when hovering over the point.
How do I accomplish this?
Here's some sample data.
MEMBER X Y
JOE 397 397
JIM 421 384
JANE 467 406
JEFF 389 395
I need a macro or some idea on how to sort the following numbers (I hope this makes sense!)... The problem is with the zeros get sorted to the top (or even if I have blank cells sorted to the bottom) and I need them to ignore the zeros and sort according to the value in their column but not any other column. However, I need them to be sorted in order from D, C, B, A.
A B C D
0000
4000
101000
0540
0050
6660
ie I need them in the following order.
A B C D
0000
4000
0540
0050
6660
101000
Therefore they need to sort based on the following columns.
This is because I am charting the values in a column clustered chart that need to be in ascending order.
I've been trying to make a "pivot table" of sorts, but I'm having trouble because I need to have a few columns - I wanted to have a "planets" column, then a "Weight" column and a "mass on planet", so that when a weight is entered the "mass on planet" comes up
I've worked out how to use the drop down list so only one planet comes up at a time, but I still can't work out why I can't have three columns - it just doesn't let me make an empty column that can be filled out.
Step-by-step instructions would be GREATLY appreciated as this is my first attempt at excel.
On another note I have a task to use excel "creatively", any ideas? I need to put in graphs, is it maybe possible to also put in GIF's?
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
End Sub
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies View RelatedEssentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112
Value 2345678910111213
And get it like:
Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.
=IF(E7=F7,0,IF(E7>F7,E7-F7-INT((WEEKDAY(F7,2)+E7-F7)/7),F7-E7-INT((WEEKDAY(E7,2)+F7-E7)/7))).
I have this formula in a cell. f7 is the control cell. if f7 has nothing in it i dont want to do the formula, and if f7 has data to do the formula, is this possible to do?
I need a formula to automatically transfer data in a column into another column, omitting cells in the 1st column that do not have data in them.
So, for example, transfer the data in column "A" below to column "C" below omitting any blanks when the formula automatically copies data over:
Example Spreadsheet.xlsx
Note: I know the syntax below is not how you would enter forumlas into Excel but I am using it to quickly illustrate what I'm trying to do and need help with. A cell not in column D, E, or F contains a formula of the form: F=IF(D>0, D/E, "blank") .
Note: If a cell in column D>0 (eg. D5>0) then the cell to the right of the selected D cell (eg. E5) will also be >0 ; otherwise, both cells will be empty.
This is what I want to do: If cell D?>0 , then F?=D?/E? ; otherwise, F?=empty .
Example 1: If D5>0, then F5=D5/E5 ; otherwise F5=empty .
Example 2: If D7>0, then F7=D7/E7 ; otherwise F7=empty .
Etc. I want this to apply to all rows.
I cannot enter the formula directly into the F? cell because sometimes I will need to enter data into cell F? manually. When I need to enter data manually into F? this results in the formula being overwritten by the new data and this means that if I ever needed to have data calulated based on the formula F=IF(D>0, D/E, "blank") I would have to re-enter the formula from scratch over again; obviously this is a nusance.
I have bunch of column Data in Column D:H
My data starts in row 5. I need a excel formula to add data every 63rd row for column D:H.
So for example for column D, i would have D5+D67+D129 and so on until D687.
formula to add up certain data.
In the example below, in Column A there are "T"s and "C"s. In column B there are distances.
In Column C, I need to sum the distance travelled since the last "C" on each row with a "T".
Sheet1 ABC1TypeDistanceTotal travel since last "C"2C 3T334T475C 6T227T468T289T72410T41911T52412C 13T2214T46
Is there a formula I can use in data validation that will only allow a six digit number and not allow other characters such as a /?
I tried =LEN(A1)
The data from the Data sheet is copied over to the Report sheet with formula.
Basically, what I need to do is If statement:
-when there is no any data in csv file (so the Data sheet will be empty);
-the range (A7:N1000) to be hidden (on the Report Sheet)
-the Report sheet to be saved
and also...
I would like to ask, how to select from the Data sheet, the rows with Data only. I copy them over with formula instead of macro- but would like to hide all the empty rows on the report.
Option Explicit
Sub Update_Report()
'
' Macro1
Dim extract1 As String
Dim dReport As String
Dim rSheet As String
Dim dSheet As String
extract1 = "LeadSheetAll_0001.csv"
dReport = "Appointments.xls"
rSheet = "Report"
dSheet = "Data1"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
I have travel data for hundreds of cars. Col A has the car ID number. Col B has the date. Col C has the distance of a journey. Column D, has the total distance traveled on a given date for a given car.
In Column E, I'm trying to calculate the remaining distance to travel "prior" to the journey being made.
I a have attached an example. [URL] ....
So in column E, prior to the first journey (9km) being made there is 19 km still to travel in the data. And for the second journey the is 10 km left to travel.
I've got a table containing different codes and their values:
AAA 1
AAA 1
AAA 5
XCC 2
XCC 1
WTY 7
WTY 2
...
I'd like to group them so the code shows up just once whith its' value summed up:
AAA 7
XCC 3
WTY 9
...
See the file attached.
I have lots of data.What is the best way (formula) to add I10+M10+Q10+.....DY10, (Every 4th column)
View 9 Replies View RelatedPlease refer to attached sheet.
I have number 10000 to 10200 in column D.
I need to use IF Statement and add corresponding number in Column A as follows.
IF D2 = 10000,10006,10012,10018,10024 and so on then A2=20002
IF D2 = 10002,10008,10014,10020,10026 and so on then A2=20003
IF D2 = 10004,10010,10016,10022,10028 and so on then A2=20004
(Basically there is increment of 6)
Once i have formula for A2 then i can copy down the column.
Book1.xlsx