Merge Two Spreadsheets (files) Based On ID
Sep 30, 2011
I'm a long time excel user and I've come up with an issue, it can surely be solved in any other larger database language but excel is just my everyday friend and I was wondering if it's possible to do such a thing.
I have a list of "people" in my 1st ssheet and they have
ID | ProductName| Category | Year |
and I have this data on the 2nd ssheet
ID | Price | Description
There is 600.000 records in the primary sheet but only 22.000 in the secondary because the data doesn't repeat (one description can fit to 10.000 products, no need to write it down that much times).
But now I need to compile one big/large file, so I need to have one file that contains all:
ID | ProductName| Category | Year | + | Price | Description
In MS SQL I would use the "Where" function and compare the ID here and there, but is there a possible way to do it in excel?
View 6 Replies
ADVERTISEMENT
Nov 23, 2009
I want to do....is...a macro or something....to be like "Hey...were going to match up column information based on the first column, there exists both the same data in both sheets....as you can see in A3 and A2, now, I want to be able to add the information that is in book1, into the "DBL approved", so like...the 2nd row, I want columns B-G to be filled via book 2 because its saying,
"okay, A3 in DBL has the data 00-120-01, but there exists no data next columns over...but oh look, A2 in Book1 does, let's fill in that missing information!" so now it'll fill in the bullet points, brand, segment, etc."
View 4 Replies
View Related
Sep 15, 2013
I have a couple of spreadsheets. all of them have a same or common column called "ID".
The order of the ID columns of the spreadsheets are not in the same order however.
I'd like to see if there is a way that I can merge the spreadsheets to one using the shared ID.
To explain more:
Spreadsheet1 columns are: "ID", "URL", "Location"
Spreadsheet2 columns are: "ID", "Picture"
Spreadsheet3 columns are: "ID", "Address"
As I said copy/past is not an option as the IDs are not in the same order. I need to create a new spreadsheeet and have all "ID", "URL", "Location", "Picture", "Address" in that spreadsheet.
View 12 Replies
View Related
Jan 2, 2009
I have two CSV's which are updated on a regular basis.
CSV Master - contains about 23,000 rows at the moment and will continue to grow.
CSV Master - does have a header row
CSV Master has unique (8 digit) numerical ID's in column A so all unique ID's start at "A2"
CSV Master - contains 31 columns ("A through to and including AE") this is a fixed figure.
---- Then I have ---
CSV Extras - generally contains about 200 rows and its unlikely it will ever be more but is contents are different every time it is used.
CSV Extras - does have a header row
CSV Extras has unique (8 digit) numerical ID's in column "A" so all unique ID's start at "A2" and these ID's are a common denominator with both sheets.
CSV Extras - contains 44 columns (last column is AR) which is a fixed figure,
CSV Extras - columns "B up to and including AE" are all intentionally all blank fields.
--- Hope that explains where I am, this is what I need to do ----
I need to be able to look up the ID's (that are in column A in both CSV's) and create a single CSV. In my mind that is a Vlookup of some sort but I can't get it to work
I want to open CSV Master and then, If the ID exists in CSV Extras then fetch all the data in columns "AF to AR" and add it to the CSV Master.
View 9 Replies
View Related
Dec 6, 2006
1. I have a master file (master.xls), currently empty
2. I have 80 excels files (all look a like but their worksheet name are different)
3. I would like all the data from these worksheet to be appended to master.xls with a click of button.
4. I tried using some codes from this forum but they did not work.
5. All the source file starts with ums bla bla.xls
View 9 Replies
View Related
Jan 31, 2007
I have two files that need to be merged together (file1 & file2). These two files have three columns each. One column on both files is the same. This column is labled "phone number". One file has more lines the other has less.
I would like to make a macro that will check file1 against file2. If it finds a phone number the same it will cut the whole line and append staring in column 4 to same line in file1.
View 5 Replies
View Related
Dec 3, 2013
I have two separate excel worksheets from which i want to merge two columns from each worksheet into a new sheet. from the first sheet column A & E and from second worksheet column B & D. The values of second worksheets need to start where the value of first sheet ends. Want a macro to run this automatically every time.
Have tried to merge and consolidate but macro does not work.
View 3 Replies
View Related
Oct 7, 2008
Right now I need help mergng 2 files:
#1 One is a txt file with emails
#2 the other is a .csv excel file which contains several fields: email, name, address, etc
Basically what I need to do is to create another .csv excel file that contains all the emails in file#1 that are also present in the file #2 plus their corresponding additional fields ( name, address ) etc
View 9 Replies
View Related
Dec 21, 2009
I haven't really been able to find a total solution to an issue I have had for a while and hope someone can help.
I have a series of workbooks that I create from Crystal reports, I want to do the following after clicking a button on a form:-Open up two files in a folder with similar names (IL-BA, IL-BA-19), both have the same headers
Open a Third file, this is my 'Master File' and is in a different location, this has the same headers
Copy from the two files onto 1 sheet on my master file (excluding the headers)
Close the first two files without saving and move them to a 'Done' folder
Save and Close the Master File
Move onto the next set of files in the folder (NE - BA,NE - BA - 19) and do the same again, until all the files in the folder have been completed.
All the files in the folder have different data, so have different headers, but the Master file for each set will always have the same headers, (if that makes sense), but they are specifically named, so cycling through the named files in the folder, merging the two together.
My main problem is that I can't seem to find all the code i need, i can merge the two files, either in different sheets(which i don't want), or with the headers for each file still attached (as i can't seem to find a robust way of removing them).
View 9 Replies
View Related
Oct 12, 2007
I have hundreds and hundreds of excel files. but in every file, there is the same column lets say column D which has all the information I want. In stead of opening hundreds of worksheets and copying and pasting over the data into a new sheet. Is there a code I could write that would open all these files and copy the data from the same colum over into my new sheet? so column D in the first work book will copy to colulm A in the new work book. Then colum D in the second workboko will copy to the new worksheet in column B ect ect ect.
View 9 Replies
View Related
Oct 8, 2010
I have this macro to go to a specific folder and open up all of the files in the folder and merge them into a worksheet.
I want to change it so the user can select the files to be merged.
Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
[Code]...
View 4 Replies
View Related
Feb 20, 2012
I have been opening files from another application which opens the files in memory as .XLS. I have not saved these to my PC. Is there VBA to merge all open .XLS files into another Workbook - preferably .XLSX.
View 1 Replies
View Related
Nov 28, 2006
I have two name/address lists in separate Excel workbooks. I need to merge and eliminate duplicates by name. One has single name field in format "Lastname, Firstname". The other has a single name field in "normal" format (First and last name). Ultimately I need to end up with two name fields, First and Last, and this I can do.
The problem is in identifying any possible duplicates between the two lists. Why? Because the first list will have names like "Smith, Robert" and occasionally maybe "Smith, Robert J.". The second list will have names like "Dr. Robert J. Smith Jr. PhD", as well as possibly "Robert Smith" or "Dr. Robert Smith".
Using Text-to-Columns will still require a lot of reworking, because the last name won't always be in the same column, depending on whether there's a Dr. or Mr. or middle initial and so forth.
I've looked into the LIKE( ) function which holds some scant hope, but I don't think it will happen for me either.
How I could compare "Robert Smith" to "Robert J. Smith" or "Dr. Robert Smith" or "Dr. Robert J. Smith Jr. Phd"? Imagine all names in column A.
View 9 Replies
View Related
Sep 9, 2006
is there any way for a worksheet to import and merge (append) tab delimited files from these directories:
C:TempmergeV¿ñdata.txt
C:TempmergeV¿ñ (2)data.txt
C:TempmergeV¿ñ (3)data.txt
C:TempmergeV¿ñ (4)data.txt
C:TempmergeV¿ñ (5)data.txt
- contents of data.txt have range A1:Pn, where 'n' is last row, different for each file
- found a previous thread very similar to what i wanted, but donno how to edit this to suit my purpose
[Solved] Importing: Import many TXT files to singe workshee
BTW, does VBA recognise directory folder with unicode characters? I can rename my directory if it doesn't.
View 3 Replies
View Related
Jun 6, 2008
I have a folder named DATAENTRY (consist 90-100 password protected files & it may increase whenever the need arises).
I need to merge A2 to (data in last available row & last available column) of each file in that folder into a single sheet named MERGEDDATA of file named MASTER.XLS.
I got one excellent code to unlock/lock the files automatically without opening the concerned files. This is the Link [url]
& the Code is: ...
View 5 Replies
View Related
Oct 12, 2010
merging the multiple *.xls files into one single *.xls file but each *.xls file com in separate worksheet.
Say i have 30 xls files in datewise i.e., 01.10.10, 02.10.10, 03.10.10 so on....
I want to merge all the above 30 xls files in single file master workbook - in that master workbook file the above 30 xls should come in separate work sheets.
View 9 Replies
View Related
Jan 6, 2009
I need to use to prepare for analysis a large set of individual participants' Excel data files collected using a psychological reaction-time experiment (a modified Stroop task). This experiment was created using a program called Superlab by Cedrus, so I posted an inquiry at the Superlab forum [url]. Their tech support suggested I might need to use macros in Excel but couldn't offer further assistance. I'll try to briefly describe (a) our Stroop task, (b) the operations I'm trying to carry out, and (c) the format of the individual Excel files,
Our Stroop task: The participant gets some instructions and practice trials, then a series of stimulus words are presented one word at a time in the center of a computer monitor in one of several colors. Word presentation order is randomized for each participant. The participant must press a key corresponding to the color of each word as quickly as possible. For each keypress (including the spacebar, pressed on instructions screens), the reaction time (in msec) is recorded in their Excel spreadsheet under the 'Reaction Time' variable. The 'Error Code' variable indicates whether the participant pressed the correct (C) or incorrect (E) color key in each trial. There are 8 types of words for a total of 48 trials. (Actually, 4 sets of emotion words and 4 sets of control-animal words, each set composed of 6 words each. The task basically measures how much peoples attention is captured by emotionally relevant cues in the environment.)
For each participant's data, I need an efficient way (macros?) to:
(1) clean it [i.e. discard or ignore the instruction & practice trials as well as trials where the participant was too impulsive (reaction time < 100 msec), too distracted (rt > 3000 msec), or gave the wrong color response (Error Code= E)]
(2) determine whether enough valid trials remain for further analysis (>39 trials), and if so:
(3) calculate a mean reaction time score for each of the 8 word types [where denominator for each word type is based on # remaining (non-discarded) trials for that word type]
(4) merge each participant's summarized data as single rows in a common spreadsheet (for importation and further analyses in SPSS), like this:
---------------------------
ID#...... MeanRTW1.......MeanRTW2...... (etc. for word types 3-8)
101.......1056................2013...............
102.......2148................2594...............
103...
---------------------------
Excel File Format: I've attached a sample file, and here's a simplified overview of a spreadsheet (irrelevant columns and some rows removed). First the ID number is recorded (e.g., 393E), then some unnecessary junk (exp name & date, intro, practice, instructions), then data for the 48 actual trials of interest:
-------------------------------------------
........A...............B..................C.................D............
1 393E
2 stroop.xpt
3 Wed Jan 1, 12:33:00 2008
4
5
6 Trial Name.....Trial No..... Error Code.....ReactionTime
7 introduction..... 1................C............561243
8 practice.......... 2................C............... 2062
9 red-worried........14.............C................1001 (*the first practice trial)
10 (9 more practice trial rows, deleted here)
19 instructions........3..............C................5000
20 red-chicken......19..............E............... 1205
21 blue-tense.......32...............C.................782
* (46 more actual trial rows, deleted here)
--------------------------------------------------
To clarify:
-The variable 'Trial Name' indicates nominally what stimulus was presented for each trial.
-The variable 'Trial No.', indicates the unique numerical marker for each stimulus word. (It probably should have been labeled 'Word Type'. It doesn't reflect the order of stimulus presentation. E.g., the word "tense" is always 'Trial No.' 32 for all participants, regardless of when it presents.)
-A subset of actual trial words (e.g., worried) were also used for the 10 practice trials. Thus, the 10 practice trials must first somehow be discarded or flagged to be ignored before calculating average reaction times for the remaining 48 actual trials.
View 14 Replies
View Related
Aug 20, 2006
I did a search for " import text" and found some promising leads, but not exactly what I was looking for. I have tried running macros and looking at the code but don't know how to pass the file names from the the D47:D147 range to VBA(see below). I saw elsewhere that Excel can be told to create a temporary batch file, and that the batch file with the command "copy text1.txt+text2.txt+text3.txt all.txt" for example could be used to merge the 3 text files into a file called all.txt. I don't however know how specify the path where copy starts, to tell the batch file to look in subdirectories or to pass the file into Excel. I've posted this question yesterday to Yahoo Answers http://tinyurl.com/omers and http://tinyurl.com/rfww9 without much luck.
Below is the macro I would like:I have text files whose names are found in the range D47:D147 although without the appended ".txt" extension. The sheets can only contain one name sometimes, but on average 8 to 10, so in the average case only cells D47:D56 would have entries.The text files are found in the say H:Textfiles directory or subdirectories.I would like Excel to find these files, concatenate them with a row between each file, and paste the results into cell K251.Finally, the text import wizard should be used with a space as a delimiter and the last 3 columns (it's sometimes only 2) of the concatenated file, not imported.
View 2 Replies
View Related
May 13, 2006
I have two worksheets. One has columns A B C , the second has columns A B C D. Columns A in the two worksheets reflect the same data (serial number) , although one worksheet could have more rows than the other since it gets updated manually daily. I would like to use a macro that would:
1. use columns A's value in worksheet 1 to match a row in worksheet 2 then merge columns from worksheet 1 & 2 and insert into a new worksheet
2. repreat until all rows in worksheet 1 are read
3. save worksheet 3
View 3 Replies
View Related
Jul 6, 2007
I have a master spreadsheet containing 4 relevant fields, 'Manufacturer', 'Product', 'Version' and 'Type'. This contains all possible variations of 'manufacturer', 'product' and 'version' that can occur in the organisation.
The type field shows how the particular item is going to be processed, either 'automated', 'manual' or 'non'
This is a computer generated spreadsheet, apart from the 'Type' field which we've entered for each one individually, as are the user spreadsheets which dont yet have the 'type' field.
This is a large list of around 7000 items
I then have a multiple spreadsheets (one for each user, totaling around 1200) that has a list of a couple of hundred items that are relevant to the user which contains 'manufacturer', 'product' and 'version'.
i need to sort these out quickly by comparing the sheet to the master spreadsheet so where 'manufacturer', 'product' and 'version' match up with the masters record it automatically removes the the 'non' type of item from the user sheet and placing either 'automated' or 'manual' in a new 'type' filed where appropriate
View 3 Replies
View Related
Feb 6, 2009
Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:
Sheet A has 10,000 records with these fields: id#, name, address, place of employment.
Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.
Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.
View 3 Replies
View Related
Dec 18, 2013
I have a data that has several columns. But I need to separate the spread sheet based on one particular column values.
View 14 Replies
View Related
Feb 10, 2014
I have two sheets with a Unique ID that I'd like to match and generate a third sheet. The third sheet would consist of First Name, Last Name, ID number where the phone numbers match from between sheets.
Sheet 1: A = Fname, B = Lname, C = Phone
Sheet 2: A = message, B = Phone
I'd like to make a Sheet 3: A = Fname, B= Lname, C = Phone, D = Message
Is my only option to try and get on a PC and use MS Access? Never had to do this in Excel before.
View 1 Replies
View Related
Apr 2, 2012
I have two sheets that both have all the same company names on them but one of the sheets doesn't have ALL of the company names. So, one sheet might have 2500 rows with company names and data specific to that sheet, and the next sheet might have 2450 company names (all match up to the first sheet, but 50 are missing), with different data.
The first sheet is A through L, the second is A through K.
Attached is a sample spreadsheet expressing
First sheet:
A:[Name]
B:[DBA]
C:[Owner/Officer_1]
D:[Owner/Officer_2]
E:[Owner/Officer_3]
F:[Owner/Officer_4]
G:[Phone]
H:[TxDMV]
[code]....
Company Name and Name will match EXACTLY on each sheet, but the second sheet won't have quite as many. So, if possible, I would like to, where column A in matches in both sheet 1 and 2, move or copy all the data from sheet two into the columns on sheet one that come after L.
View 2 Replies
View Related
Dec 19, 2007
The begining portion of this works fine, I have it moveing data from one column to another, but I then need it to combine two rows (BW and P) and then place that into column (O)...
View 3 Replies
View Related
Jul 4, 2013
i am trying to merge cells based on the input from another sheet. these input will change from month to month and i need the sheet to cancel the merge and merge again based on the new input.
Example.xlsx the file showing what i need it to do.
View 8 Replies
View Related
May 19, 2014
I have a large volume of data and i need to manage and organize somehow.
Example:
A B C D E
CHRIS AN204 2005 Apple Green
CHRIS AN204 2005 Apple Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green
This should look like this
A B C D E
CHRIS AN204 2005 Apple Green, Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green
I manage somehow to find a VBA code that will merge my rows, but it's not working as it should. It's losing data.
Also in a cell it can happen to have more than 255 characters.
View 1 Replies
View Related
Mar 19, 2014
Now I know that merging cells is usually not a good thing in VBA, but I'm working on a data report that just looks bad unless longer pieces of text can span several cells to minimize column width.
So here's the problem:
I have a column of 1's and 0's in columns E and F, and text in Column G. I want to merge the cells in columns G and H for that specific row if there is a 1 in either the E or F column for that specific row. And I need to do this for a long range...rows 7-5000. Any ways that VBA can do this?
View 4 Replies
View Related
Jul 27, 2007
I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this?
If Sigma = 0 Then
Selection.EntireRow.Insert ' New row for new entries
ActiveCell.Value = "NONE"
ActiveCell.Offset(1, 0).Select
End If
View 4 Replies
View Related
Apr 19, 2008
I have multiple worksheets with multiple varying columns with varying rows. My one constant is the product_id. I want to merge all worksheets into 1 worksheet based on the product_id's. Here is my example:...............
It has to consolidate all of the column names from all of the worksheets into the final worksheet, then take all of the rows and put the product_id in the product_id column and put the other data under the appropriate columns. Some columns will end up blank where they may be a column in worksheet 2 but not worksheet 1.
View 4 Replies
View Related
