Loop to compare and delete or leave

[Lee Jeffery]

Using Excel 97 on WINNT.

I regularly receive a report and I need to identify if something ne
has appeared on the latest report. There are 2 identifiers I need t
use to decide whether to delete the compared row of info or keep it.
What I do is open the most recent report (call it ReportNew,xls for th
purposes of my question) and save it under a new name (Comparison.xls)
I then open the previous report (ReportOld.xls) to begin m
comparison. Up to this point, I'm cruising on automatic but from her
on in, I go to manual because I don't know how to write a loop to hel
me automate the next bit.
I want to compare the first value in Column B (B2) from Compariso
against all values in column B in ReportOld. This value is always an
digit number. If it doesn't exist in ReportOld, I need go no furthe
and just leave the row alone and move to the next Column B value. I
the value of B2 exists in ReportOld, I then need to check a secon
identifier which is always listed in Column D of both reports. Thi
second identifier consists of digits and text. If this value exists i
both reports (which means that it has previously been identified), the
I delete the row from Comparison. If the second value is not i
Comparison, this means that this is a new item and I want to leave i
alone and move to check the next cell in Column B of Comparison agains
all values in Column B of ReportOld and so on).
At the moment I copy the value and run a find function manually the
view the row to check for the second identifier but this is tim
consuming and mind-numbing, and I have been known to accidentall
delete information because my brain has stopped functioning. Pleas
help. I've tried to list this in a flow chart style to try to make m
descriptions a little clearer:
Does value in B2 of Comparison appear in Column B of ReportOld?
No - Leave in place and begin to compare value of B3 against ReportOld
Yes - Now check if value in D2 appears in D of ReportOld on same row a
value in B2
Does value of B2 and D2 in Comparison appear in same row in ReportOld?
Yes - delete entire row in Comparison
No - leave row intact and begin to compare value in B of next ro
down.
I've searched for assistance where others have posted workboo
comparison questions but none seem to suit my situation. I have trie
to modify some loops but I'm not even sure which type of loop I need
there are no empty rows or cells in these reports as they have bot
been formatted to exclude any blanks. At the end of this I save al
changes to the Comparison workbook so that I now have 3 workbooks
ReportNew.xls, ReportOld.xls, and Comparison.xls. All 3 are needed fo
further reporting.
Any assistance would be greatly appreciated.

Many thanks in advance.

Lee :

 

[CLR]

Hi............
I think I would Concatenate the values in your columns B and D on both the
old and new reports and then use the VLOOKUP to tag the items in the new
report if they exist in the old one..............

Vaya con Dios,
Chuck, CABGx3

 

[Lee Jeffery]

Chuck,

Thank you for your suggestion but I am unsure about what you're tellin
me. Is this something I can do with VBA or is this something which ca
only be done on the Excel sheet?

I'm trying to automate the entire procedure as much as possible as I a
not the only one who needs to action this report and, as inexperience
as I am, I can actually open Excel as well as spell it (ta da..!)
Others I work with have difficulty with these two things and need mor
help.

Do you have any further suggestions I might be able to use? Any furthe
explanation of your first suggestion would be greatly appreciated.

Many thanks.

Lee :confused

 

[CLR]

Hi Lee...........

Sorry, the solution I offered was just for use on a spreadsheet, but of
course, almost anything that can be done on a spreadsheet can be "recorded"
into a macro, if you wish. I was only describing a scheme whereby you could
determine and flag which items were new and which were previously received,
according to your dual-column criteria. To automate the whole thing is a
bit of a project, but you could start by doing Tools > Macro > Record new
macro......and then running through all the steps necessary to do the
comparison and then the sorting and elimination of the unwanted rows
manually once, then Stopping the recording. This will give you a macro of
what you just did. Then, with perhaps a bit of tweaking of the code, you
could tune it up. If you are wanting a purely VBA solution to your inquiry,
then perhaps you might find more better answers at the other newsgroup ,
microsoft.public.excel.programming. If you don't have any better luck
getting the answers you want, you could send a workbook with both sheets in
it to my home addy, (do not post it), and I will take a look at it for you.

Vaya con Dios,
Chuck, CABGx3

 

[Lee Jeffery]

Thanks again, Chuck.

I'll give the macro thing a try.

Lee :