On Sun, 04 Apr 2004 23:03:58 +0100, Richard Buttrey
wrote:

On Sun, 04 Apr 2004 01:32:10 GMT, Eric G 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 sheet 1, add another temporary column H1 and use a vlookup
formula to identify the records that have been already deleted on
sheet 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 Sheet 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 sheet 1 Col H to list all the "Deleted" rows, copy the
names from Sheet 1 Col A:B to Sheet 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 Sheet 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 Sheet 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 Sheet 1 Col G to
Sheet 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
__________________________