 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2004Loop to compare and delete or leave
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 : -- Message posted from http://www.ExcelForum.com 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 > Using Excel 97 on WINNT. > [quoted text clipped - 49 lines] > --- > Message posted from http://www.ExcelForum.com/ 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 -- Message posted from http://www.ExcelForum.com 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 > Chuck, > [quoted text clipped - 17 lines] > --- > Message posted from http://www.ExcelForum.com/ Thanks again, Chuck. I'll give the macro thing a try. Lee : -- Message posted from http://www.ExcelForum.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.