matching columns
 

I'm trying to concord data between two excel files, but unfortunately some
information was dropped from one file to the next. I was wondering if there
is a way to sort ranges such that the contents of one column match up to the
contents of another column. So, for example this:

a/1 = a/1
a/2 = a/3
a/3 = b/1
b/2 = b/2
b/3 = c/2
c/1
c/2

would become this:

a/1 = a/1
a/2 = -/-
a/3 = a/3
-/- = b/1
b/2 = b/2
b/3 = -/-
c/1 = -/-
c/2 = c/2

matching columns
 

Not sure if this is what you need or not, but give it a try and see:

Open both workbooks. Choose one of them, we'll call it WB1 and go to cell
B1 on the sheet with the sorted list on it, start a formula by typing an =
symbol in it.

Select the other workbook (WB2), and appropriate sheet and cell A1 in it and
press the [Enter] key to complete the formula. You should end up with a
formula that looks something like:
='[WB2]Sheet1'!$A$1
Edit that formula to remove the $ symbols so it becomes
='[WB2]Sheet1'!A1
You can now fill that formula down the sheet as far as you need to go.

I hope this is what you needed.




"Chris" wrote:

I'm trying to concord data between two excel files, but unfortunately some
information was dropped from one file to the next. I was wondering if there
is a way to sort ranges such that the contents of one column match up to the
contents of another column. So, for example this:

a/1 = a/1
a/2 = a/3
a/3 = b/1
b/2 = b/2
b/3 = c/2
c/1
c/2

would become this:

a/1 = a/1
a/2 = -/-
a/3 = a/3
-/- = b/1
b/2 = b/2
b/3 = -/-
c/1 = -/-
c/2 = c/2

matching columns
 

That sort of seems to be in the right direction, but all it really seems to
be doing is copying the values of the column from workbook 1 over the column
from workbook 2 while leaving the other values untouched. I need the values
of both columns to be identical while still maintaining the values originally
assigned to them

"JLatham" wrote:

Not sure if this is what you need or not, but give it a try and see:

Open both workbooks. Choose one of them, we'll call it WB1 and go to cell
B1 on the sheet with the sorted list on it, start a formula by typing an =
symbol in it.

Select the other workbook (WB2), and appropriate sheet and cell A1 in it and
press the [Enter] key to complete the formula. You should end up with a
formula that looks something like:
='[WB2]Sheet1'!$A$1
Edit that formula to remove the $ symbols so it becomes
='[WB2]Sheet1'!A1
You can now fill that formula down the sheet as far as you need to go.

I hope this is what you needed.

matching columns
 

What is "concord"? What does that mean?

"Chris" wrote:

I'm trying to concord data between two excel files, but unfortunately some
information was dropped from one file to the next. I was wondering if there
is a way to sort ranges such that the contents of one column match up to the
contents of another column. So, for example this:

a/1 = a/1
a/2 = a/3
a/3 = b/1
b/2 = b/2
b/3 = c/2
c/1
c/2

would become this:

a/1 = a/1
a/2 = -/-
a/3 = a/3
-/- = b/1
b/2 = b/2
b/3 = -/-
c/1 = -/-
c/2 = c/2

matching columns
 

It just means making sure all the information is translated properly and
compatible.

For example in this case specifically I'm trying to figure out changes in
the US labor market between 2003 and 2008. They use the same basic
classification standard, so the same industries and occupations will have the
same serial numbers assigned to them. But since there are gaps in the data
the tables will be altered so that, for example, the information on managers
in food manufacturing will be on row 115 in 2003 and 125 in 2008.

Unfortunately this makes it impossible for me to use simple functions to
figure changes from one period to the next. I figure there has to be an easy
way around this problem, i'm just not familiar enough with excel to figure it
out.