i need code to find name and compile the value cell next to it...This is how it goes..
File A (2 columns) consist of name,Cash and the name tends to have many repeats (for some reason)
i want my file B to let user first key in a name(which should be the same as File A) and Compile all the cost of that particular name and display in a cell in file B
I have 5 sheets in 1 wookbook with about 15 columns of varying rows of data. What I need to do is create a macro which collates all the rows of data, which with their own heading, onto 1 sheet so that it can be printed and distributed.
The problem is of course is that normal copy and paste won't work because the rows may vary and the macro need to take that into consideration. I guess probably an 'If not or' type command that would copy and paste the row so long as it had values then move onto the next sheet and continue from the last one.
I have a row of text (for example say they are cells c3, d3, e3, f3) that have one of four text strings (red, yellow, green, blue). In cell b3, I need it to report back the column heading of all the cells that have the text "blue".
So if cells c3 and f3 have text "blue", then I need their column headings to be inserted into cell b3.
I have several tabs in a spready, and I want to be able to compile a range of cells from each tab (constant selection of cells from each tab) together as a list in a new sheet.
The list needs to be able to be populated in any order and with repetition.
I'm thinking some sort of sheet for selecting which tabs to bring in info from would be useful, and then a command button to compile all the info into one list on a new tab...but I have no idea how to write the code for it!
I have 3 different sheets with data that I want to combine into one sheet using a formula. Here is an example:
Sheet A
Date Amount
10/1 $5
10/2 $10
10/7 $5
Sheet B
Date Amount
10/2 $7
10/3 $10
Sheet C
Date Amount
10/5 $5
10/6 $10
I want a fourth sheet that fills with all the data from these three sheets. Ideally it would be in order by date, but I understand that might not be possible. So the last sheet should look like this:
Date Amount
10/1 $5
10/2 $10
10/2 $7
10/3 $10
10/5 $5
10/6 $10
10/7 $5
The reason I want to use a formula of some sort is because if I add more rows to sheet A for example, I want the fourth sheet to automatically add it (whether it's possible to do in date order or not).
I have a sheet called "Usage Data" and in that sheet Column A is called "DTL_LOC" (Warehouse #)
I need to pull all data from this column and create a list with no duplicates. Data is both Text & Numeric.
I need to use this information in a Validation List on my "Inventory" sheet in C1
The list is currently located in L3:L102 on the "Inventory" sheet. However this list may increase or decrease as the company grows.
I'm creating an InputBox that pulls from various cells in my worksheet to create a sentence as the default value in the text-space. For example:
A1 = Policy Number
B1 = Credit Amount (i.e. $10)
C1 = $ Info [i.e. if credit is for one month (1 x 10), if two months (2 x 10)]
D1 = Date Policy Ended
E1 = Months Excluded from Bills
Thus, the sentence that the InputBox would display would be:
Today's Date + A1 + B1 + C1 + D1 + E1
Date - Policy # - Credit Amount - Info - Date Ended - Months Excluded
A text example would be:
30JUL2009 - 1234 - $10 - 1x10 - 01JUN2009 - Exc Jul,Aug09
The issue I'm having is how to have the InputBox macro ignore one of the cells if it's empty. My current code is:
I have a file with 26 worksheets, each have four columns and the last column is averaged at the bottom, and I'd like to make a summary worksheet tab showing the name of the table, the number of records, and the average score of all 26 sheets.
The problem is, some worksheets have 20 rows, some have 1400 rows. I could go choose the data manually, but isn't there an easier way to do this?
The table name is merge/centered across the four columns, and is the worksheet name (using a formula I found on here). Then the four columns have headers, then data. The last row of each worksheet doesn't have the normal data in it, but says "Records Counted," then has a COUNT of the rows, then has "Average," then has an average of the column above.
I've been asked to compile and sum weekly sales data. The problem is that the data was supplied in 52 separate tabs, for each week of the year (I don't know who on earth thought that would be the most appropriate way to send the data).
Is there any quick way to import all the data from each tab into one worksheet? The data is laid out the exact same way on each tab. All the row and column headings are the same, and in the same cells. Only the numbers are different (obviously).
I have looked all over the place but nothing seems to work for me.
In detail, I have a quote book i made with two sheets for different types of items on them. For example:
Sheet 1 is for wooden items, and sheet 2 is for metal items.
So after i enter all the items on sheet one and sheet two, I want to be able to combine all the items from both sheets into one summary sheet that i can send to the customer.
I tried consolidate, and messed around with pivot tabels but it didnt seem to be what i'm looking for.
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 am working on a spreadsheet where I need to have data from different sheets added but based on the instructor whose data I want to see in the summary sheet. I want to be able to type the name of the instructor in a cell and have all of the data appear below. I believe that an index match formula would work, but I do not know how to have the data from the different sheets added nested in the formula.
View 2 Replies View RelatedI have been trying to compile some monthly and annual data from our main sheet to a FY09 sheet (for the fiscal year 2009). The 2 tabs in the uploaded example are the Distribution tab which we use to track the status of every item and the FY09 tab where I need the totals to be calculated for each month as well as the entire year. I have tried several formulas I found while searching the forum but I can not seem to get any of them to work, (I am sure it is because I don't understand them very well).
Since any formulas used will need to be copied 200 or so times, I would really like a VB solution which should also reduce the physical size of the file. I also tried a pivot table but I do not think it will show everything I need.
Basically, I need all the items separated by month on the FY09 tab. Then column 'F' on the Distribution tab needs to be summed up for each item in column 'D' of the FY09 tab for the respective month and multiplied by the respective item price in column'C' with the total value going in column 'E'. Column 'D' and 'E' need to be summed up for each month and cumulative for the entire year. There needs to be an average items and value for each month as well as for the year.
I am compiling an Excel programme with two sheets, One sheet is constantly being updated with data from a website. The download from the website is quite messy and I have to sort through the relevant data. I need to compile two lists on Sheet two that will take the Name from Column A1 and Value from B1 on Sheet 1 but only If the value of C1 is above 5 or below 0 (negative).If the value isn't correct, it will ignore that row and skip down to A2 B2 C2 etc. Hoping to get two tidy lists on Sheet 2 with no blanks/gaps.
View 1 Replies View RelatedHere is my code thus far - it seems to go through the directory and copy data (single columns of 101 data-points) from the workbooks, but does not successfully paste it into the master-workbook (error message reads: 'paste method of worksheet class failed').
VB:
Sub LoopThroughDirectory()
Dim MyFile As String
Dim ecolumn
MyFile = Dir("C:Documents and SettingskbriemMy DocumentsErindi_i_vinnsluEXCELprufa1")
[Code] ....
I do not know what function will do what I need. I am compiling info from different sources to make a mailing list. Basically it boils down to the following. Sheet 1 has Names and Parcel Numbers, sheet 2 has Parcel Numbers and Addresses. I am trying to combine it in a way that I have names and addresses on the same sheet.
View 2 Replies View RelatedI have several workbooks (5) with the same variables (columns- A:Q) but with a changing amount of rows (2:n, not including the headers). Each row corresponds to a date range (usually a week) for a particular person (up to 40 people) plus a few other values.
I would like to have a way of "merging" or "compiling" the 5 "seed" workbooks into 1 "master" worksheet. Where rows 2:n of each of the 5 "seed" workbooks are added to the master without any duplication of the same name-date range combination. Also, the master worksheet should not include the rows which only contain a name and date range but for which all the other variables are zero or missing.
Each "seed" workbook would have a button that sends the data over to the "master" worksheet.
Is this a really difficult project? Feasible for someone with near to zero VBA experience?
I attached 3 files to show you what I mean. The 2 "seed" files are merged into the "master" file. Please note that in the files only 2 names are used, but the "seed" files could contain any combination of 40 some names. Also note that the length of rows which contains data in the "seed" files is variable, although it should not be longer than 16 rows + the header row.
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
I frequently need to extract email addresses from huge amounts of text, like 40+ pages long, etc... I was wondering if a macro could be developed somehow to leave me with a stack of email addresses. I suppose the macro would have to test each piece of text for an "@" and a ".com" and then stack only those terms in a column somewhere. Any ideas on how to do this? I am not macro savvy AT ALL...
View 9 Replies View RelatedVBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedI have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub
loop and range function to apply in the below code through which I can avoid writing code for all the rows.
I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).
I have created follwing code which opens the file and then runs a macro in it.
a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value
[Code].....
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.