Find and delete lines

%3CLINGO-SUB%20id%3D%22lingo-sub-2171065%22%20slang%3D%22en-US%22%3EFind%20and%20delete%20lines%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2171065%22%20slang%3D%22en-US%22%3E%3CP%3EQuicken%20exports%20year-end%20data%20which%20I%20want%20to%20collect%20for%20a%20decade.%26nbsp%3B%20I%20wish%20to%20compare%20expenses%20by%20category%20and%20subcategory%20for%20each%20year%20in%20a%20decade.%26nbsp%3B%20It%20has%20the%20appropriate%20report%2C%20but%20when%20the%20Quicken%20file%20is%20exported%20it%20includes%20all%20transactions.%26nbsp%3B%20There%20is%20no%20option%20to%20export%20only%20the%20categories%20%26amp%3B%20subcategories%20with%20their%20amounts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20delete%20about%202000%20lines%20of%20transactions%20automatically%2C%20each%20year%20with%20a%20different%20number%20of%20transactions%20per%20category%2Fsubcategory.%26nbsp%3B%20(Otherwise%20I%20must%20enter%20115%20numbers%20manually%20for%20each%20of%20the%2010%20years.)%26nbsp%3B%20The%20lines%20I%20wish%20to%20delete%20have%20two%20entries%20in%20the%20columns%2C%20each%20with%20this%20format%3A%20date%20and%20amount.%20So%20lines%20have%2C%20for%20example%2C%201%2F30%2F2010%20in%20one%20column%20and%20%24100.99%20in%20the%20second%20column.%26nbsp%3B%20Nothing%20else.%3C%2FP%3E%3CP%3EI%20want%20to%20search%20for%20those%202000%20lines%20and%20delete%20them.%26nbsp%3B%20It%20is%20easy%20to%20replace%20the%20date%20with%20nothing.%20But%20that%20leaves%20a%20number%20in%20the%20second%20column%20which%20is%20right%20under%20a%20number%20I%20need%20to%20keep.%20I%20cannot%20just%20search%20for%20numbers.%3C%2FP%3E%3CP%3EDoes%20Excel%20provide%20us%20with%20the%20ability%20to%20search%20for%20specific%20lines%20and%20then%20delete%20the%20entire%20line%3F%3C%2FP%3E%3CP%3EI%20used%20to%20write%20macros%20in%20early%20spreadsheets%2C%20but%20have%20never%20written%20one%20that%20works%20in%20Excel.%20I%20rarely%20need%20macros%20so%20I%20never%20pursued%20the%20topic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2171065%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2171102%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20and%20delete%20lines%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2171102%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F980708%22%20target%3D%22_blank%22%3E%40nantucketbobmentions%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20does%20the%20full%20export%20look%20like%3F%20There%20must%20be%20some%20information%20concerning%20budget%20categories%20and%20subcategories.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20use%20Quicken%2C%20but%20do%20use%20Excel%20to%20track%20my%20own%20income%20and%20expenses.%20I%20WANT%20it%20to%20be%20a%20full%20list%20of%20all%20transactions%2C%20and%20with%20each%20transaction%20I%20have%20columns%20for%20category%20and%20subcategory.%26nbsp%3B%20It%20then%20is%20simplicity%20itself%20to%20use%20Excel's%20Pivot%20Table%20capability%20to%20produce%20exactly%20that%20year-by-year%20report%20you%20seem%20to%20desire.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20macro%2C%20no%20VBA.%20Excel%20does%20the%20work.%20Does%20Quicken%20let%20you%20export%20such%20a%20set%20of%20data%3F%20That%20is%20to%20say%2C%20an%20export%20with%20at%20least%20the%20following%20columns%2C%20transaction%20by%20transaction%3A%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3EDate%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3EAmount%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3EPayee%20(Payor)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3ECategory%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3ESubCategory%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Quicken exports year-end data which I want to collect for a decade.  I wish to compare expenses by category and subcategory for each year in a decade.  It has the appropriate report, but when the Quicken file is exported it includes all transactions.  There is no option to export only the categories & subcategories with their amounts.

 

I need to delete about 2000 lines of transactions automatically, each year with a different number of transactions per category/subcategory.  (Otherwise I must enter 115 numbers manually for each of the 10 years.)  The lines I wish to delete have two entries in the columns, each with this format: date and amount. So lines have, for example, 1/30/2010 in one column and $100.99 in the second column.  Nothing else.

I want to search for those 2000 lines and delete them.  It is easy to replace the date with nothing. But that leaves a number in the second column which is right under a number I need to keep. I cannot just search for numbers.

Does Excel provide us with the ability to search for specific lines and then delete the entire line?

I used to write macros in early spreadsheets, but have never written one that works in Excel. I rarely need macros so I never pursued the topic.

 

1 Reply

@nantucketbobmentions 

 

What does the full export look like? There must be some information concerning budget categories and subcategories.

 

I don't use Quicken, but do use Excel to track my own income and expenses. I WANT it to be a full list of all transactions, and with each transaction I have columns for category and subcategory.  It then is simplicity itself to use Excel's Pivot Table capability to produce exactly that year-by-year report you seem to desire.

 

No macro, no VBA. Excel does the work. Does Quicken let you export such a set of data? That is to say, an export with at least the following columns, transaction by transaction:

Date

Amount

Payee (Payor)

Category

SubCategory