Macro To Extract Variable Info From Data Set And Calculate Corresponding Values
Nov 16, 2009
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............
I am finding it very difficult to extract the Project reference data for each employee, I have attached sample working file for reference.
For Example
I have a project names in Rows and Employee names in Columns and each employee fill the working efforts in multiple projects and i would like to extract which all project they have worked for each month. Since the data is very huge i am not able to progress fast in collecting the data
I have some problem with the extraction, I would like to extract the data to another sheet based on variable conditions but I can not do it. My code in just case working if the all conditions are specified. The target is that if want the code could extract the data based on one condition even, or two or three or more.
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
Is it possible to extract certain info from one cell. Example in cell A I have a description of a product plus price I only need the price, The price has a $sign in front of it. Example below
I have over 2000 products so I was hoping I wouldn't half to go through one at a time. Is is possible to extract just price and place it into cell b.
1 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $22.50 Stock Quantity: 107 YD
2 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $28.50 Stock Quantity: 107 YD
3 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $19.50 Stock Quantity: 107 YD
4 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $119.50 Stock Quantity: 107 YD
5 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $67 Stock Quantity: 107 YD
6 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $89 Stock Quantity: 107 YD
7 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $25.50 Stock Quantity: 107 YD
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
I have an invoicing spreadsheet, what I want is to have a main working page and then tabs after separated by vendor, but save myself a bunch of manual work in the future.
I want to be able to work in the main tab and the other tabs automatically update with the new info.
So I want the macro to search the text in the first column and if it finds "AIM Land Services Ltd." then I want it to populate the appropriate tab, in this case "AIM", with all the respective information after it ie: A-O. But I dont want it to select any other vendors/ info.
This is what I have:
Sub MoveData() For Each cell In Range("A1:A1") ' Where to look for the date If cell.Value = "AIM Land Services Ltd." Then ' The date Range("A5:O350").Select ' What is the range to copy Selection.Copy ' Copy it Sheets("Invoice_Log").Select ' Select the Sheet it applies to Range("A5").Select ' Where to paste the data
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty. From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c. b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
The Table : Column R represents a list of services
Column L is the full price Column N is the discounted price ( my example reflects no discount in this case) Column C is an associated code , in some cases the code is n/a Starting at Row 13 ........
I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.
The core logic of the macro is to extract what I call “Break Point” into column M and “Time Difference” between the Break Points to column O.
I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:
1) The user inputs the starting cell. In the Sample I use G200.
2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.
If the value is greater than original cell then there are 2 possible outcomes:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. “Time Difference” is calculated as the difference between the 2 point breaks in Column A. In this case I’ve hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values. b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.
Once lesser value has been copied to new cell, logic is now: a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O. b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.
OR Here's the logic now in reverse I need:
If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.. b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Once greater value has been copied to new cell, logic is now: a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O. b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.
Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.
There is a loop that controls the execution of the core logic until the last value in column G. In the macro I’ve hardcoded last row being 9171, but I’d like the Macro to figure out what the last row is automatically.
I know this description is a bit long but I’ve worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really can’t figure it out.
Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.
The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.
Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.
EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.
I would like to create a macro so that it will pull all the data from the worksheet "Data" and put it onto worksheet "Extracted Data". However I only require the rows which have Booking Office No SU17.
Macro that extract information i need from column A into column B. My file contains comments regarding a sale made by a manager. And, the only information that pertains to me is the bags it will be using.
Below is a example of a comment (ROW 28 of the file):
PO 186 TRADER SHIPPING WITH BROWN 240 X 20 # PANDA $ 9.90 EACH $ 49.50 CWT. 850 X 40 # PANDA $ 19.05 EACH $ 47.63 CWT. US # 1 MAX. 4% BROKENS
The only information i need is 240 X 20 # PANDA and 850 X 40 # PANDA.
In the file, the Bags Tab, lists the names of all of the bags we current have in our inventory. I was thinking that a macro can run through column A of the comment tab and if it finds a match in the bag tab it would list it out in column B of the comments tab.
The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:
While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.
The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).
I have a big collection of spreadsheets that are all laid out in the same fashion: from A2 down are the names of different individuals (one name per row). Column titles (from B1 right) are the names of unique qualifications that these individuals possess. In the cell where a row and column intersects is the date at which this qualification was awarded. If this doesn’t make total sense, I’ve attached a very basic version of this to the post (Sheet 1) – although the spreadsheets I’m working with have hundreds of individuals and scores of qualifications, rather than just a couple!
This data needs exporting/rearranging into a format that’s recognisable by another piece of software that we use. In this format, the data is split into three columns: the person’s name, a possessed qualification and the date it was awarded. This means that a single individual’s name may have multiple rows, since a different qualification will be present in each row for that person. An example of this layout is shown on Sheet 2 of the attachment.
I need to create a "purchasing programme sheet from extracted data in a different workbook.
In short I need to open the Allocation, Add stock qauntities to the the boxes with a quantity in it and if its a zero i need (Once the button has been pressed) the information to be extracted to the corresponding boxes in the Purchasing Programme Document: Description, Part Number and quantity.
I’m not to sure this can be done but I will try and explain what I would like. I have a Master workbook simply called Master.xls (Price) and I need to extract data from 3 other workbooks, these workbooks are in a folder called INFO, the workbooks are called, Summary.xls,(Sheet1) Supplier.xls (Sheet1) and List.xls.(Sheet1). I have attached sample files of all the workbooks, the original Summary has about 2000 colums and the original List.xls has about 20000 columns.
The only workbook i would like to open would be the Master.xls, would it be possible to exract the data without opening the other workbooks? or at least to look like they are not opened. On the Master.xls
When the data button is clicked I would like the following info displayed, if possible. A3 info comes from looking at Summary.xls columns B & C and comparing it against List.xls columns A & B, when a match is found in either of the columns, the code in List.xls C column will be displayed in the Master.xls A3 cell ...........
Can VBA be used to extract a table from a website and output it on an excel sheet?
Basically we have to log in to a website and type data to our spreadsheet which is a bit tedious from the table on the website.
I'm guessing this can be done in some way but us having to enter username/password on website first to get to page we want will probably cause an issue would it?
As part of a research project i need to extract as much data as possible from a webpage. The problem is to access each table i have to follow lots of links which I can't get to work automatically.
Its from a greyhound-data.com. So an example would be I want to extract all the racing stats for every dog that raced in swindon between 1st jan 2010-1st feb 2010. When i put it in the search engine I get 512 races in a table. I have to follow the link on name of race for each race.. Then I have to follow the link for each dog in the racers box, 6 for each race. Then when i'm on the dogs page theres a link saying on the top of the page saying a nember of races eg. 67races (number will vary for each dog). If I press that I get a list of all the racing stats for that dog. Its that table i need. But i also need the dogs name as a constant column. I need the table for each of the 6 dogs for each race out of the 512.. So in total 3072 tables.. It a lot of data and I have no idea how to get it?!
My biggest problem is I don't know how to say follow the various links. And I don't know how to loop multiple times - once for each of the races in the original list and then for the 6 dogs.
I have a spreadsheet containing a factory's operational data, with each machine in the factory being represented by its own worksheet.
The sheets all have the same structure, with each line of data representing a shift (we have 12-hour continental shifts, so column A of all the pages has the date in an standard format, and column B has either "d" or "n", representing the shift). Various datat is documented on each machine on a shift-per-shift basis.
What I need to do is figure out how to do a macro that extracts all of the lines on all of the machines' worksheets that pertain to a particular shift (for instance, all of the lines where column A read "14-Mar" and column B read "d"), and export the values of the entire row to another worksheet, creating a shift summary on one page.
To do this, I would like to make the date and shift user-definable variables that appear at the top of what would become the summary sheet, and have a button that activated such a macro at the top of the page as well.
I'm trying to create a macro that will allow me to extract data from another workbook (whose name and location is listed in a defined cell) and populate a range in my new sheet with this data.
Ideally, I want to be able to do this without actualy opening the source file, but any means to this end would be great!
I know this was possible in Lotus 123, but have not yet found a way to do it in Excel.
I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3 Transactions for CUSTOMER ID: 0000CUST7 Sales 4 5 6Product summary for Vehicle ID 00001080 7 8Product Description Transactions Quantity9 101 Unleaded 3 57.60 GL11 12Hose summary for Vehicle ID 00001080 13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get the data into a more convenient format for analysis. I need a macro that will:
1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).
2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded 3 57.60 GL111081 Unleaded 6 84.70 GL121122 Unleaded 5 47.00 GL131182 Unleaded 8 95.80 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
In the attched sheet, I am wanting to draw data from the worksheet "On-Off" Array $AI$14:$AJ$91 into column AA, while at the same time choosing the closest match to the time. I have managed to get the closest matching time into column AC.
What I am having trouble with is now calculating the number of staff on that break time (column Z) against the number of staff rostered on at that stage "On-Off" (column AJ). I thought it would be a simple division and format as a percentage, however something in it using the extract from the "On-Off" sheet is stopping me, and I cannot figure it out.
