Then it will ask the user what name it wants to find.... then user inputs the name and the macro searches the 2 newly imported worksheets in column A and B, and if it 'contains' the name then will copy it to a results worksheet (and copy row 2 as a static line from each of the work book to differentiate which sheet it came from) ....
View 14 RepliesI have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.
Here is what I have:
Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.
I have 25 users with 25 seperate workbooks each with individual information on each workbook.
I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.
The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.
I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
I have a simple macro importing a .csv data file to a worksheet every 30 minutes.
When the macro runs an Import Text File dialog box displays.
What do I need to do to have the macro run to completion without the dialog box interruption?
Sub RefreshHourlyData()
htime = Now + TimeValue("00:30:00")
Application .OnTime htime, "RefreshHourlyData"
Sheets("H1Updates").Select
Sheets("H1Updates").UsedRange.Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A1:A1").Select
End Sub
can i consolidated all my sheet into 1 Consolidated Sheet by one sheet information below another. To make it clear i am showing the result how it should appear and what if the sheet is another workbook how would i write the path.
I have a spread sheet that looks like the following. I need to take all of the Y answers and make one line.
Client
ProductA
ProductB
ProductC
ProductD
ProductE
ABC Inc
N
Y
N
Y
N
[Code] ......
If any of the Product columns had a Y in the row, the line would get the Y. If not, an N.
I have a spreadsheet that has 25 different sheets on it and all of the sheets have it's own set of private macros. I want to know how do I just take one of the sheets macros and allow it to perform that same function across all the sheets since all the sheets are identical. The function of the macros is to hide/unhide rows for the toggle buttons and to hide/unhide columns for the checkboxes.
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Columns("j:o").EntireColumn.Hidden = False
Else
Columns("j:o").EntireColumn.Hidden = True
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
Columns("p:u").EntireColumn.Hidden = False
Else
Columns("p:u").EntireColumn.Hidden = True
End If
End Sub.............................................
I have a Spreadsheet that has about 150 rows filled with information. Column A (Part Description) Column B (quantity). Certain parts are repeated multiple times. I am trying to come up with a Macro to assign to a button that will do the following.
1.Grab every part on the sheet that has a Quantity in Column B and insert it in Sheet 4 starting in row 15.
2. Instead of having duplicate parts i want it to sum the quantities of all the matching parts.
Right now i am just using a "Hide 0 Quantity" Macro but it is still a pain.
I have three worksheets with a large amount of data on them (~60,000 lines). The column titles and layout are exactly the same. I need to either 1) Get all of the data into one worksheet so I can create a standard pivot table, or 2) Analyze all of the information using a multiple consolidation range pivot. When I try to copy the data from one spreadsheet to another I get an error message stating the copy area is a different size or shape from the paste area, even though it is not. When I try to create a pivot table using multiple consolidation ranges the fields returned are Column, Row, etc instead of the data fields I would expect (the column titles - exactly the same in each worksheet).
View 6 Replies View RelatedI have a list of entries, people log how many products they sell a day, at the end of the I need to consolidate that data, to get a sum of how many goods each person sold that week. So on on tab I will have multiple enetries by the same person and on the consolidtaed tab, I want each person's name and Id # to show up on one row, but them I would like a total of how much goods they have sold for that month. I have attached a copy of my file, the consolidated tab shows what I would like to get in the end.
View 2 Replies View RelatedI have an excel file with a Bill of material for each tab. I'd like to consolidate all the equipments from the differents tab into an unique tab. I enclose a sample: Test BOM.xlsx
I'd like a macro that should be able to detect the name and the quantity of equipment in each tab and copy the data in the 'desired output' tab. I don't know if the same result is possible with a pivot table.
how to consolidate file01 to file02 merging them, resulting the expected output sheet. Currently im doing this manually and it really take up a lot of my time this is just a sample scenario. My real world use of this is composed of over 2000+ accounts with different product purchases and billing numbers.
View 14 Replies View RelatedThis should be easy but after searching "consolidate" all that I could find was multiple sheets and complex questions.
I have 5 data columns that I need consolidated into 1 single column. I've tried the consolidate funtion but it appears to only work with formulas and numerical values (unless I cant find the proper command.) I've also tried paste special but it populates the cells following.
I need 5 cells word values pasted into a single cell
I need to pull a data from 3 different SharePoint lists and present it in a new data sheet. So far I know how to create data connections and I am able to see everything in 3 different worksheets but in the same workbook. Also if we have the same Project and IT number we should pull information just from the SharePoint 1 list.
View 1 Replies View Relatedi have a workbook with multiple spreadsheets A1, A2, A3, A4, ...... and I have information in each table, i need a macro to select the range of information and consolidate them into another file, ie the macro would say:
Enter the desired range of sheets:
I would input: from A31 to A34
and create a new new workbook called CONSOLIDATED
example:
[URL]
I have three sheets viz AR , AP, TR where I am capturing the monthly maturities for AP and TR and monthly collections in AR sheet.
All the sheets have same number of columns , the common factor between this three sheets are
1) the status column which contains two status cleared and Not cleared and
2) bank where I have different banks
I need to apply two conditions in the first sheet (named consolidate sheet ) where in the user will select first condition as criteria and the second condition as bank , based on this two criteria all the related information should get consolidated from (AR,AP,TR) sheets to the consolidated sheets
I may add more sheets in the future , it should take that also into account
I was able to apply the first condition that of criteria but need setting up the second condition of Bank .
I have attempted to code a "capture" worksheet that will consolidate other worksheets into an importable template. The problem i'm having is when I run my code I beleive it is referenceing the capture page, instead of the other worksheets. I know there are a few ways to accomplish this, one being to reference the other pages through name or location. This will not work as the other sheet names and locations will change, the only constant is the page where all will be consolidated "change capture".
I beleive it is misreferencing as it points to a being 256, and b being 0... both should have different values.
I would like the capture page to consolidate downwards (begin the second paste after the last line of the previous paste).
I have been trying to find a way to consolidate data in two tables, but can't seem to locate a good response. I have been toying with EXACT, VLOOKUP, HLOOKUP, MATCH, and SUMPRODUCT, but none seem to work for my issue.
Two Files - Call them acrendo.xlsx and office.xlsx
Both have a single sheet and contain first name, last name, and date columns. Office also has other information that I'm trying to merge into the corresponding row of acrendo.
Basically, I am trying to take the combined first name, last name, and date column from a row in acrendo as a single record, look for an exact match in office, and if it finds one, return the corresponding values in Coumns D, E, F back to acrendo. Someone suggested using the following sumproduct formula:
=SUMPRODUCT(--(A2=[ofc.ally.xlsx]ofc.ally!$A$2:$A$9329),--(B2=[ofc.ally.xlsx]ofc.ally!$B$2:$B$9329),--(C2=[ofc.ally.xlsx]ofc.ally!$C$2:$C$9329),E:E)
This always returns a value of 0, even if there is data in the matching row to be copied over.
One small complicating matter is that there may be duplicate entries in office (i.e. one row in acrendo may match to four rows in office). I can cleanse that if necessary, or preferably, if multiple matches are found, it would simply insert the additional rows into the acrendo file.
Also, is there a way to go through the acrendo file and check for duplicate records and delete them (e.g. if there is the same last name, first name, and date, delete the duplicates).
Can't do this manually as I have acrendo has almost 18,000 rows and office has almost 10,000 rows.
I have three worksheets in which the marks are entered.Now i want to consolidate the marks like
1 in sheet1 2 in sheet2 3 in sheet3 now i want to omit the min(1) marks and add/average of remaining two((2+3)/2)*100.
Is there any formula for this!
I am trying to consolidate multiple data sets in one worksheet into one list. An example of the data sets is below:
Product1
Company1
Product1
Company2
Product1
Company3
Product2
Product2
Product2
Product3
There are over 50 data sets in the worksheet with exactly the same number of columns. However, when the data is updated, the number of rows for each data set can change.
The output table is below:
Product1
Company1
Product2
Product1
Company2
Product2
Product3
Product1
Company3
Product2
I am assuming it is a loop function in vba to loop through all of the data sets in the worksheet, but I have limited experience with vba to know for sure.
I need some VBA to consolidate multiple worksheets into one (a 'master' worksheet). I do not have the data to hand. But I have been told every tab has identical columns but with varying number of rows. Also, please note that the data we are interested in starts a cell A15 on each tab.
View 3 Replies View RelatedI need to create a macro that pull in data from various spreadsheets, all with the same layouts, but with different file names and different worksheet names, into on master worksheet. The data should drop in after the title line and then continue on after each data set has copied.
View 3 Replies View RelatedI have a long task ahead of me and hope that someone may be able to assist in shortening it a little....?
I have a directory with circa 200 workbooks which have filenames that are dates in the format dd.mm.yy. The workbooks all contain just 1 sheet. I need to move each of the sheets into one master workbook and title each sheet with the old filename.
I run an activity group wherby members have to complete various events such as swimming, cycling, climbing etc..
I have a worksheet that comprises of columns A - P
Column A = Members Name
Column B = Address Info
Columns C - P equals the activities
As each member completes an activity a marker (the word "DONE") is placed in the relevant column C - P
This results in several records being created for each member with a single entry in one of the columns C - P.
I want to consolidate the data into a single row showing the activities completed for each member
The data is sorted by column A brining all like records together
So if the name in the current row = the name in previous row then move entries in columns C - P up one row then delete the current row.
I am wondering if Excel can consolidate data from 6 worksheets found in 6 different workbooks and consolidate them into a "master" workbook. Here is a sample of 2 worksheets, but again, there will be 6 in total:
Workbook Name = CC 0038
Worksheet Name = Monthly Results
Column A = Acct #
Column B = Acct Description
Column C = Actual
Column D = Budget
Acct # Acct Description Actual Budget
589800Salary-Related Benefits4,808 5,498 589803HO Employee Benefits2,055 2,055 589632Recognition Award0 83 561605Furn/Equip/Alterations0 308 568010Telecom (Local Access)0 250 568041Telecom (Telephone & Fax)0 42 568042Telecom (Wireless)546 473 583402Printing/Stationery IDB0 250 584200Office Stationery/Supplies28 129 560000Travel Costs0 440 ..................
I'm using a workbook containing a worksheet for each month of the year. I want to consolidate all the data from the different worksheets into one worksheet to enable data manipulation by means of a pivot table. Updates in the individual worksheets must be automatically updated in the consolidated worksheet.
View 4 Replies View RelatedI have attached a sample workbook to show my layout.
I need to link multiple sheets (but not all, just certain ones) to one "master sheet". More specifically I need to link only certain colums from each certain sheet. The sub sheets have detailed information, but I only want basic information on the master sheet like FirstName, LastName, Ext, Buyer#, etc Not every column from the certain sheets is needed.
I need a way to update this master sheet when I update the sub sheets manually. So if I add a employee record to a sub sheet, the master sheet is updated. These updates aren't often, so running a macro to rebuild the master sheet after I manually delete it wouldn't be out of the question, whatever is convienent.
In the attached worksheet, I believe there is a sheet called Items. I don't need any information from that sheet at all to be on the master sheet so it can't be in the array when the master sheet it built from the sub sheets.
I would like code to consolidate weekly hours worked, on each project, for each person. I therefore wish to end up with 1 row per person, per date, showing their GRAND Total Hours worked in column P (across all projects, and across all tasks). So, in the example below, everybody's rows would stay as is, apart from Mike Jones's, which I would like to be consolidated into one row, showing a GRAND Total Hours worked of 16.
View 3 Replies View RelatedI have attached a compressed excel file containing 1,2 and final worksheets. How do i consolidate all the data in their respective B columns of 1 & 2 to the B column of "final". In reality i actually have more than 5 worksheets to be combined to a single final column B.
View 9 Replies View RelatedI have around 10 worksheets containing a time sheet in each. In each sheet from cell A8 to A39 project nos. are entered for each day. I need to consolidate the total hours for each project. For this first I need to list out the project nos. from all the time sheets. Is there a way to do this by some formulas?
I have attached a sample. Kindly have a look at it. I have used a macro(taken from the forum) to protect all worksheets. Please press ctrl+shft+P and click OK to protect/unprotect the sheets. No password.