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
vcharlesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kulboyCommented:
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??
0
MlandaTCommented:
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
0
vcharlesAuthor Commented:
Hi,

I would like to achieve it using VB.NET (Windows App).

Victor
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

MlandaTCommented:
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
0
vcharlesAuthor Commented:
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
0
MlandaTCommented:
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
0
vcharlesAuthor Commented:
Hi

Do you have a link for sollution in VB.NET?

Thanks,

V.
0
MlandaTCommented:
You can use http://converter.telerik.com to convert between C# and VB.NET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Thank You.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.