Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Importing unopened worksheet into an open Workbook | Excel Discussion (Misc queries) |