how to tackle a piece of work that I really don't want to do.
We have extracted some summary information on one of our products. The workbook has 10 sheets, one for each of our 10 main sales channels. Each sheet has the same layout of information on. There are 7 tables on each sheet, and each one cuts the data in a different way (eg one summarises by age of purchaser (in age bands), another by demographic group etc). Each table then has the same 14 columns of key data (eg number of sales, average order value).
I've been asked to analyse this information "for anything interesting". At the moment, the only way I can think to do this is to print out the 10 sheets, sit down with a highlighter, and try to visually identify trends and anomalies. The idea fills me with dread, and I suspect will be quite inefficient.
I've asked whether I can get the data in a pivot table or some other format that might be more conducive to analysis, and have been told no. (There will easily be more than 100,000 lines in the original data (we're using Excel 2003)). To be honest, I'm not sure that I'd really be that much better off, even if it were in a pivot table.
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
Basically, I would like to pass the array by reference. Basically, the code below imports the file into my workbook, but I since I have several files going into sheets in the workbook, I don't want to write the code 5 times. I figured the best way would be to create a multidimensional array and pass through my procedure below.
Code: Sub import_Employee_Data() strSourceFile = ThisWorkbook.Name strPath = ThisWorkbook.Path & "" strFirstImportFile = strPath & "byemployee.csv" sDestSheet = "byEmployee" If Len(Dir(strFirstImportFile)) > 0 Then
if someone have a code for filter o limit a Listbox but with 6 columns and 6 textbox because i have a code but is only onedimensional works fine but i dont have any clue how to modify to works like multidimensional
this the one dimensional Private Sub tbxFind_Change() Dim vList As Variant
'Read the original list vList = Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
'Convert it to a 1D array vList = Application.Transpose(vList)
I'm getting a compile error "Can't Assign to array" when I try to return a multidimensional array from a function. I'm not a VB expert, and I was unable to find any examples of returning a multidimensional array from a function. Here is some sample
Sub someSub Dim data(1 To 4, 0 To 23) As Integer
For counter = 1 To numSheets data = processData(counter, data) ' The error happens here Next End Sub Private Function processData(counter As Integer, data() As Integer) As Integer() Dim dataCopy(1 To 4, 0 To 23) As Integer
dataCopy = data 'modify dataCopy processData = dataCopy End Function
I have 9 named ranges on worksheet Sheet1. I want to print every combination of every non-singular range on worksheet Sheet2. Below is a simplified version of the scenario.
There are three named ranges: Letters, Colors, Animals. Say the below are the entries for each range.
Letters = {A, B, C} Colors = {Red, Blue} Animals = {Dog}I want to print every combination of Letters and Colors but exclude Animals since it only has 1 entry.
Therefore my result would look something like this:
A Red B Red C Red A Blue B Blue C Blue
My thought is to make a multidimensional array GrandArray where GrandArray(1) = Letters and GrandArray(2) = Colors, then recursively go back through every combination and print to Sheet 2. I can set up GrandArray, but stepping through each element is creating mismatch errors.
I'm trying to avoid For loops since my real data has 9 ranges which may or may not be included in the final print.
I'm trying to pass a multidimensional array to a function that I have defined, but I receive an error about an object mismatch. Here is what I have in regards to the array and function. What should I change?
Code: Dim diffArray() As Integer Dim countArray() As Integer Redim countArray(count,2)
diffArray = getRunningSum(countArray) End Sub
Public Function getRunningSum(ByRef countArray() As Integer) As Integer() 'Code here...... End Function
I have data in a dictionary object and need to load it into a two column listbox. The VBA help says you can load data into a multicolumn listbox from a 2D array. So my question is how can I extract the data from a dictionary object directly into a 2D array.
The dictionary object stores data in key and item pairs. So the 2D array would have one dimension for the keys and the other for the items. I want to do this without having to extract the items and keys into separate 1D arrays and then loop through them to build the 2D array.
Rather than looping through a multidimensional array to populate a worksheet, is there a quick function which can export the entire array to a worksheet?
How to apply a filter over the array "a" shown below and get the result in a new array "b" containing the filtered values based on the following conditions (the conditions criteria could be 1, 2 or 3. In this case only 2):
Criteria1 in Column 4="yellow" Criteria2 in Column 3="ggg"
And only show values of columns 1 and 3.
If were using an SQL query would be something like this:
Code: SELECT F[1],F[3] FROM "Table" WHERE F[4]="yellow" and F[3]="ggg" The output array would be as below:
Code: b=[{12,"ggg";140,ggg}] this is the array:
Code: Sub test()
a = [{"122","53","ggg","yellow";"140","9","ggg","yellow";"16","-22","ddc","yellow";"127","-37","ddc","green";"53","-28","ggg","grey"}]
I need to write a program that will take numbers from various sheets, do a function on them (specifically a statistical analysis), and print that number to a cell on one sheet. I've tried to have all of the numbers go to one cell and have the analysis done on them, but after so many sheets, I get a memory error. I don't necessarily need all of the numbers to be saved, just the result is what I need.
Here is the situation. I bought 100 shares of stock at $40 each for total investment of $4000. The stock price is now $26 a share so the investment is down $1,400. I want to figure out the number of additional shares I need to buy at $26 so that when the price goes to $26.01 i turn a profit.
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
What's the technique for opening a .pdf file and copying its contents into Excel for subsequent analysis by VBA?
On my Mac version of Excel there doesn't seem a facility to import it or even copy and paste it. Is this something that's available in Windows versions of XL?
I have a problem that I am finding impossible to find a solution to myself. I want to take the maximum value on a graph (in a column) that has the 2 values next to it (above and below) within 5% of that value. If they are not within 5% I want it to look for the second largest value with the same conditions and so on until the conditions are met.
If it makes it simpler, the final point can be ignored as there are only values above it in the column.
I found this code in the posts to automatically install the Analysis ToolPak at Open.
Private Sub Workbook_Open() AddIns("Analysis ToolPak").Installed = True End Sub
I tried this in the workbook module but it doesn't seem to work. Is this code correct or have I done something incorrectly?
Also, are there any drawbacks to an automatic install of an add in like this and if it runs the code every time the workbook opens, is there any performance issues (maybe only opens a bit slower)?
I have been putting together workbook for my golf analysis.
Against each hole (1-18) I enter in one row which club I used from the tee which could be any of the following: D,3w,5w,1,2,3,4,5,6,7,8,9,w,s.
I also have a row that says whether I have hit the fiarway or not. If hit fairway = 1 if missed = 0.
What I want to be able to do is show the % of times I hit the fairway with each club. This would seem ida for the if function but I'm not quite sure how it would work because the result row is going to need to look at every hole to see if the club used was d,5w,3 etc etc and 'if' so then it will need to look at the row which says 'hit fairway or not' i.e. 1 or 0. This is where I get lost and am not sure if I do need the 'if' function or something else? Perhaps I need additional rows?
If I wanted to calculate weighted average of a bunch of salespeople, is there a way to not include some values, such as ones below a certain value? I'm trying to so something like, what would my weighted average be if I didn't include my bottom 2 salespeople or my top 2 salespeople.
I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
I have an excel question on how to create a table that will be able to compare actual results with our estimated results.
Basically, the column I have in blue in the attached excel file contains our estimates. Once the actual numbers come out, we will replace those numbers with actual numbers.
I would like to create a table that will automatically show the magnitude of the impact of the actual numbers vs our results -- basically how much each item added or subtracted from EPS.
The items I would like to show the impact of are: (1) The tax rate -- how much the actual tax rate impacted EPS vs. our estimate. For example - if it was a lower tax rate there may be a $0.03 benefit. (2) interest expense line - more/less interest..what the impact of that is. (3) non-recurring items (4) Share count - a lower share count or a higher share count than we anticipated - what the effect on EPS is.
For each item though, every other item should be held constant. So if looking at tax rate -- then use the actual interest expense and the actual shares.. and if looking at shares...compare that to our estimate but use the actual tax rate reported and the actual interest expense....
There should then be a sum total of these items +/- impact to EPS.
i want to run data analisys(Rank and percentile) on each row in my sheet and put the results on a new sheet (the reuslts are 4 rows ) My problem is some how the command of the run analisysWon't run the command
kStop = . Cells(1, Columns.Count).End(xlToLeft).Column Sheets.Add ActiveSheet.Name = "Analisys_Results" Sheets(" Total time data").Select Var = 4 Var1 = 8 For ILoop = 1 To kStop If (ILoop > 1) Then Var = Var + 4 Var1 = Var + 4 End If.....................
I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).
I am currently doing this manually but I'm sure there must be a smarter way of doing this.
Data Analysis . I have 7 tables gathered in one spreadsheet but in seperate sheets. Tables have the same columns but different data baceause they come from different locations. Now I would like to create pivot table to combine data from all 7 tables. I read a little and I got to know that relationships is needed between the tables but I can't create it because I don't have unique data in the table. Usually tables consist repeating names, locations, some numerical data.
For example every table consists worker's name. I create pivot table from one of 7th starting from worker's name in row field and put some data in value field. Then I add another workers from another table. I can do it but when I put in row field I have workers from second table under those from first one. And of course data are badly calculated. I would like to have all workers in row field with proper data. It means one column with all workers from all 7 tables an then some their data
I know I can copy all of them into one big table and then create pivot table but this is solution what I wanted avoid. I know it will work but these data can change from time to time and it will be very hard to maintain spreadsheet. When data will change I will have to copy manually .Is there any solution to create such a pivot table from multiple tables sources but with the same column structures?
For my thesis I collected data of football players. I have the data in an excel file, but it is unstructured. Due to the nature of the dataset, I don't want to sort it out by hand. The result should be that the dataset is ready for statistical analysis. I have three categories, namely; Player_Performance, Player_Profile and Transfer_History. I will discuss them now.
Player Performance In this excel file the performance data of the player is gathered. As you can see, the player ID and the player name are a row above the performance data in different competitions. This data should be on the same row. Player ID, Name and then the performance data. How can I achieve this?
Player Profile The player profile file is the easiest. I want to delete the double information. Some players are 2 times in the file. I want to delete every row which doesn't start with a player id. I also want to remove player id's which arent followed by player information.
Transfer History The transfer history is the same as player performance. In the first row you will find the player id and name, and beneath are the different transfers. I want to put every transfer in a row of their own. The row should start with player id, then name and then the transfer. So it is possible that one player id will be on more rows.
In the attachment I put the files as they are and three files of how it should look like.
Hi there, I would really appreciate the help with being able to enter a code and having Excel find a match. I have attached a simple example of what I aiming for.
