Delete a line that matches certain criteria!

[Julian]

Hi all,

I currently have a list of all our user names and computers in ou
organization. The computer names all end with a '$' symbol. I woul
like to delete all the rows that contain the '$' symbol. Is thi
possible? Example of spreadsheet below:

ADAM_BURTON$ ADAM_BURTON$
ADAMB Adam Burton
Pickup Accounts Pickup
Adamben Adam Bennett
AdamS Adam Seiles
ADAMSEILES$ ADAMSEILES$
Adelef Adele Foster
ANDREWLAPTOP$ AG_LAPTOP$
aileenc Aileen Cox
AILEENCOX$ AILEENCOX$
AILSAM Ailsa MacRae
AIMEE Aimee Dyer

So the firslt line that says
ADAM_BURTON$ ADAM_BURTON$

I would like to delete the entire row.

After that I would like to filter out all the usernames that are all i
capitals (the first row).

Also is there any way to compre the username to the second row? We ten
to use the format for user names as FirstS (S been the capilization o
the surname). If possible id like to filter out all the ones that don
match this criteria.

Please forgive me if i havnt explained myself very well but this is al
new to me. Oh an sorry about the formatting, couldnt quite get the lis
correct.

Many Thanks

Julia

 

[Debra Dalgleish]

You can add two columns to your table, to calculate which rows should be
deleted, and which ones have the correct naming convention.

Assuming your data is in columns A and B, with headings in row 1:

Add headings in C1 (Delete) and D1 (Correct)
In cell C2, enter the following formula:

=OR(ISNUMBER(SEARCH("$",A2)),EXACT(A2,UPPER(A2)))

In cell D2, enter the following formula:

=A2=LEFT(B2,SEARCH(" ",B2)-1)&MID(B2,SEARCH(" ",B2)+1,1)

Copy the formulas down to the last row of data.

Select a cell in the table, and choose Data>Filter>AutoFilter
From the dropdown list in cell C1, choose FALSE
Delete the rows that are visible
From the dropdown list in cell D1, choose FALSE
Correct the names that are visible.

 

[Debra Dalgleish]

Correction:
From the dropdown list in cell C1, choose TRUE

Delete the rows that are visible

You can add two columns to your table, to calculate which rows should be
deleted, and which ones have the correct naming convention.

Assuming your data is in columns A and B, with headings in row 1:

Add headings in C1 (Delete) and D1 (Correct)
In cell C2, enter the following formula:

=OR(ISNUMBER(SEARCH("$",A2)),EXACT(A2,UPPER(A2)))

In cell D2, enter the following formula:

=A2=LEFT(B2,SEARCH(" ",B2)-1)&MID(B2,SEARCH(" ",B2)+1,1)

Copy the formulas down to the last row of data.

Select a cell in the table, and choose Data>Filter>AutoFilter
From the dropdown list in cell C1, choose FALSE
Delete the rows that are visible
From the dropdown list in cell D1, choose FALSE
Correct the names that are visible.

 

[Max]

Just some thoughts on the deletion part of your post...

One way using autofilter..

Try this on a *back-up* copy of your book

Assuming your data starts in row2 down

Do an autofilter in row1

Select row1 > Click Data > Filter > Autofilter

Click the drop arrow > select (Custom..)

In the Custom Autofilter dialog:
Under "Show rows where:", make the setting:

equals | *$

Click OK
(this filters out all the rows with $ symbol)

Select all the "blue" numbered filtered rows
Right-click > Delete rows
(this deletes all the rows with $ symbol)

Click on the drop menu > Select (All)

 

[Julian]

Thats great. Thanks very much

 

[Max]

you're welcome, Julian!