Filtered Sorting - Please Help!

[SmokingMirror]

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 ar
from one source, and contain two names (first(A) and last(B)), and
redundant column (C). Columns D-F are from the second source, an
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 sor
order of these two sources is completely different with each. Sourc
one, for example, has JOE BLOGGS as the first line entry, wherea
source two has him as entry 461. It is the source one layout
require, but it is vital that I expand it with the data from source tw
that is held in column F.

Now what I need to do is find some quick and easy way of getting Exce
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 replacin
columns A to C entirely is not - sadly - an option. The data _must_ b
in the current sort order as it appears in columns A to C.

Can anyone help

 

[Dave Peterson]

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.

 

[Guest]

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