Excel 2000 Extracting Data

  • Thread starter Thread starter mab
  • Start date Start date
M

mab

Hi,

Can anyone tell me how I could easily extract the name and email field
from this sample worksheet layout? Can I use a Pivot table?
Below is a sample of a very large worksheet.

Student Name Parts Tuition Discounts Tuition

E. Hanover #0473


Alario, Olga AFLR $0.00 $0.00 $0.00
Address: xxxxx
Status:DROPPED Phone: xxxxx
E-mail: (e-mail address removed)
College: xxxxx


Ammerman, Bruce AFLR $560.00 $0.00 $560.00
Address: xxxxxxx
Status:ENROLLED Phone: xxxxxxx
E-mail: (e-mail address removed)
College: xxxxxx
 
Check out help info on MATCH and OFFSET. I'd try to find
status and offset -2 for the name and +1,+1 (maybe) for
the email address. I can't see the spacing exactly in this
message board.
 
If it's just a one time thing, I think I'd do it manually. But do it against a
copy of the worksheet--just in case.

Insert a helper column in column A.

Apply Data|filter|autofilter to your range (include column A, too).

Filter on column that contains the email address. Choose custom, contains and
type in "@" w/o the quotes.

Select your range A2:A(bottom of data) and type:
Keep
and hit ctrl-enter. Now those visible cells will have a "keep" in them.

(It might be better using the "Email:" value and keeping those rows--there might
be some without email ids.)

Filter on the student name and choose blanks.
Select A2:Axxx and fill it with "delete" (ctrl-enter again)

Filter on the student name and choose custom, begins with, "Status:".
Fill those visible cells with "delete", too.

Anytime you want, do a Data|filter|showall, then use the filter in column A to
show Deletes and delete those visible rows.

After you get rid of all the trashy rows, you should be left with a pair of rows
for each student--one for name and one for email address.

You can use this technique at Debra Dalgleish's site to copy the email address
to the previous row:

http://www.contextures.com/xlDataEntry02.html

After you've converted the formulas used in Deb's technique, then you can filter
once more on the student names and show just the blanks. Delete those rows.

Delete column A and, drum roll, you're done!

Save often--so you can go back and retrieve if you delete too much.
 
Back
Top