Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tamesh
 
Posts: n/a
Default combining and sorting data from two workbooks

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
  #2   Report Post  
Mike
 
Posts: n/a
Default

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

  #3   Report Post  
Tamesh
 
Posts: n/a
Default

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

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
Tamesh
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing unopened worksheet into an open Workbook GrayesGhost Excel Discussion (Misc queries) 24 July 3rd 08 11:06 PM


All times are GMT +1. The time now is 01:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"