Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I combine two Excel Workbooks and delete the duplicate colu
I am dealing with 2 Workbooks that have about 40,000 rows each, so it is way
too much work to combine, and edit them by hand. I have numbers in the 1st column, 2nd i have Text, and 3rd I have Numbers. One File only has the first 2 columns. I need my other file to be combined w/ this one. The other file has all 3 columns, but a lot of numbers in the 1st column should match each other. So i want them to combine, but have no duplicate numbers. Can anyone help me? thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I combine two Excel Workbooks and delete the duplicate colu
if the number in the first column matches will the data in the other two
columns be identical. if not when they combine do you want the information in one sheet to override the other sheet or do you want the final product to show the information from both sheets in the same line. in separate cells or combined in one cell. one method to do it if all of the information is identical in each worksheet is to in sheet 2 use in D1 =countif('Sheet1'!A:A,A1) copy down to the end of the data use autofilter to select all the zeros and copy thes and paste at the end of the data in sheet 1. "Myles" wrote: I am dealing with 2 Workbooks that have about 40,000 rows each, so it is way too much work to combine, and edit them by hand. I have numbers in the 1st column, 2nd i have Text, and 3rd I have Numbers. One File only has the first 2 columns. I need my other file to be combined w/ this one. The other file has all 3 columns, but a lot of numbers in the 1st column should match each other. So i want them to combine, but have no duplicate numbers. Can anyone help me? thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I combine two Excel Workbooks and delete the duplicate
hm okay sorry i wasnt very clear lol.. ok here is what im doing
I have 2 files. File 1 has 5 columns and 39739 rows, and File 2 has 3 columns and 31247 rows. The 1st column in both files has account numbers. File 1 has more account numbers, but i only need the 31247 account numbers that are in file 2. When i combine the two files i want the extra from file 1 to not be in there at all. Also File 1 my 2nd and 3rd collumns have 1st and last names. I am trying to combine these by a function called COM something. Its not working. Okay File 1 has all the Balances in it The Balances are my main concern. I dont have the balances in File 2, but i ned them in there, and i need them to match the correct account number. Is there a way i can do this? "bj" wrote: if the number in the first column matches will the data in the other two columns be identical. if not when they combine do you want the information in one sheet to override the other sheet or do you want the final product to show the information from both sheets in the same line. in separate cells or combined in one cell. one method to do it if all of the information is identical in each worksheet is to in sheet 2 use in D1 =countif('Sheet1'!A:A,A1) copy down to the end of the data use autofilter to select all the zeros and copy thes and paste at the end of the data in sheet 1. "Myles" wrote: I am dealing with 2 Workbooks that have about 40,000 rows each, so it is way too much work to combine, and edit them by hand. I have numbers in the 1st column, 2nd i have Text, and 3rd I have Numbers. One File only has the first 2 columns. I need my other file to be combined w/ this one. The other file has all 3 columns, but a lot of numbers in the 1st column should match each other. So i want them to combine, but have no duplicate numbers. Can anyone help me? thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I combine two Excel Workbooks and delete the duplicate
in file 2
to get the names first (space) last in column 4 in D2 (assuming you have a header) =vlookup(A2,'[book 1]sheet 1!A:B,2,0)&" "&vlookup(A2,'[book 1]sheet 1!A:c,3,0) in E2 to get the info from Book 1 column4 =vlookup(A2,'[book 1]sheet 1!A:D,4,0) and in F2 the info from column 5 =vlookup(A2,'[book 1]sheet 1!A:e,5,0) copy E2:F2 and paste down to end of data. "Myles" wrote: hm okay sorry i wasnt very clear lol.. ok here is what im doing I have 2 files. File 1 has 5 columns and 39739 rows, and File 2 has 3 columns and 31247 rows. The 1st column in both files has account numbers. File 1 has more account numbers, but i only need the 31247 account numbers that are in file 2. When i combine the two files i want the extra from file 1 to not be in there at all. Also File 1 my 2nd and 3rd collumns have 1st and last names. I am trying to combine these by a function called COM something. Its not working. Okay File 1 has all the Balances in it The Balances are my main concern. I dont have the balances in File 2, but i ned them in there, and i need them to match the correct account number. Is there a way i can do this? "bj" wrote: if the number in the first column matches will the data in the other two columns be identical. if not when they combine do you want the information in one sheet to override the other sheet or do you want the final product to show the information from both sheets in the same line. in separate cells or combined in one cell. one method to do it if all of the information is identical in each worksheet is to in sheet 2 use in D1 =countif('Sheet1'!A:A,A1) copy down to the end of the data use autofilter to select all the zeros and copy thes and paste at the end of the data in sheet 1. "Myles" wrote: I am dealing with 2 Workbooks that have about 40,000 rows each, so it is way too much work to combine, and edit them by hand. I have numbers in the 1st column, 2nd i have Text, and 3rd I have Numbers. One File only has the first 2 columns. I need my other file to be combined w/ this one. The other file has all 3 columns, but a lot of numbers in the 1st column should match each other. So i want them to combine, but have no duplicate numbers. Can anyone help me? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine five EXCEL 2003 workbooks into one? | Excel Discussion (Misc queries) | |||
How do I combine five Excel 2003 workbooks? | Excel Worksheet Functions | |||
How do you delete duplicate addresses, but keep duplicate names? | Excel Discussion (Misc queries) | |||
How to combine Excel 2002 files and remove duplicate records? | New Users to Excel | |||
How do I delete duplicate entries in excel? | New Users to Excel |