Home |
Search |
Today's Posts |
#1
|
|||
|
|||
merge
I have two worksheets, some headings are the same, some not. I have to merge
them into a new worksheet, for example: 1st Worksheet: Province City Number of Sales ON Toronto 51 ON Ottawa 44 NY New York 101 Another worksheet: Province City Number of books ON Kingston 1345 ON Ottawa 34343 NY New York 56575 QUE Montreal 33434 Merge to a new worksheet: Province City Number of Sales Number of books ON Toronto 51 ON Ottawa 44 34343 NY New York 101 56575 ON Kingston 1345 QUE Montreal 33434 The way I used was pretty tedious. 1. Intert Columns, make sure both of worksheets have the same structures. 2. Copy one sheet into another sheet, then sort by "City" 3. Then intert one more column, and fomula" =if(B2=b1, 0,1)" to check the duplicates. 4. Check all "0"s, manually copy the data, make sure all of the data entered into the same row. After merge one row, then delete the row above..... Is there any way to speed up the whole process. Thanx! |
#2
|
|||
|
|||
Maybe more tedious????
I'd create a new worksheet (sheet3?) Back to sheet1. select columns a:b and copy|paste into A1 of sheet3 Then back to sheet2 and copy the data to the bottom of that new list (avoid the header rows). Now you have a big ole giant list (with duplicates on sheet3). Select sheet3 select columns A:B Data|filter|advanced filter check unique records only and choose "Copy to another location" Use C1 for that output range. Delete columns A:B (done with them). Tnen in C1:d1, put your headers: In C2, put this: =INDEX(Sheet1!C1:C9999,MATCH(1,(A2=Sheet1!A1:A9999 )*(B2=Sheet1!B1:B9999),0)) and in D2, put this: =INDEX(Sheet2!C1:C9999,MATCH(1,(A2=Sheet2!A1:A9999 )*(B2=Sheet2!B1:B9999),0)) Both of these are array formulas. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) (remember to adjust 9999 to go far enough down each sheet for all the data.) ==== Now select columns C:D. edit|copy edit|paste special|values and (finally!) with columns C:D selected edit|replace what: #n/a with: (leave blank) replace all (Just to clean it up.) Sabrina wrote: I have two worksheets, some headings are the same, some not. I have to merge them into a new worksheet, for example: 1st Worksheet: Province City Number of Sales ON Toronto 51 ON Ottawa 44 NY New York 101 Another worksheet: Province City Number of books ON Kingston 1345 ON Ottawa 34343 NY New York 56575 QUE Montreal 33434 Merge to a new worksheet: Province City Number of Sales Number of books ON Toronto 51 ON Ottawa 44 34343 NY New York 101 56575 ON Kingston 1345 QUE Montreal 33434 The way I used was pretty tedious. 1. Intert Columns, make sure both of worksheets have the same structures. 2. Copy one sheet into another sheet, then sort by "City" 3. Then intert one more column, and fomula" =if(B2=b1, 0,1)" to check the duplicates. 4. Check all "0"s, manually copy the data, make sure all of the data entered into the same row. After merge one row, then delete the row above..... Is there any way to speed up the whole process. Thanx! -- Dave Peterson |
#3
|
|||
|
|||
Thanx, Dave,
The thing is I am not familar with both of functions: index and match. But I would like to practise both of them from now on. "Dave Peterson" wrote: Maybe more tedious???? I'd create a new worksheet (sheet3?) Back to sheet1. select columns a:b and copy|paste into A1 of sheet3 Then back to sheet2 and copy the data to the bottom of that new list (avoid the header rows). Now you have a big ole giant list (with duplicates on sheet3). Select sheet3 select columns A:B Data|filter|advanced filter check unique records only and choose "Copy to another location" Use C1 for that output range. Delete columns A:B (done with them). Tnen in C1:d1, put your headers: In C2, put this: =INDEX(Sheet1!C1:C9999,MATCH(1,(A2=Sheet1!A1:A9999 )*(B2=Sheet1!B1:B9999),0)) and in D2, put this: =INDEX(Sheet2!C1:C9999,MATCH(1,(A2=Sheet2!A1:A9999 )*(B2=Sheet2!B1:B9999),0)) Both of these are array formulas. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) (remember to adjust 9999 to go far enough down each sheet for all the data.) ==== Now select columns C:D. edit|copy edit|paste special|values and (finally!) with columns C:D selected edit|replace what: #n/a with: (leave blank) replace all (Just to clean it up.) Sabrina wrote: I have two worksheets, some headings are the same, some not. I have to merge them into a new worksheet, for example: 1st Worksheet: Province City Number of Sales ON Toronto 51 ON Ottawa 44 NY New York 101 Another worksheet: Province City Number of books ON Kingston 1345 ON Ottawa 34343 NY New York 56575 QUE Montreal 33434 Merge to a new worksheet: Province City Number of Sales Number of books ON Toronto 51 ON Ottawa 44 34343 NY New York 101 56575 ON Kingston 1345 QUE Montreal 33434 The way I used was pretty tedious. 1. Intert Columns, make sure both of worksheets have the same structures. 2. Copy one sheet into another sheet, then sort by "City" 3. Then intert one more column, and fomula" =if(B2=b1, 0,1)" to check the duplicates. 4. Check all "0"s, manually copy the data, make sure all of the data entered into the same row. After merge one row, then delete the row above..... Is there any way to speed up the whole process. Thanx! -- Dave Peterson |
#4
|
|||
|
|||
You could look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html and http://www.contextures.com/xlFunctions03.html Lots of nice instructions on that site. Sabrina wrote: Thanx, Dave, The thing is I am not familar with both of functions: index and match. But I would like to practise both of them from now on. "Dave Peterson" wrote: Maybe more tedious???? I'd create a new worksheet (sheet3?) Back to sheet1. select columns a:b and copy|paste into A1 of sheet3 Then back to sheet2 and copy the data to the bottom of that new list (avoid the header rows). Now you have a big ole giant list (with duplicates on sheet3). Select sheet3 select columns A:B Data|filter|advanced filter check unique records only and choose "Copy to another location" Use C1 for that output range. Delete columns A:B (done with them). Tnen in C1:d1, put your headers: In C2, put this: =INDEX(Sheet1!C1:C9999,MATCH(1,(A2=Sheet1!A1:A9999 )*(B2=Sheet1!B1:B9999),0)) and in D2, put this: =INDEX(Sheet2!C1:C9999,MATCH(1,(A2=Sheet2!A1:A9999 )*(B2=Sheet2!B1:B9999),0)) Both of these are array formulas. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) (remember to adjust 9999 to go far enough down each sheet for all the data.) ==== Now select columns C:D. edit|copy edit|paste special|values and (finally!) with columns C:D selected edit|replace what: #n/a with: (leave blank) replace all (Just to clean it up.) Sabrina wrote: I have two worksheets, some headings are the same, some not. I have to merge them into a new worksheet, for example: 1st Worksheet: Province City Number of Sales ON Toronto 51 ON Ottawa 44 NY New York 101 Another worksheet: Province City Number of books ON Kingston 1345 ON Ottawa 34343 NY New York 56575 QUE Montreal 33434 Merge to a new worksheet: Province City Number of Sales Number of books ON Toronto 51 ON Ottawa 44 34343 NY New York 101 56575 ON Kingston 1345 QUE Montreal 33434 The way I used was pretty tedious. 1. Intert Columns, make sure both of worksheets have the same structures. 2. Copy one sheet into another sheet, then sort by "City" 3. Then intert one more column, and fomula" =if(B2=b1, 0,1)" to check the duplicates. 4. Check all "0"s, manually copy the data, make sure all of the data entered into the same row. After merge one row, then delete the row above..... Is there any way to speed up the whole process. Thanx! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different graphic for each record in mail merge document | Charts and Charting in Excel | |||
How do I do an excel merge like a word mailmerge with another exc. | Excel Discussion (Misc queries) | |||
MAIL MERGE PROBLEMS | Excel Discussion (Misc queries) | |||
Serious Mail Merge Problem | Excel Discussion (Misc queries) | |||
Extract Data for Mail Merge | Excel Discussion (Misc queries) |