Help with merging excel files based on identical IDs


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?


ID   NSN      NAS                  AGD                     NSC        FIF    NASC
0     Name   Location          Address               XXXX     YYY    ZZZZZ
1    NameA  LocationA         AddressA           AAAA     BBB   CCCCC


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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??
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.
vcharlesAuthor Commented:

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

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

In .NET code, I would use a library like  Epplus (   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
vcharlesAuthor Commented:

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.

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.

One you have the datatables in memory, then do the join between them as Microsoft has shown
vcharlesAuthor Commented:

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


You can use to convert between C# and VB.NET

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.
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.