Re: Comparing two sets of data

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Richard Buttrey (chaos.theory.nospam.removethis_at_zen.co.uk)
Date: 04/04/04 

Date: Sun, 04 Apr 2004 23:46:44 +0100

On Sun, 04 Apr 2004 23:03:58 +0100, Richard Buttrey
<chaos.theory.nospam.removethis@zen.co.uk> wrote:

>On Sun, 04 Apr 2004 01:32:10 GMT, Eric G <EricG@ether.net> wrote:
>
>>Hi Max,
>>
>> Sorry I forgot to mention one other important things which will throw
>>a monkey wrench into our calculations.
>>
>>The new list (Sheet2) has two things done to it.
>>1. New students have been added to it and the list sorted alpha.
>>2. Some 50+ students have been deleted.
>>
>>So.... I need placeholders for those students who have been deleted.
>>The placeholders can be something like "Deleted" for each deleted
>>record.
>>I'm hoping there's a way to do this.
>>
>> Thanks! Eric
>
>The way I'd approach this is as follows.
>
>1. On both sheets add a new temporary column (Col G).
>Use Col G to concatenate the last and first names.
>i.e. =A1&B1
>
>2. On *** 1, add another temporary column H1 and use a vlookup
>formula to identify the records that have been already deleted on
>*** 2
>
>i.e. H1
>
>=IF(ISERROR(VLOOKUP(G1,Sheet2!$G$1:$G$670,1,FALSE)),"Deleted",VLOOKUP(G1,Sheet2!$G$1:$G$670,1,FALSE))
>
>Copy this down all 600 rows.
>
>3. Use a similar techinique on Sheet2 to identify all the New
>Students. i.e. in H1 enter
>
>=IF(ISERROR(VLOOKUP(G1,Sheet1!$G$1:$G$600,1,FALSE)),"New",VLOOKUP(G1,Sheet1!$G$1:$G$600,1,FALSE))
>
>4. Filter *** 2 on Col H to list all the "New' Records, then copy
>the word "ZZZNew" down Col C against all the filtered records. and
>remove the fiter.
>
>5. Filter *** 1 Col H to list all the "Deleted" rows, copy the
>names from *** 1 Col A:B to *** 2 Col A:B underneath the last
>record. (i.e.to row A671) and then copy the word "Deleted" into C671
>and copy down as far as necessary.
>
>6. Now Sort the whole of *** 2 on Column C (first sort) and Col G
>(second sort). This will put all the New students at the bottom in
>alpha order.
>
>7. Now sort all the *** 2 records apart from those you've just
>sorted in step 6, using Col G as the sort.
>
>8. Finally delete your temporary columns G & H
>
>Takes longer to write this than do it, but I'll be interested to know
>if there are other quicker/elegant methods, since this is a general
>technique I seem to find myself using fairly regularly.

Oops - spoke too soon - a correction......

Step 3 should have "ZZZNew" instead of just "New" in the formula.

Step 4 should be ignored.

Step 5 should also copy the concatenated names from *** 1 Col G to
*** 2 Col G

Step 6 reference to First sort should be Col H (placing all new
students at the bottom), with second sort on Col G to put them in
alpha order

Step 7 should sort all records apart from those with "ZZZNew" in Col H

Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Quantcast