I will try to keep this as short as possible. I have a huge amount of stringed data from dry kiln runs all well organized. I want to make something that will look at all this data (or at lease the ones that I specify) and give me a prediction of what would happen if I was to make a change somewhere in the schedule. I have alot more info if needed.
View 10 RepliesI have data for average turnover per hour of a business and have fitted it to a polynomial (order 6) trendline. So I have for example, running along the x-axis i have 8am-9am, 9am-10am, 10am-11am etc and on the y-axis the average turnover for the relevant hour.
What I would like to be able to do is use the trendline's formula to be able to predict sales for any given day. So if I were to enter the first few hours of turnover on any given day, I would like excel to predict the rest of the day's turnover based on the trendline.
I need to predict sales data in future using multiple independent varaibles.I used FORECAST function to predict sales value for single independent varaiables.But i dont know how to predict sales using multiple varaiables.
View 10 Replies View RelatedSay there are 3 cols ( A,B & C ) whereby C sums B and A. I have a situation here where C does not show (=SUM(A10:B10)) on the formula bar. I can type anything in C and the formula remains. I've even tried deleting the contents of C. It's like the formula is only activated when values are entered into A and B. Could excel be auto-predicting the formulas as a trend? I would like to repeat this for my other workbooks but I could not find a way to repeat this fluke.
View 9 Replies View RelatedI have a large table (20,000 plus entries) and I need a way to pull out data that matches criteria in buckets.
For example:
April data (column B) that is from 0 to 30 (column C)
April data (column B) that is from 31 to 45 (column C)
... 46 to 60
... 61 to 75
... 75 to infiniti
Then do it again for May, June, July, etc. I have used DCOUNTs to pull this type of data, but it's a bit cumbersome. Any other ways to pull this type of data from the table?
I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.
The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.
I was wondering is there a formula that will collect the data automatically.
As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.
Is there a formula or something that will enter the per day data?
I have just added some false data to show you how some things get recorded.
I have a UserForm function which accepts as a string the name of a TextBox control on the form. The function returns a reference to the named TextBox control (or Nothing if the control does not exist). I have the function header defined this way:
Public Function TextBox(byval strName as String) as TextBox
The function returns a reference to the TextBox control like this:
set TextBox = Me.Controls.Item(strName)
(I know, the .Item is not required.)
The code which calls the above function first declares a TextBox object with this Dim statement:
Dim txtTextBox as TextBox
The routine then goes on to call the TextBox function like this:
set txtTextBox = TextBox(strTextBoxName)
When run, the above statement genrates a Type Mismatch error. I'm confused, because if I 'TypeName()' the TextBox function's returned value, it's "TextBox,".
I thought I could work around this problem by changing the TextBox function's return value's data type to Variant, but that produced the same result.
The only "solution" I've come up with is changing the TextBox function's return value's type (and any reference to the functions' return value) to Control.
I expected that the Controls object would behave similar to a Collection object in that it's items can be different types (TextBox, Label, CommandButton, ListBox, etc.) and no Type Mistmatch erros occur so long as the type of the item returned matches the type of the variable referencing that item. But is seems that Controls requires that any reference to one of its items must be type Control, not the actual type of the item returned.
I'd prefer not to use Control data rypes in my applcation, as that would require additional code in all of my subs and functions to ensure that any Control object passed to it is the correct type of control (TextBox, Label, etc.).
Can anyone explain what's going on here? Why shouldn't I be able to assign a TextBox type variable to Controls.Item("xyz"), so long as the item returned by Controls.Item("xyz") is type TextBox?
Objective: Collect data into the table below on a weekly basis coming from a real time data feed.
Can this be done formulas in the table?
The current time is running in cell D1.
The current date is in cell C2.
When the hour closes for the matching date, I would like rows to show the last value for that hour.
Example: ROW 3 to show the value of the currency pairs at the close of 7:00 hour for 12/13/09, etc.
My real time data is on another sheet. For reference call it: Main!G25:Main!G38 (14 currency pairs of data running, listed vertically)
ABCDEFGHIJKLMNOPQR1Week of:12/13/091:22:00AUDUSDNZDJPYGBPCHFEURUSDCHFJPYEURCHFUSDJPYUSDCHFEURGBPNZDUSDGBPUSDEURJPYAUDJPYGBPJPY2Today:12/13/2009Open Price0.911864.521.67941.462886.11.512489.051.03390.90030.72461.6242130.2681.2144.65312/13/200908:00 GMT7:004SUNDAY12/14/200909:00 GMT8:00512/14/200910:00 GMT9:00612/14/200911:00 GMT10:00FILLIN TABLEHERE712/14/200912:00 GMT11:008MONDAY12/14/200913:00 GMT12:00912/14/200914:00 GMT13:001012/14/200915:00 GMT14:001112/14/200916:00 GMT15:001212/14/200917:00 GMT16:00
I can create my own new collection which is handy as it accepts uniques only and i can access using its "key"
But can i have a collection of "user defined data types"
This is all taking place in vb6 and excel 2003.
I am making a userform that is activated once the user highlights a bit of spreadsheet and clicks a button on the command bar, here is what I want it to do:
1. (copy data from the spreadsheet
this is tricky because I am trying to have it be a conditional situation where the user highlights a place on the spreadsheet and whatever that place is at the moment, it will get copied to a variable on the form.
There are 8 fields to highlight and copy, two with info that wont be copied onto the new spreadsheet but will be used to update fields and make the filename.)
2. make a new spreadsheet:
a. the filename is made from cell values in the 'from' and 'to' columns and the date.
b. new spreadsheet needs to have a template section from a7:f7 that has 4 fields that will be filled in from the fields on the form.
c. copied data is to be moved from old spreadsheet to new spreadsheet to cell a8. There should be 6 columns that will be filled with data.
3. on the form there will be a browse button to save the file in a location specified by user and 'last saved location' name should be saved to a textbox.
I read about a browse button here that I would like to use, but I have to adapt it so it can be used in the 'browse' button. [url]
I dont even know if it is possible to copy a user-specified range. It seems that it should be, because when a user highlights an area, that area is being held in memory as a position... I tried passing these values to another sheet but it wasnt successful. I tried to dim 'selection' as string and pass it into a variable, but I am new to variables and this project is a big experience for me in vb.
I also am still learning about how to make values in a field pass to another field on a spreadsheet.
I'm trying to collect specific data from a collection of different (.xls) files into 1 master file using the following code (which runs in the master file):
Code:
Sub FolderPick()
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
[Code].....
I'm having trouble with the Name-variable in the 'red line'. This variable is set in the 'purple lines' however, the code (which I got online) keeps adding ".pdf" to the name.
I think it has to do with the settings of the 'purple/underlined line' but I don't know if this is true and if so, how to change it.
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
I have a one column spreadsheet. The column contains this data:
1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
Need to produce a formula to reference a large amount of data and pull one month at a time:
View 7 Replies View RelatedI have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.
See Below:
Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890
From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.
End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890
Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.
I have a very large spreadsheet that was exported from an ecommerce site with close to 1000 products. I have one column that I need to extract some text from. This column holds all of the html from the product description and is huge. I only need to extract the actual description of the product, but am having a very hard time figuring out how to do it. I've tried using the mid, left, and right function; but not all of the html is the same so it's not really working the way I need it to.
I have multiple tags throughout the html that I can use with the mid function, but there is more than one occurrence of them. So, how can I tell it to start at the 4th occurrence? I've spent countless hours searching, but I'm a complete novice when it comes to excel and I don't even know what to search for. I end up looking through sites that explain how to pull the Y out of XYZ, which is what I need, just on a much larger (and more complicated) scale.
It was suggested that I set up a macro that will find the 4th occurrence of the word, and then uses the mid function to pull the data out, but when I try to find the word, it says it doesn't exist even though I can see it right in front of me.
I have enormous amounts of data to sort through (as we all do) and I am trying to link them to unique identifiers for easier data maintenance later.
What I want to do is vlookup from the 'solved' sheet a code, which will search the 'data' table and return the 'DFU' code.
Example 2905.xlsx
I need to find the most frequent number for each name in a large datatable, eg. Name1=12.
ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..
Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.
I have a large data (more than 50k rows) in a spreadsheet and i want to consolidate the information's.
here is the sample source data.
Source Data
codeamt1amt2amt3
123456$16.00 $0.00 $0.00
789011$0.00 $18.00 $0.00
123456$12.00 $5.00 $0.00
123456$0.00 $0.00 $7.00
111111$11.00 $1.00 $3.00
789011$22.00 $0.00 $0.00
and the output should be.
Output
codeamt1amt2amt3
123456$28.00 $5.00 $7.00
789011$22.00 $18.00 $0.00
111111$11.00 $1.00 $3.00
currently i'm using the consolidate function of excel inside my vba (which is working fine) but i took forever before it finished, i'm just wondering if there is an alternative way which is more faster.
I have a large amount of data, 97000 rows and 4 columns that I need to sort every 26 rows starting with row 26. I only need 2 columns but I'll take all four, any easy way to accomplish this task. The data is position and time and the time is in seconds from which I need the 0,.5,1,1.5... all the way to 12577 seconds.
View 1 Replies View RelatedI am facing problem to make summary sheet of top 3 value from large number of value of my excel sheet like-
Sheet1
A
Group
B
Name
C
Value
1
Nashid
200145
[Code] ......
Now I want to make summary in sheet2 top 5 value among the list of sheet1 separately from group 1, 2 & 3.
If group number is 1 in sheet1 then top 3 value will be like this-
Group1
Name
Value
Nashid
200145
Jabed
184198
Eman
156622
If group number is 2 in sheet1 then top 3 value will be like this-
Group2
Name
Value
Rubel
172569
Hasin
152304
Monem
104463
If group number is 3 in sheet1 then top 3 value will be like this-
Name
Value
Romel
168251
Nasim
136357
Hasan
120410
I have a large list of data in sheet1. The relevant columns contain a unique identifier (column 'N') and a value of either "True" or "False" in column O. Because I don't want users to have to trawl through hundreds of lines looking for the 'False' entries, I have a macro that copies only those records to Sheet3. In this sheet the user can set the 'False' value to 'true' but I don't know how to return the new value to Sheet2 (possibly for multiple records).
View 9 Replies View RelatedI have a worksheet that uses Excel 2003's automatic subtotalling feature witSelectihin one of my macros. It works fine on my sample data, but when I try to run it on the larger set of customer data it hangs.
Any suggestions? I've been up all night, maybe this is just something really dumb, but I don't know. I stepped thru the macros, and it is hanging when I press f8 for the line that inserts the subtotals.
According to the task manager it really is hung, not just slow. Waited over 2 hours.
The line of code where it is hanging is:
Selection.subtotal groupby:=3, function:=xlsum, totallist:=array(14, 15, 16, 17, 18, 19, 20, 21, 22), Replace:=true, Pagebreaks:=false, Summarybelowdata:=true
I am a total newbie, so if there is a painfully obvious reason for why this is happening, don't think I'll be upset if you point out how dumb I am. Speaking of dumb, if this issue is posted elsewhere I apologize, I searched for hours before asking, but I may have looked in the wrong place.
I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.
What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.
There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.
This sounds like a complex function to me but I am hoping that someone has a solution for me.
I want to use the attached Test.xls file below and do a lookup from the Data.csv (see zip file) where "Empl ID" (column A in Test.xls) matches "ID" (column A in Data.csv). I want to bring back "SupvID" (column B in Data.csv) into column D in Test.xls.
The problem I have is that in real life Data.csv is a very large file and contains more than Excel's maximum number of rows. I want to automate this using VBA to lookup the values without opening the csv file.
I have looked at a number of forums for answers and I can see where using an ADO connection will probably help accomplish this, but I can't seem to get it to work on these specific files.
I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.
Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland
The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)
Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
I have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?
View 3 Replies View Related