combining and sorting data from two workbooks

[Tamesh]

Hi,

Here is my situation. I have two workbooks whose information i have to
combine in to a third workbook. the data in all colums are different except
for the first column. the first column of each contains customers
identification numbers. for the third workbook i will be including ALL the
columns from the first two workbooks. my problem is this: the ID numbers
will not be in the same order in the first two workbooks. is there a way to
check the order in the first two workbooks and create one list in the third
workbook that is sorted. i guess the data in the other columns would have to
be sorted based on the ID number sort. does this make sense?

thank you in advance

[Mike]

Is there another column that is fairly unique that you can sort by instead?
You can always resort the third workbook by ID later.

"Tamesh" wrote:

> Hi,
>
> Here is my situation. I have two workbooks whose information i have to
> combine in to a third workbook. the data in all colums are different except
> for the first column. the first column of each contains customers
> identification numbers. for the third workbook i will be including ALL the
> columns from the first two workbooks. my problem is this: the ID numbers
> will not be in the same order in the first two workbooks. is there a way to
> check the order in the first two workbooks and create one list in the third
> workbook that is sorted. i guess the data in the other columns would have to
> be sorted based on the ID number sort. does this make sense?
>
> thank you in advance

[Tamesh]

No, that is the only identifier available.

"Mike" wrote:

> Is there another column that is fairly unique that you can sort by instead?
> You can always resort the third workbook by ID later.
>
> "Tamesh" wrote:
>
> > Hi,
> >
> > Here is my situation. I have two workbooks whose information i have to
> > combine in to a third workbook. the data in all colums are different except
> > for the first column. the first column of each contains customers
> > identification numbers. for the third workbook i will be including ALL the
> > columns from the first two workbooks. my problem is this: the ID numbers
> > will not be in the same order in the first two workbooks. is there a way to
> > check the order in the first two workbooks and create one list in the third
> > workbook that is sorted. i guess the data in the other columns would have to
> > be sorted based on the ID number sort. does this make sense?
> >
> > thank you in advance

[Dave Peterson]

Are the customer ID numbers unique?

No duplicates in column A of each of the worksheets?

If you have duplicates, then ignore the rest of this message.

If there are no duplicates, you could create a new worksheet.
copy the data in column A of each sheet to column A of the new worksheet
(Include only one header row.)
Now you'll have a giant list (some with duplicates, some without).

You can distill that to just unique entries by using data|Filter|advanced filter
Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Place the unique list in column B
then you can delete column A (we're done with it)

Then you can use =vlookup() to retrieve all the values from each worksheet
Debra's site again for instructions:
http://www.contextures.com/xlFunctions02.html

I'd use the version of the formula:
=if(vlookup(a2,sheet1!a:z,2,false)="",na(),vlookup(a2,sheet1!a:z,2,false))

The 2 means to bring back the 2nd column. Copy it over to the right as many
columns as you need.

And change the formula to point to sheet2 when you need to retrieve the stuff
from the other sheet.

After you get the formulas dragged down the range, you can convert the formulas
to values (edit|copy, edit|paste special|values).

Then select those columns and do:
edit|replace
what: #n/a
with: (leave blank)
replace all

to make it look pretty.

==
You may have to apply formatting to some of your columns (times/dates/currency)
to make them look even prettier.


Tamesh wrote:
>
> No, that is the only identifier available.
>
> "Mike" wrote:
>
> > Is there another column that is fairly unique that you can sort by instead?
> > You can always resort the third workbook by ID later.
> >
> > "Tamesh" wrote:
> >
> > > Hi,
> > >
> > > Here is my situation. I have two workbooks whose information i have to
> > > combine in to a third workbook. the data in all colums are different except
> > > for the first column. the first column of each contains customers
> > > identification numbers. for the third workbook i will be including ALL the
> > > columns from the first two workbooks. my problem is this: the ID numbers
> > > will not be in the same order in the first two workbooks. is there a way to
> > > check the order in the first two workbooks and create one list in the third
> > > workbook that is sorted. i guess the data in the other columns would have to
> > > be sorted based on the ID number sort. does this make sense?
> > >
> > > thank you in advance

--

Dave Peterson

[Tamesh]

Once the first column is created/sorted from the two workbooks, how can i go
about bringing over ALL of the remaining columns that would correspond with
the ID numbers in the first column? thanks




"Dave Peterson" wrote:

> Are the customer ID numbers unique?
>
> No duplicates in column A of each of the worksheets?
>
> If you have duplicates, then ignore the rest of this message.
>
> If there are no duplicates, you could create a new worksheet.
> copy the data in column A of each sheet to column A of the new worksheet
> (Include only one header row.)
> Now you'll have a giant list (some with duplicates, some without).
>
> You can distill that to just unique entries by using data|Filter|advanced filter
> Debra Dalgleish has some nice instructions at:
> http://www.contextures.com/xladvfilter01.html#FilterUR
>
> Place the unique list in column B
> then you can delete column A (we're done with it)
>
> Then you can use =vlookup() to retrieve all the values from each worksheet
> Debra's site again for instructions:
> http://www.contextures.com/xlFunctions02.html
>
> I'd use the version of the formula:
> =if(vlookup(a2,sheet1!a:z,2,false)="",na(),vlookup(a2,sheet1!a:z,2,false))
>
> The 2 means to bring back the 2nd column. Copy it over to the right as many
> columns as you need.
>
> And change the formula to point to sheet2 when you need to retrieve the stuff
> from the other sheet.
>
> After you get the formulas dragged down the range, you can convert the formulas
> to values (edit|copy, edit|paste special|values).
>
> Then select those columns and do:
> edit|replace
> what: #n/a
> with: (leave blank)
> replace all
>
> to make it look pretty.
>
> ==
> You may have to apply formatting to some of your columns (times/dates/currency)
> to make them look even prettier.
>
>
> Tamesh wrote:
> >
> > No, that is the only identifier available.
> >
> > "Mike" wrote:
> >
> > > Is there another column that is fairly unique that you can sort by instead?
> > > You can always resort the third workbook by ID later.
> > >
> > > "Tamesh" wrote:
> > >
> > > > Hi,
> > > >
> > > > Here is my situation. I have two workbooks whose information i have to
> > > > combine in to a third workbook. the data in all colums are different except
> > > > for the first column. the first column of each contains customers
> > > > identification numbers. for the third workbook i will be including ALL the
> > > > columns from the first two workbooks. my problem is this: the ID numbers
> > > > will not be in the same order in the first two workbooks. is there a way to
> > > > check the order in the first two workbooks and create one list in the third
> > > > workbook that is sorted. i guess the data in the other columns would have to
> > > > be sorted based on the ID number sort. does this make sense?
> > > >
> > > > thank you in advance
>
> --
>
> Dave Peterson
>