Solved

merging 3 excel files into one

Posted on 2004-04-08
9
228 Views
Last Modified: 2010-04-17
Hello.
I am trying to move 3 excell files into one excell file. I need the final excel sheet to not have any duplicates in one field, and to check the number in another field and only put the record that has the higher number in the final project.
its really just a data price sheet. has item numbers, other atributues to the item, then the price, and i dont want any dupe items in the final, and i only want the highest prices item of all the sheets.
Any help would be great.
lots of points.

Thanks again

-BugNuker
0
Comment
Question by:bugnuker
9 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 10788946
Hi bugnuker:
First point: Getting three files into one file:

Open all three files in Excel
Right Click on the tab of one of the sheets, then choose move or copy.
In the top dropdown you can specify the worksheet you want to copy this sheet to.


Dabas
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10788950
bugnuker:
Next: Extracting non duplicates:
Not so easy, specially without having the data in front of me.
There is VBA programming involved for sure, and it depends on your knowledge of programming and VBA

Dabas
0
 

Author Comment

by:bugnuker
ID: 10788983
I know VERY VERY little. not enough at all, was hoping there was some program out for that.
anyone know how to code something like that, its got to be easy... :D cheap?
-BugNuker
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Expert Comment

by:Dabas
ID: 10789002
0
 

Expert Comment

by:BlueTom
ID: 10789624
You  can record some macros and Alt+F11 to see them,they are very easy to understand.
0
 
LVL 1

Accepted Solution

by:
JimMetzler earned 500 total points
ID: 10790720
If this is a one-time merge, it is fairly simple (with a few steps).  I do it all the time for one project or another.

1.  Copy all rows from the 3 excel files into one spreadsheet.

2.  Sort the rows by the unique ID field first (your product number?), then by the column where you are looking for the highest value.  Be sure to select "Ascending" order where you want to only keep the highest value.

3.  Create a new column that will be used to flag the rows that you want to delete.  We'll call it the DELETE column.  Enter the code @IF(A1=A2,1,"") in the first row of your the DELETE column, where the "A" column contains your unique identifiers.  Copy this code to all your rows.  Now you should have a "1" next to all rows that you wish to delete.

At this point you can delete all the rows that have a "1" in them, but be careful: If you sort by the DELETE column, your "1" flags will change!  To get around this problem:

4.  Highlight the DELETE column and copy it to another new column (we'll call it DELETE2).  When you paste to DELETE2, select the "Paste Special" option from the EDIT menu and paste just the "VALUES" into the new column.

5.  Sort by the DELETE2 column and delete any row with a "1" in it.  What is left will be rows with the unique identifiers, where you keep only the highest value from the other field.

After you are done, you can delete the DELETE and DELETE2 temporary columns.

Good luck!
Jim
0
 

Author Comment

by:bugnuker
ID: 10797610
Jim-
Thanks for the help, however, I have ran into another problem.
The three files have all the same info, but some collums are in a diffrant format than the others.
Sample:

I have one file that has the item number in this format: "123456789"
and another file has it in 1-234-56789
i need to have uniform data.
also, the price field is all jacked up too.
sample:

one file has prices in 2400 format (meaning 24.00)
and the other files have it in 24.00 format

i need help making all fileds alike.
thanks
-Shane
more points are being added.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB 6.0 printer how to align 6 58
wordappend challenge 8 147
How  do I get an older program to run in Windows 10? 20 103
C# application error "Parameter Is Not Valid". 3 79
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now