List of just the new items

Posted by David on April 03, 2001 8:25 PM

I have a database of clients which was created by a download from our mainframe. Monthly, I get an updated list of clients from the mainframe. I would like to find a way for excel to compare the latest list with my old list and tell me what new clients were added. Thanks for your help.

Posted by Dave Hawley on April 04, 2001 3:04 AM

Hi David

There are a few ways you could do this, here is but one. I will assume your names are in Columns A and B, with B having the latest entries and both have a heading. Put any heading in C1.

In cell C2 put this Formula:
=COUNTIF($A$2:$A$65536,B2)=1

Now select C2 and double click the fill handle (Small black square, bottom right) this will copy the formula to the last entry in Column B.

Now apply Excels Auto filter and filter column C by FALSE

Select all of the entries in Column C and push F5, select "Special" and then tick "Visible cells" and click OK. Push Ctrl+C to copy and paste where you want them.

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on April 04, 2001 12:13 PM

Assuming that the old list is on a worksheet and the new list on another.
If both lists has (A) a column containing values that uniquely identify the clients (e.g., Client Code or ClientNo) or (B) a column that contains names of the clients.

Caveats
If A is the case, that would be great. If B is the case, you cannot exclude beforehand the existence of distinct clients having the same name.
The following procedure, which is not fully automatic, can be used to create a new clients list.

Select all cells with clients ids (unique numbers or names) on the old list and name the selected range OLDLIST.
Select all cells with clents ids (unique numbers or names) on the new list and name the selected range NEWLIST.

On the sheet with the new list, activate the first empty cell in the first row of client data. Lets suppose that that cell is E1 and the id of the first client is in A1.

in E1 array-enter: =IF(SUM(1*(OLDLIST=A1)),0,1)

To array-enter this formula you need to hit CONTROL+SHIFT+ENTER at the same time.

Copy down the array-entered formula for the rest of ids.

Select the cells in the id column starting with the first id, activate the option Format|Conditional Formatting, choose Formula is for Condition 1, and enter the following formula:

=COUNTIF(OLDLIST,$A1)=0

Activate Font tab and select a color (red, for example).

Select all cells from A1 to E1 and down. Sort on column E (which contains just 1s and 0s) then on A in descending order.

The result is a consecutive list of new clients whose ids are in red.

Hope this helps.

Aladin