You are here:

Excel/Excel-Shorten a long, tedious process

Advertisement


Question
Excel 2003 or 201
I know Excel quite a bit and have a little Access. Self taught from books and online help.

I have a spreadsheet at work that is driving me nuts! Every month I have to manually delete rows that "don't count" in a 90,000 line spreadsheet.  I'm sure there must be a way to get Excel to do some or all of the work for me, but I can't quite figure out how to do it.  Any hint or suggestion will be greatly appreciated.

Here's what I'm working with...

Each row contains County, SSN, AU Number, and Program Type (AF,FS, and MA).  SSN's may have more than 1 Program Type so can be dupliated.  AU number is unique for each row.

I need to capture the rows of ALL SSN's that also have an AF case.  Here's a basic example.

Row #   SSN   Program Type
1   1   AF
2   1   FS
3   1   MA
4   2   FS
5   2   MA
6   3   AF
7   3   FS
8   3   MA
8   4   AF
10   4   MA

I want it to select lines 1,2,3,6,7,8,9,10 but not lines 4,5 because column 3 (Prog. Type) does not contain AF for that SSN.

When I do it manually, I sort by SSN then Program Type, highlight the rows that have Prog Type=AF then delete the rows where the SSN doesn't have a corresponding AF case.  It takes SEVEN HOURS!

I'd love you to write a formula for me (I'm thwarted by too many nested IF's) or should I be looking at a Vlookup function? Maybe an OFFSET? or combination?  Or would an Access query be better and how would I set it up?  I want to use a pivot table, but even that may be going in the wrong direction.

I hope that even made sense.  Please let me know if you have any ideas or suggestions.    

PS.  I can't tell you how much your tutorials have helped me over the years.  You answered an Access problem for me a couple years ago and saved my A LOT of trouble.  Thank you, Thank you, Thank you!

Answer
This would be SOOOOOO much easier to do in an Access query. All you need is to just import all of the data and then set the criteria for the Program Type field to

="AF"

And that's it. That will give you all of the "AF" lines. Then just output the fields you want and copy it back to Excel if you need to. Done.

Now if you still need the other lines for the SSNs with an AF, it gets a little more complicated. I would say use a DLOOKUP to see if that SSN has an AF.

1. Add an ID (Autonumber) field to whatever table has your data.

2. Set up a custom column:

HasAF: Nz(DLOOKUP("ID","MyTable","ProgramType='AF' AND SSN='" & SSNField & "'"),0)

3. Save that query as Q1 (or whatever)

4. Now create a 2nd query and pull in the results from Q1 (you might be able to get away with doing this in one query, but if all else fails use 2).

5. Now just set the CRITERIA for the HasAF field to >0

Basically what this does is, for each record, say "does this SSN have ANY records in the table that say "AF". If so, return the ID. If not, you get a ZERO from the NZ (null to zero) function.

This isn't exactly easy stuff, but it should work... and it's a WHOLE lot easier than trying to do this in Excel. Once you build it, you won't have to do it again every month when you get new data.

Hope this helps.


Be sure to visit my web site and watch my FREE 2-hour-long Microsoft Access Video Tutorial

Cordially,
Richard Rost
599CD Computer Training

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Rost

Expertise

I am the author of The Complete Idiot's Guide to Excel 2010.
 
I am happy to answer any questions about Microsoft Excel. If you have an Excel problem, let me help you with it. Also, please be sure to check the Excel Tips & Tricks and Excel Tutorials sections of my web site.
 
You can also watch my complete Excel 2010 For Beginners tutorial online which is over 90 minutes of FREE videos and an eBook.

Experience

I have been using Microsoft Excel since the very early Windows 3.1 versions. I have been teaching Microsoft Excel in the classroom since 1994, and online through computer tutorials since 2002.

Organizations
I have been volunteering on AllExperts in the categories of Microsoft Access, Visual Basic, and Weight Loss for years.

Publications
I am the author of The Complete Idiot's Guide to Excel 2010. I have created a line of computer tutorials online at www.ExcelLearningZone.com and www.599CD.com/Excel.

Education/Credentials
Personally, I am self taught. I've learned everything I know from books and trial & error. If I don't know the answer, I know how to find it.

Past/Present Clients
I have over 20,000 happy clients worldwide.

©2016 About.com. All rights reserved.