Merging Two Spreadsheets Not Consolidating
Jul 18, 2012
I have two spreadsheets of part numbers, one with a full list of parts approx 3500 lines and another with the same part numbers (but only approx 1000 lines) but this file also contains sales history. columns shown below
PART NUMBER, PART DESCRIPTION, RETAIL, COST, UNIT OF ISSUE, CURRENT STOCK, YTD SALES, MTD SALES
What i want to do is merge the two files together so the 1000 parts i have sales history for correspond with the same 3500 lines on the other master spreadsheet, so i can then sort them in which ever order i need at the time.
View 5 Replies
ADVERTISEMENT
Oct 24, 2008
What I am trying to do is take the data (all columns) in Spreadsheet 2 and put that data in Spreadsheet 1 with all the companies matching up, how do I do this, is this possible?
I tried using VLOOKUP but I don't know if I was doing it right and I don't even know if it's the right function to use.
In sheet 1, column A I have a list of about 30,000 companies
In sheet 2, column A I have a list of about 10,000 (pulled from sheet 1), I have other info associated with these companies in columns B and C.
Basically I want to take column A, B and C from sheet 2 and match them up with the same companies in sheet 1. So there will be 10,000 matches and 20,000 N/A's. This is how I set up the VLOOKUP but nothing happened.
View 8 Replies
View Related
Jul 14, 2013
I have a template and several spreadsheets based on this template, and when changes are made to the template they need to be manually merged into each spreadsheet, a very painstaking, time consuming, and error prone process. Changes are mostly added rows, data tables, and macro (coding) changes. Surely there must be a better way of doing this. There must be some way of automating at least part of this process, or so I would think. I know that changes to a workbook can be tracked, but I don't shows changes to code, and I don't know if it is true for templates as well (but I suspect that it is); at least this would show me what changes to the template had been made, but maybe not changes to the code, but this is only a small part of the problem.
View 1 Replies
View Related
Aug 11, 2006
I have three large txt files. All three are the same number of columns but are of a different number of rows. (Each month the number of columns will increase but the 3 source files will still contain an equal number).
Therefore I need to open the first text file, select all data and paste it into my master file.
I then want to open the second txt file and paste all the data at the first empty row of my master file.
I will then open the third txt file and paste all the data at the first empty row of the master file.
Finally I will close the txt files.
I will then have several other amendments to make to the master data file.
So to my problem....
Opening the txt files is fine.
However I want to select all the data in the first txt file but excel gives me an error message. (On the line highlighted in my code). I remember there being a function in VBA that selects all data around a cell, can anyone remember how to do this?
My code so far is:
Workbooks.OpenText Filename:= _
"C:CUSTOMERPass 1.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:= Array(Array _
(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8 _
, 1), Array(9, 1))
Cells.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Sheets("Data").Select
ActiveSheet.Paste
View 5 Replies
View Related
Jan 23, 2014
I have a spreadsheet with names and phone numbers. I have another spreadsheet with similar- but not the same names and not in the same order- with email addresses.
I have pasted all info into one spreadsheet and alphabetised it- it looks a little like this now.... (joe bloggs being an example name)
joe bloggs telephone no
joe bloggs email
Is there a way i can easily merge these two rows keeping the name the same? Otherwise it will take FOREVER!!
View 5 Replies
View Related
Aug 15, 2014
I have a spreadsheet with only 80 rows in it and I need data from another spreadsheet with over 200 rows. One of these rows in each spreadsheet contains a matching field (Job #). I only want the 80 matching rows data from the over 200 row sheet.
Is there an easy way to combine these two. Even adding the 80 to the 200+ will be fine as I can just remove the blanks. I can't figure out a way to combine them and match them up.
View 2 Replies
View Related
Jul 31, 2014
I have two years of sales data for same and different product codes in different sheets for each year.
I want to merge both the sheets so that i have a consolidated view of both years data in one sheet.
The sheets have columns that look like the one described as under:
Product code Month 1 m2 m3 m4 m5 m6 m7 m9 m10 m11 m12
View 2 Replies
View Related
May 2, 2014
I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).
I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.
Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii
Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.
Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.
View 1 Replies
View Related
Aug 8, 2009
I have data that looks like this.
A1 B1 C1
1 2 100
2 3 100
3 4 100
My result should look like '1' in column A1, '4' in column B1 and '100' in column C1. I have hundreds of rows this way and any help will speed up my process. I tried writing a macro, but have no idea on how to delete the rows in between. Even if we can copy the result to the next column (i.e. have '1' in column D1, '4' in column E1, and '100' in column F1, that will solve the problem.
View 3 Replies
View Related
Oct 13, 2009
I have a sheet with the following information in rows:
PO# Acct# Item# QTY
There are multiple lines that share the same acct#.
How can I create the sheet so that each row combines the records for the same acct#.
For example:
PO# Acct# Item# Qty PO# Item# Qty PO# Item#
There are never more than 6 rows that share the same acct#.
View 23 Replies
View Related
Nov 12, 2009
I have written a macro to consolidate multiple sheets into one sheet. I have two sheets which are to be copied one below the other.I am attaching the code for this here.
ub Bingo()
Dim ws As Worksheet, n As Long, flg As Boolean, last As Long
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Consolidated-Input").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add(before:=Sheets(1)).Name = "Consolidated-Input"
n = 3
For Each ws In Worksheets
If ws.Name "Consolidated-Input" And ws.Visible And ws.Name "DCS-User" And ws.Name "Cal" And ws.Name "DCS" = True Then
If Not flg Then
ws.Range("a:c").copy
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteValues
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteFormats
flg = True...................................
View 9 Replies
View Related
Jan 12, 2010
I have 85 workbooks in the same folder with a sheet in each workbook called "Budgets". Its the same template in each workbook with different data. I need to create a Summary master file of all my budgets so in the end I will have 1 Workbook with the 85 copies of the "Budgets" Worksheets. Does anyone know the VBA for this?
View 9 Replies
View Related
Sep 25, 2013
consolidate data from different workbooks into a single work book.
Each workbook contains one month payment information for employees.
i want to consolidate the the workbooks into one mastersheet such that i will have twelve columns (One for each month). On the consolidated sheet, i want each contributors monthly contribution displayed under the months to which the cointribution relates.
*find attached a dummy data illustrating the request*
i will like the results displayed as illustrated in the consolidate tab.
View 1 Replies
View Related
Jan 14, 2014
I have a clock machine report weekly which generates a spreadsheet very similar to the example attached.
The number of sheets can vary, the number of rows per employee can vary all of which makes me think that I can't use a formula to collate the data. how I can do this?
View 1 Replies
View Related
May 29, 2014
Tried the consolidate feature, however not sure that it is what I am looking for.I am basically trying to condense several calendars ( one calendar per department in a company) into one Calendar..Each calendar has its own tab and looks exactly the same. (tab names: Clubhouse, spa, and final calendar... possibly more departments added later) Hard to explaing so I will attach the file. All I would like to do is combine all calendars into the "final calendar" and in time order if possible... example below
{Clubhouse Tab} A3 will have the time (10 am) , B3 will have the item "Meeting"
{Clubhouse Tab} A4 will have the time (5 pm) , B4 will have the item "Dinner"
[code]....
View 14 Replies
View Related
Oct 21, 2006
I have 4 worksheets where the structure is exactly the same except the figures differ
The worksheet have text and values in columns A to S. I need to write VBA code that will combine the data and show the descritions in a consolidated woorksheet.
View 9 Replies
View Related
Jan 21, 2008
I have a workbook with multiple tabs (sheets) I would like to make another tab to consolidate all the data, rows and columns into one master sheet.
View 9 Replies
View Related
May 1, 2008
way to combine multiple rows into a single row using vba?
I have large amount of data approximately 5000 rows. I would like to combine all of the rows by DOB.
The reason why they are listed multiple times if they have multiple Benefit #'s.
Here is an example of the multiple rows of data: ....
View 12 Replies
View Related
Jul 14, 2008
I have several workbooks (around 15) in one folder which all contain the same worksheets but with different data. The worksheets all have the same columns. What I would like is a way to consolidate each of the worksheets of the workbooks into one new workbook by running the code whenever I want it.
In other words I have workbook 1 till 9 which all have worksheet 1 till 3. And I would like to consolidate them into a new workbook with sheet 1 till showing all of the data.
View 9 Replies
View Related
Sep 27, 2008
I have a 2 worksheets (IJOFTD3D(1) and IJOFTD3D(2)) that I need to filter using criteria in 2 columns and combine to one worksheet (GKA Data). These are not the only worksheets in the workbook.
Here is the criteria: Column A (sortcode) = GKA
Column D (Cust_Code) does not begin with I
I am able to get the first worksheet into GKA Data, but I don't know how to get the second worksheet to append to the first using VBA. This is a daily report that I am trying to make as automated as possible.
View 9 Replies
View Related
Nov 26, 2008
I have two tables that pull data from two different SQL tables via an ODBC connection and refresh every 5 minutes. The table headers etc are all identical (it is support call information FYI) but what I want to do is display those tables as one as opposed to two seperate tables - can this be done relatively easily and hopefully without the use of VBA? (Unless anyone is willing to provide the code of course!!)
View 9 Replies
View Related
May 28, 2009
I am trying to automatically copy and paste data from multiple source workbooks into one master file, and could use some assistance with building a macro to do so. Specifically, I'm trying to accomplish the following:
1. Open source file.
2. Within the source file, find any worksheets whose name begins with a 5-digit number.
3. For these worksheets only, copy values from a specified range (B15:B64, J15:J64, and V15:V64), and paste these values into the master workbook (into worksheets already existing in the master workbook, whose names match those in the source workbook).
4. Close the source file and repeat this process for the next one.
In total, I'll have ~20 source files to process in this manner. I have the file names listed in Column C of the "Import" sheet on my master workbook.
So far, I have been successful in taking care of the step 1 (opening the external files). I'm a macro newbie, though, and am not sure how to tackle the conditional cutting and pasting outlined above.
View 9 Replies
View Related
Jun 15, 2006
I am trying to make a program that opens a file from my hardrive, adds to a list box and allows me to send to a second box. I then want to be able to open the selected files in box 2. I have done the above except being able to open the files in the box. Below is my code can anyone help. I want all the selected files to open in one workbook, so want all files sheets to be copied into one new workbook.
Private Sub CommandButton1_Click()
If Lb1.ListIndex >= 0 Then
Lb2.AddItem Lb1.Text
Lb1.RemoveItem Lb1.ListIndex
End If
End Sub
Private Sub CommandButton2_Click()
Do While Lb1.ListCount > 0 .........
View 6 Replies
View Related
Sep 21, 2006
I am creating a consolidation worksheet. It contains a macro that automatically copy data from other sources and paste it into my DB sheet. It already works, but I want to improve it. The source worksheet databases has been defined with the name “SOURCE”. Is there any way that I can make my macro look for this dynamic ranges directly, copy and paste it into my DB sheet. I am attaching an example of what my current macro is doing.
View 2 Replies
View Related
Aug 15, 2012
I have a folder that contains around 45 excel files, each of these files contain a tab called "corp val". (these files change name month to month e.g. Report June, Report July etc)
I am finding on a monthly basis i need to go into each of these excel files and copy the data from the "corp val" tab into a summarized spreadsheet - which i then use the data for various things.
Just wondered if there was a solution to this or will it always be difficult to copy and pasting all the data i need. I am not in anyway an advanced excel user, I am guessing this might be some form of macro in which case I am maybe better to keep doing it the way I was as I am not literate in macros.
View 1 Replies
View Related
Dec 3, 2013
I have a large patient data set consisting of one column that includes their zip codes and one column that includes the number of times they were seen at a hospital over a period of time. I want to try to consolidate the data so I just have the number of total cases (for all patients) in a certain zip code over that time period. There are several thousand patients and it would be great to avoid doing it manually.
View 1 Replies
View Related
Aug 5, 2014
Having trouble consolidating 3 worksheets into one worksheet, on these 3 worksheets they all have the same headings which go from A-R. I simply need to be able to run a formula or use some sort of method to incorporate all 3 lists into 1 master list. The only way i currently can do this is to create a vba script which simply copies say 500 rows from the first worksheet paste onto master spreadsheet then copy 500 rows in second worksheet and find next blank row and paste etc.
Is that the best way to do this or is there a better way?
View 1 Replies
View Related
Nov 25, 2008
I'll be as succinct as I can, and I'm sorry if this question has been answered already. I've had a look at past posts and can't seem to find anything.
My service has an Excel workbook of patient details. These details are separated across three sheets called 'Generic', 'Heart Failure' and 'Falls and Fracture Management'.
The columns in each sheet are the same. Every month I produce a single report of data from columns A, B, L, Q and R in each sheet.
I've been doing this by copying/pasting each of those columns from 'Generic' individually into a new sheet, then putting the same data from 'Heart Failure' underneath, etc., then manually deleting all rows that contain a blank in any cell.
Is it possible that VBA code can be written to do this? I don't know anything about codes; I know I could achieve the same effect with linking, but it seems that this would be unreliable.
View 12 Replies
View Related
Jan 5, 2009
I have data that looks like this
A-----B
300 TEXT1
300 TEXT2
300 TEXT3
300 TEXT4
300 TEXT5
300 TEXT6
300 TEXT7
I'm looking for a formula, procedure or vb code I can use to keep only 5 of these records based on whats in column A. So if there's 50 lines that have 300 in column A, I want to delete all rows but the first 5.
View 3 Replies
View Related
Apr 17, 2009
I have a zipcode file I am trying to consolidated that has zipcodes and customers. how to consolidate the customers into a single row by zipcode as outlined below
Current Data
Zipcode Customer
00001 Customer 1
00001 Customer 2
00001 Customer 3
00002 Customer 4
00002 Customer 5
Desired output
00001 Customer1, Customer2, Customer3
00002 Customer4, Customer5
etc etc
View 6 Replies
View Related
