Hi all,

and thanks for any help you can give me with regards to my problem.
Hokay, on with the thread...


I have a simple worksheet open. In it are six columns of data,
gathered and imported from two different csv sources. Columns A-C are
from one source, and contain two names (first(A) and last(B)), and a
redundant column (C). Columns D-F are from the second source, and
contain the exact same names as the first source in columns D and E,
and extra required data in the third column (F).

Now the problem is that whilst these lists are duplicates, the sort
order of these two sources is completely different with each. Source
one, for example, has JOE BLOGGS as the first line entry, whereas
source two has him as entry 461. It is the source one layout I
require, but it is vital that I expand it with the data from source two
that is held in column F.

Now what I need to do is find some quick and easy way of getting Excel
to check columns A and B against D and E and, if there is a match,
populating column C with the data from column F.

And no, simply using the sort order in columns D to F and replacing
columns A to C entirely is not - sadly - an option. The data _must_ be
in the current sort order as it appears in columns A to C.

Can anyone help?


--
SmokingMirror
------------------------------------------------------------------------
SmokingMirror's Profile: http://www.excelforum.com/member.php...o&userid=12225
View this thread: http://www.excelforum.com/showthread...hreadid=565076

So you can use column C to hold a formula--the preexisting values can be lost?

If yes:

=index($f$1:$f$100,match(1,(a1=$d$1:$d$100)*(b1=$e $1:$e$100),0))

This is an array formula. 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.)

Adjust the range to match--but you can't use the whole column.

And drag down the column.

I'd convert to values (edit|copy, edit|paste special|values), and then
Edit|replace to remove any errors.

SmokingMirror wrote:

Hi all,

and thanks for any help you can give me with regards to my problem.
Hokay, on with the thread...

I have a simple worksheet open. In it are six columns of data,
gathered and imported from two different csv sources. Columns A-C are
from one source, and contain two names (first(A) and last(B)), and a
redundant column (C). Columns D-F are from the second source, and
contain the exact same names as the first source in columns D and E,
and extra required data in the third column (F).

Now the problem is that whilst these lists are duplicates, the sort
order of these two sources is completely different with each. Source
one, for example, has JOE BLOGGS as the first line entry, whereas
source two has him as entry 461. It is the source one layout I
require, but it is vital that I expand it with the data from source two
that is held in column F.

Now what I need to do is find some quick and easy way of getting Excel
to check columns A and B against D and E and, if there is a match,
populating column C with the data from column F.

And no, simply using the sort order in columns D to F and replacing
columns A to C entirely is not - sadly - an option. The data _must_ be
in the current sort order as it appears in columns A to C.

Can anyone help?

--
SmokingMirror
------------------------------------------------------------------------
SmokingMirror's Profile: http://www.excelforum.com/member.php...o&userid=12225
View this thread: http://www.excelforum.com/showthread...hreadid=565076

--

Dave Peterson

You could add an extra column to each block and set formula to string first
name and surname (=A2&B2) then use a Vlookup to find the match and output
value from last column into another column.....Bit fiddly but will do what
you want

HTH - Sheila
www.c-i-m-s.com

"SmokingMirror" wrote:


Hi all,

and thanks for any help you can give me with regards to my problem.
Hokay, on with the thread...


I have a simple worksheet open. In it are six columns of data,
gathered and imported from two different csv sources. Columns A-C are
from one source, and contain two names (first(A) and last(B)), and a
redundant column (C). Columns D-F are from the second source, and
contain the exact same names as the first source in columns D and E,
and extra required data in the third column (F).

Now the problem is that whilst these lists are duplicates, the sort
order of these two sources is completely different with each. Source
one, for example, has JOE BLOGGS as the first line entry, whereas
source two has him as entry 461. It is the source one layout I
require, but it is vital that I expand it with the data from source two
that is held in column F.

Now what I need to do is find some quick and easy way of getting Excel
to check columns A and B against D and E and, if there is a match,
populating column C with the data from column F.

And no, simply using the sort order in columns D to F and replacing
columns A to C entirely is not - sadly - an option. The data _must_ be
in the current sort order as it appears in columns A to C.

Can anyone help?


--
SmokingMirror
------------------------------------------------------------------------
SmokingMirror's Profile: http://www.excelforum.com/member.php...o&userid=12225
View this thread: http://www.excelforum.com/showthread...hreadid=565076