Clubbing blocks of Data for two entities in a Column Chart
I need to show graphically the comparison between two LOAN amounts for a Housing LOAn..
I am almost done with the MATH part part of it, however need to compare the results Graphically...
I have two Blocks of DATA primarily: ...
Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.
For Example:
Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................
I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here...
2
2
2
2
33
33
33
4
4
4
4
4
4
etc.
Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no.
so that it looks like this
2
2
2
2
46empty rows here.......................
i think this will be simple for most of you. i need a macro that will move blocks of cells from one column into the succeding columns. say i have one column of 1000 values and i need to break that up into columns of 50.
View 6 Replies View RelatedI have 250000 lines of data and at the moment they are in seperate blocks of different sizes, and seperated by 5 blank lines.
For Example
112
1523
523
1523
*5 BLANK LINES*
12
23
*5 BLANK LINES*
344
4563
etc.
What I would like to do is give each block a number.
1 112
1 1523
1 523
1 1523
*5 BLANK LINES*
2 12
2 23
*5 BLANK LINES*
3 344
3 4563
The lines in between will come out eventually I just need them there as they are difineing the blocks of data.
I have the follwing data in A1:T17
3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
..................
I want to compare the first set of 20 numbers (A1:T1) with the second set (A2:T2) and check how many numbers match. ** If the matched numbers
are >=10 ** then list them to the right of cell W1. In this example, 10 numbers matched are 4,9,10,21,35,47,64,72,74,75. List them in W1:AF1.
Now compare (A1:T1) with (A3:T3). In this example, 11 numbers matched 4,9,10,21,33,41,47,57,6,72,74 list them in W2:AG2.
Now compare (A1:T1) with (A4:T4). 10 numbers matched 3,4,10,11,21,32,33,35,60,69 list them in W3:AF3.
Compare (A1:T1) with (A5:T5). Here only 5 numbers matched 9,21,64,69,75 (Which is ** less than 10 ** - does not match the criteria) DO NOT LIST
this.
Compare (A1:T1) with (A6:T6). 11 numbers matched, 3,4,7,10,21,33,37,47,57,69,75 list them in cell W4:AG4.
.................
Sub Compare()
Dim r As Integer, rr As Integer, c As Integer, cc As Integer, opc As
Integer, opr As Long
opr = 1
For r = 1 To 16
For rr = r + 1 To 17
opc = 23
For c = 1 To 20
For cc = 1 To 20
If Cells(r, c) = Cells(rr, cc) Then
Cells(opr, opc) = Cells(r, c)
opc = opc + 1
End If
Next cc
...............................
Next r
End Sub
The above code runs fine but If I increase my rows from 17 to 1200 it takes 3 1/2 hours to complete. Can it be reduced?
I would like to copy specific cells (all in one block) to another worksheet, then do a bunch of other things with the data (these steps work already), then go back to the original worksheet and copy the next block of data, repeat about 50 times, then stop at the end of the list.
I can't attach a file as I can't get it under the file size limit but the rows to move (just to any worksheet in the workbook, from there I can manipulate and move as required) each time are row 1 plus all those pertaining to each subject group. So for group 1 it would be row 1 plus rows 6 to 28 - for group 2 row 1 plus rows 29 to 51 - group 3 row 1 plus 52 to 74 etc. If I can just get looping through chunks of data and moving them around under my belt
I have a list of data (as shown below under Input), which I would like to sort them according to their block number (as shown below under Output). The hurdle is the sorting should be able to cater for missing fields. Also, note that the sector can be recurring, that is "P,P,Q,Q", and then follow by "P,P,Q,Q" again.
INPUT
Block No.SectorData
11P19
22P
33P39
41P18
52P21
63P38
75P22
85P24..............................
I have a series of spreadsheets that represent the rosters of teams in a league. At the end of the league season, some teams qualify to advance to a final event, and some do not. I start with a file of all the teams for that level of competition. This list comes from a program that creates it's output in an excel '97-2003 format (and I can't change the way the data comes out of this program). I modify the data a bit by removing data from merged cells and rename that worksheet a 'Raw Data'. In the past I have manually copied and pasted the data for the teams that advance to a sheet called 'Roster Data'. I am hoping there is a way to automate this part of the chore.
There must be 6 blank lines between the team names on the 'Roster Data' tab, but some teams have more members than others, so they are not spaced the same on the 'raw data' tab. Is there a way to select teams from the 'Raw Data' tab and have only the selected teams show on the 'Roster data' tab, AND have the spacing become uniform?
I have around 50,000 records in a data file within which groups of records are contained in blocks delineated by date/time. e.g.;
A B C D E
10/03/2008 13:15 a 100.0
10/03/2008 13:15 b 2.8
10/03/2008 13:15 c 50.5
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:25 75.2 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 3.5
etc
As you can see, while the file is ordered by date/time in Column A, the values in column E are randomly ordered.
My objective is to maintain date time order but sort the values in Column E into an ascending order within each block. So, for example, using the details above I would want it to look like;
A B C D E
10/03/2008 13:15 b 2.8
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:15 c 50.5
10/03/2008 13:15 a 100.0
10/03/2008 13:25 3.5 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 75.2
Note that there is data in columns B to D which would need to be sorted in line with Column E. So, all records in the block, cols A to E are sorted to give the above order for each time block.
My problem is that I'm not sure whether a basic sort can do the job or whether a macro is needed. I'm using Excel 2003.
I have a spreadsheet where data is pasted on from spreadsheet supplied by various external companies. One of the main problems we're having when validating the data is an incorrect NI Number and I'm hoping to find a way of getting this automated.
What I would like to do is, when a block of text is pasted into our template, a block of code to look through the selection and check column S for a valid format NI no (eg AB123456C). I have some code (below) that works when changing an individual cell, but nothing that works when pasting in blocks of text.
Private Sub Worksheet_Change(ByVal Target As Range)
stringvalue = Target.Value
statuschange = 0
Application.EnableEvents = False
'Check for text only cells
If Target.Column = 19 Then
I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.
Part 1:
I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)..................
I have a column of data that is blank-delimited into five blocks. The size of each block will vary, since the data comes from a web query. A blank (empty) cell tells me where a block ends and the next one begins.
I would like to set up five dynamic names (e.g. Block_1, Block_2 and so on), each of which will reference the corresponding segment of data.
What would be an elegant way of defining the dynamic names?
I'm doing a termination report at work and I need to create a pie chart based on data in a selected column. Ive never used excel charts before but I cant get the pie chart to display the information that I want.I created a sample workbook. Its very a simple column of data. I would like a pie chart based on the percentages of "yes" to "no". I havent been able to get it to work.
View 6 Replies View RelatedI have a very simple template which plots out means and standard errors of a treatment structure. The input is from an analysis of variance so I want to have the significance levels dispalayed in the title. This is simple for a single col of data but when there are more then one it doesn't use a cell to populate the chart title.
From the sheet I've attched the first and second chart have dynamic titles, if you change cell F10 from 0.364 to 0.001 the title on the second chart will change to "Input (**)" as it uses M19 for the title. The problem is that for the 3rd chart titled "CV*TR (NS)" any change has to be done manually (change cell F15 to 0.04 and this changes cell J43 to "CV*TR (*)".
example.xlsx
I'm using a table with dynamic data to populate a column/line chart.
The data is based on monthly targets achieved and forecasted. With each month, the information will update. The graph needs to have only the current month and future months to be displayed and I don't want to include the previous months information in the chart. The information in the chart automatically updates and loses the previous month's data. Unfortunately, the graph plots the blank data and has a blank entry on the graph. This means that the information I need starts in the middle of the chart and has a line that shoots up from 0 to the current month's value.
Is there any way that I can omit the month altogether?
The data source I am using is below:
MonthTarget for month* (examples)
October 100,123,669
November 125,154,586
December 150,185,503
January 175,216,420
February 200,247,337
March 225,278,254
April 250,309,171
May 275,340,089
June 300,371,006
As you can see, July August and September are not included. I want to be able to start the graph from October in the above example. In the following month, I want to use the same data but October Figures will not be required. How can I achieve this?
I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.
However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .
VB:
'activate sheet and chart
Worksheets("Home Page").Activate
ActiveSheet.ChartObjects("Histogram").Activate
'set variables for chart
With ActiveChart
[Code] .....
Im plotting data on a Line-Column 2 Axes Chart which works really well with 4 data series (2 column, 2 Line) but when adding a fifth data series, the fourth disappears from view. I can see its still there cos I can display the values but I cant get the line to appear. The values for the 5th series are also similar to other series values so its not like the chart cant display it....are there limits to the number of series on such a chart?
View 9 Replies View RelatedIn my table I want to create a user form where the individual would enter a number which would correspond for the Header Row.
and
The user would also enter a Number that would correspond for the first main column (Column B).
Based on the numbers entered, I want to find the next highest number.
For example if 61 is entered 66 would be chosen.
-Same would be for the numbers entered to find the column.
Based on the numbers entered I would like to find the intersecting cell.
Basic code I can embed in a user form?
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies View RelatedI am trying to overlay a line chart on top of a stacked column chart. The stacked column chart is a chart where the x axis is dates and the y axis is amount. The line chart is a projected amount that I will reach in few years; the x axis is the date while the y axis is an amount. I can get a chart that has a line and stacked columns, but ever time, the dates get messed up. The line has an extremely wide date range while the columns only have about a month of data.
View 9 Replies View RelatedI am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
In the attached file I have the data which shows me the market size in value, my market share in value & I did a calculation to get the percentage. What I need is a chart that shows me the market size in column graph, my market share in line graph and I want the percentages to be showing also.
View 3 Replies View RelatedI need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?
View 1 Replies View RelatedIn a worksheet I have (not equal) blocks of data. In column A there is the projectnumber, in columns B-K there are data. Projects are devided by an empty row. All rows in some block have in column A the same value (projectnumber).
What I need is the following: I would like to add names to that blocks. The name must be equal to the projectnumber.