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 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?

[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.

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

[Sheila D]

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
>
>