merge

[Sabrina]

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]

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

[Sabrina]

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]

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