Help with merging excel files based on identical IDs
Hi,
How do you create one excel file from two excel files based on identical IDs?
For example if Fle1 contains:
ID NSN NAS AGD
0 Name Location Address
1 NameA LocationA AddressA
and File2.xls contains
ID NSC FIF NASC
0 XXXX YYY ZZZZZ
1 AAAA BBB CCCCC
How do I create File3 by merging File1 with File2?
File3.xls
ID NSN NAS AGD NSC FIF NASC
0 Name Location Address XXXX YYY ZZZZZ
1 NameA LocationA AddressA AAAA BBB CCCCC
Thanks,
Victor
How do you create one excel file from two excel files based on identical IDs?
For example if Fle1 contains:
ID NSN NAS AGD
0 Name Location Address
1 NameA LocationA AddressA
and File2.xls contains
ID NSC FIF NASC
0 XXXX YYY ZZZZZ
1 AAAA BBB CCCCC
How do I create File3 by merging File1 with File2?
File3.xls
ID NSN NAS AGD NSC FIF NASC
0 Name Location Address XXXX YYY ZZZZZ
1 NameA LocationA AddressA AAAA BBB CCCCC
Thanks,
Victor
Can you give some more info, on what the goal is? Do you want do do it from a ,net program? can you do it with excell itself? Is the amount of columns fixed??
Excel VLOOKUP
You can do this with the VLOOKUP command. I would perhaps put the two worksheets into the same file, seperate sheets. I have attached a sample showing how the VLOOKUP formula would accomplish this.
RDBMS Solution
However, that said, that, the VLOOKUP is really not the best for this sort of thing. You are trying to do a JOIN between two worksheets. This is a classic problem that has already been solved a million times over with relational database systems such as Access database or SQL Server. i would much sooner go the Access route. Import the sheets into Access and jsut create a view that joins the data on the ID column.
VLookupSample.xlsx
You can do this with the VLOOKUP command. I would perhaps put the two worksheets into the same file, seperate sheets. I have attached a sample showing how the VLOOKUP formula would accomplish this.
RDBMS Solution
However, that said, that, the VLOOKUP is really not the best for this sort of thing. You are trying to do a JOIN between two worksheets. This is a classic problem that has already been solved a million times over with relational database systems such as Access database or SQL Server. i would much sooner go the Access route. Import the sheets into Access and jsut create a view that joins the data on the ID column.
VLookupSample.xlsx
ASKER
Hi,
I would like to achieve it using VB.NET (Windows App).
Victor
I would like to achieve it using VB.NET (Windows App).
Victor
In .NET code, I would use a library like Epplus (http://epplus.codeplex.com) to read the files into a dataset, then do a job operation add shown by Microsoft here HOW TO: Implement a DataSet JOIN helper class in Visual C# .NET https://support.microsoft.com/en-us/kb/326080
ASKER
Hi,
Thanks for the link (codeproject) but it's not for what I am trying to achieve, I need to merge the data in the same worksheet.
Thanks also for the other links but would prefer to avoid third party controls if at all possible.
Victor
Thanks for the link (codeproject) but it's not for what I am trying to achieve, I need to merge the data in the same worksheet.
Thanks also for the other links but would prefer to avoid third party controls if at all possible.
Victor
Technically, you don't have to use Epplus. I just tend to use it to make loading data from Excel easier. But you could still load the data from Excel into a datatable with ADO.NET OleDb. http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB
One you have the datatables in memory, then do the join between them as Microsoft has shown
One you have the datatables in memory, then do the join between them as Microsoft has shown
ASKER
Hi
Do you have a link for sollution in VB.NET?
Thanks,
V.
Do you have a link for sollution in VB.NET?
Thanks,
V.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You.