I have two sets of data (delivery destinaition & profit) for 2 separate depots (A&B) delivering to 3 countrys (1,2,& 3). I have used a clustered column chart to show the number of deliveries to the different countries by each depot. The problem I have is that I want to add separate lines (one for each depot) showing the profit figures for each destination (on a secondary axis).
View 4 RepliesI need to show a chart with new and return visits 2 years side by side. I also need to show a budget line and 2 seperate utilization lines. How to show this.
View 1 Replies View RelatedI would like to be able to add a line (a trend line) to a clustered column chart. The attached jpg (picture copied from Excel Charts - Easy Excel Tutorial) shows the general structure, with the red (poorly) hand drawn lines representing what I want to add to the chart.
If using Excel's Trend line functionality is not an option, I have already calculated the trend points adequately, so have the data to add as an additional series or combine with an existing series. (using Excel 2010)
I have thousands of lines of data that are of the following form:
Jackson, TN 9623 BCBS TN 98 UnitedHlthCare 2
Jacksonville, FL 3577 Aetna 47 BCBS FL 37
Knoxville, TN 3796 Cariten 44 John Deere 41
Lakeland-Winter Haven, FL 2165 UnitedHlthCare 31 Aetna 29
I need to separate these lines into columns such that, for example, the first line would be in 6 columns: (Jackson, TN) (9623) (BCBS TN) (98) (UnitedHlthCare) (2)
The delimiter does not work because some of there is no one character that always separates the text and the numbers. I've tried going through in word and typing something like '%' where I want to separate, but with thousands of lines of data that is extremely tedious.
Is there any formula I can use that would be capable of solving this?
I am trying to show some trends, from data taken on a monthly basis. The attached spreadsheet is an example of what I have:
The first two columns show data for Month1. The next two columns show data for the last 2 months (Month1&2). I was thinking of using a line graph, and showing Month1 and Month1&2 on the X axis. At time Month1, I'd have 4 different data points, for hw, sw, fpga and chip. At time Month1&2, I'd show the cumulative data for those same 4 categories. Over a period of time, we could see whether one area or another (hw, sw) keeps getting worse. How can I do this via pivot table?
Two graphs with different scale x-axis.
Graph No. 1 with smaller time frame, Daily.
Graph No. 2 with larger time frame, Weekly.
Objective: Add vertical line to mark the same times in both graphs.
Trouble is both graph already utilise the secondary axis. VBA is allowed.
Plotting of bar and line graphs given an arrays of x and y values in VBA? I want to plot the rDateRange and rOEERange.
Code:
Dim rInitialDate As Range, rFinalDate As Range, rDateRange As Range, Calculation As Worksheet
With Worksheets("Calculation")
Set rInitialDate = .UsedRange.Find(What:=DateValue(InitialDate), After:=.Cells(1, 4), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext, SearchFormat:=False)
[Code] .........
There were 2 macros. One printed all of the defined ranges (40) on separate pages, and the other printed all the graphs ("charts" - 39) on separate pages. I tried to combine the VBA code to print each range and then the corresponding graph. Everything is still on separate pages, but it saved time because I didn't have to collate after printing. It seemed to work. Then I tried figure out if I could print them all to a specific tray of the printer as set in the workbook or as the printer default. Now both the combined macro and the original macro are giving errors.
Is the code I have correct to do what I am trying to do (print each range and then the graph all on separate pages)? Is there any way to put the output tray choice into the macro?
Sub Load_Data_Report()
'
' Load_Data_Report Macro (print all tables & graphs)
' Macro recorded 12/21/00 by xxx
'
' Keyboard Shortcut: Ctrl+j
'
[Code] ........
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
I have a column who's values I would like to graph against many other columns. I was wondering if there was something that would let me do that in one go.
View 1 Replies View Relatedi have attached a sheet where I have 4 graphs on one sheet. I prefer this to an overlay. I would like to add a 5th graph but it would make all the the graphs too small. Is there a way to make the graph background longer. For instance, extend the length of this so that I can put multiple graphs on it and then just scroll from top to bottom to review them? Also, how do I copy and paste these individual graphs into a newly created graph. copy/paste doesn't work on these?
Boiler.xls
I have a database of address which have been inputted into text wrapped cells like this:
Line1
Line2
Line3
Line 4
Can I turn these lines into columns?
Actual
Result
london#123;new york#34;
london;
new york;
delhi#145;chennai#54;
delhi;
chennai;
hyderabad#748;
hyderabad;
[code]....
removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.
I was recently upgraded from Excel 2003 to 2010. I have some code that creates a 3d clustered bar chart, previously the chart's rotation was 0 and now it seems like Excel wants it to default to 15 degrees. I'd like to change the rotation back to zero, but Excel doesn't seem to see the chart.
I recorded a macro, inserted the relevant recorded code in my routine, but Excel doesn't seem to like it.
' begin code ------------------------------------------------
Dim Rng As Range
Dim cTyp As XlChartType
Set Rng = Range(Cells(2, 1), Cells(11, 2))
cTyp = xl3DBarClustered
Charts.Add
[Code] ....
I'm getting a Visual Basic error that says "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found."
Excel seems to know the chart name, but doesn't seem to think there are any shapes (initially, a loop looking at the names of shapes in the shapes collection returned nothing; printing ActiveSheet.Shapes.Count returns zero.)
What is the correct method of rotating a 3d clustered bar chart?
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
I have four columns of data, as follows:
label 1, value 1, label 2, value 2
I need to create a formula in the fith column that for each line will tell excel to:
look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.
I have tried doing this with SUMIF but am getting nowhere fast....
if anyone know if there is a formula that can separate a name which sometimes have two words or one or two space. For example a formula that can separate a name by first space in one column and the second half in the second column.
example: David T Blain
name is in column A,
column B is David T
column C is Blain.
I don't think this is possible with a formula, but if you think it is let me know.
I think it would be more efficient to do this with a macro, could you please see if you could provide a macro that can do this.
Need code that can separate the date and time in the following format into TWO different columns - one for date, the other for time?
Dec 15 2006 0:10 am
Dec 15 2006 0:20 am
Dec 15 2006 0:30 am
Dec 15 2006 0:40 am
how to separate .csv data into different columns in excel?
e.g this .csv data
2012.09.07,07:00,125.78800,126.12500,125.76800,126.02300,6664
2012.09.07,08:00,126.02100,126.19800,125.93900,126.05000,4707
2012.09.07,09:00,126.05100,126.11300,125.96700,125.99800,4178
2012.09.07,10:00,126.00000,126.02900,125.71700,125.77800,6389
2012.09.07,11:00,125.78300,125.91400,125.62800,125.66400,5388
2012.09.07,12:00,125.66300,125.86900,125.04100,125.12300,10447
[Code]....
I have some data which i want to split, i have tried " Text to columns "on excel but couldnt find a way it works.In one cell my data is exactly like this:
Name of product
Code of product
Loc.: here is the location
expire date:
I would like each one of these itens on one column, like
A b c d
Name of product code of product Loc.:here is the location expire date:
I have a spreadsheet of products that are listed in column A and another list of products in column B. I need to find the difference in price in a percentage for each product. My problem is the lists differ a little bit in that there are more products in column A so they are not lined up correctly. Is there a way to have excel look in column A and find the match in column B and then line them up so the matching products are in the same row? This will cut out a lot of time having to do the formula in each cell rather than autofilling the formula.
View 1 Replies View RelatedI have a huge data and want to know the best VBA or formulas to separate to consecutive columns.
Example :
BERNATTE QUIGLEY 10 WESTBOURNE ROAD STOCKTON HTH WA4 6SE
J QUINN 100 CHRISTOPHER TAYLOR COURT FLAT 18 B30 1ER
JOAN QUINN LITTLE COURT CT6 6PT
PAMELA QUINN ROAIN BANKS COTTAGES CT4 5PU
[Code] ..........
I am working on a workbook, it's not my workbook so I can't change how it's laid out.
I am trying to figure out a way that excel will check 2 different columns for a code that is manually entered, then enter the codes description in another cell.
It's basically a deposit and expense ledger.
One column has expense codes and names, the other deposit codes and names.
I want to be able to enter the code letter into a cell in the ledger and excel will enter the code description into another cell.
I have two columns in my excel sheet, the first column has the Second name, First Name and the second column has the Date of birth.
Now what I'm after is two columns really, the first column should show the Furst name Second Name (space in between with no Comma) and the Second column should show the First Initial from the first name, the first initial from the second name and then the date of birth.
I'd like to copy information from a PDF into a worksheet. The information looks like this:
1 PAOZZ 5310-00-918-0482 80205 NAS1291-7 NUT.............................................................................. 1
2 PAOZZ 5305-00-995-2125 80205 NAS603-7P SCREW.......................................................................... 2
3 PAOZZ 5305-00-866-0937 80205 NAS603-8P SCREW.......................................................................... 14
4 PAOZZ 5306-01-106-8238 80205 NAS6603-3 BOLT ............................................................................ 8
When I paste it into excel it all gets entered into one column, I'd like to spread it out over 7 columns. Is there an efficient way to do that?
I have a spreadsheet with these columns:Group_Name
Contact Name
Company Name
Contact ID
Situation:
The Group Name Column has the name of the group that the Contact Name is a member of. The Contact Name could be a member of many Group Names and therefore there may be many rows of the same Contact Name one with each Group Name that they are a member of. I have manually created separate columns for each Group Name in the spreadsheet that is attached.
Goal:
I would like to have a macro that will look at the Group Name column and create a new column for each distinct group. I then would like the macro to go down the Contact Name column (I believe that I will have to have it sorted)and look at the Group Name that is listed in that row and put "True" in the Group Column that was created in the above step. Then, go to the next row, if the Contact Name is the same, look that the Group Name column and put "True" in the corresponding Group Column in the FIRST ROW OF THAT CONTACT NAME. Then Delete that row.
I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:
B
B
B-A
B-A
B-V
B-V
Column B contains:
THR
THR-MATT
HF-MATT
HS-HS-THR
HS-MATT
I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.
I have a column of first and last names that sometimes look like this: Smith, Michael D and other times look like this Smith, Michael (no middle initial) I would like to separate them into two separate columns, one for First name and the other for last name. I have no use for the middle initial so that can be ignored.
View 4 Replies View RelatedDuring a Webinar I recently attended you said that inserting empty columns between number-columns just to keep the underlines from touching was a bad idea - however, you never said, or at least I never heard you say, what the better way to do this is - in our situation the lines absolutely cannot touch.
View 9 Replies View Related