Delete a line that matches certain criteria!

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/25/2003 11:24:45 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
352 Views

Similar Articles

[PageSpeed] 18

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.

Julian wrote:
> Hi all,
> 
> I currently have a list of all our user names and computers in our
> organization. The computer names all end with a '$' symbol. I would
> like to delete all the rows that contain the '$' symbol. Is this
> 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 in
> capitals (the first row).
> 
> Also is there any way to compre the username to the second row? We tend
> to use the format for user names as FirstS (S been the capilization of
> the surname). If possible id like to filter out all the ones that dont
> match this criteria.
> 
> Please forgive me if i havnt explained myself very well but this is all
> new to me. Oh an sorry about the formatting, couldnt quite get the list
> correct.
> 
> Many Thanks
> 
> Julian
> 
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/25/2003 1:07:54 PM
Correction:
   From the dropdown list in cell C1, choose TRUE

 > Delete the rows that are visible
Debra Dalgleish wrote:
> 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.
> 
> Julian wrote:
> 
>> Hi all,
>>
>> I currently have a list of all our user names and computers in our
>> organization. The computer names all end with a '$' symbol. I would
>> like to delete all the rows that contain the '$' symbol. Is this
>> 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 in
>> capitals (the first row).
>>
>> Also is there any way to compre the username to the second row? We tend
>> to use the format for user names as FirstS (S been the capilization of
>> the surname). If possible id like to filter out all the ones that dont
>> match this criteria.
>>
>> Please forgive me if i havnt explained myself very well but this is all
>> new to me. Oh an sorry about the formatting, couldnt quite get the list
>> correct.
>>
>> Many Thanks
>>
>> Julian
>>
>>
>> ------------------------------------------------
>> ~~ Message posted from http://www.ExcelTip.com/
>> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>>
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/25/2003 1:11:57 PM
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)

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------
"Julian" <Julian.xg63n@excelforum-nospam.com> wrote in message
news:Julian.xg63n@excelforum-nospam.com...
>
> Hi all,
>
> I currently have a list of all our user names and computers in our
> organization. The computer names all end with a '$' symbol. I would
> like to delete all the rows that contain the '$' symbol. Is this
> 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 in
> capitals (the first row).
>
> Also is there any way to compre the username to the second row? We tend
> to use the format for user names as FirstS (S been the capilization of
> the surname). If possible id like to filter out all the ones that dont
> match this criteria.
>
> Please forgive me if i havnt explained myself very well but this is all
> new to me. Oh an sorry about the formatting, couldnt quite get the list
> correct.
>
> Many Thanks
>
> Julian
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
demechanik (4694)
11/25/2003 1:34:25 PM
Reply:

Similar Artilces:

New message To, CC, BCC and Subject fields don't accept 'delete'
Hi all, I'm having a bit of a problem, actually more like an annoyance: whenever I create a new mail (that includes replying, forwarding etc...) I can type in the header field no problem. However, if I hit 'delete' or 'back' it does not deletethe characters in the field, but the ones in the email body! the only way I can get rid of something I already typed in those fields is by highlighting it and typing over it. It started happening recently, without me doing anything special. I have tried deleting the normal.dot, frmcache.dat and outcmd.dat (the last too out of hope r...

Delete From New Mail Alerts?
Is it possible to delete messages from both the New Mail Alert window and also the Open window (when clicking Open in the New Mail Alert the highlighted single message that will appear) I receive alot of email and it would be far easier if I could delete from this feature rather than having to go back into the Message pane find the messages and delete from there. Thank you I think there's a delete icon in the alert window that you can try using "dogsnack" <dogsnack@discussions.microsoft.com> wrote in message news:A811413E-5845-45D0-808B-EF00EF7808DE@microsoft.com... ...

macro to find date format in a cell and delete that entire row
macro to find date format in a cell and delete that entire row i have dates in a column and date keeps changing, i want that if macro find date in a column , it deletes that entire row help me thank u so much everyone for ue hel -- Message posted from http://www.ExcelForum.com try Sub deletedate() For Each c In Selection If IsDate(c) Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "vikram >" <<vikram.15ix9t@excelforum-nospam.com> wrote in message news:vikram.15ix9t@excelforum-nospam.com... > macro to find date format in a ...

Common word in line
I am analyzing a report with about 10000 transactions (Cheques paid) and I want to know which lines contains the word "Rent" or "Rental". Any help would be very much appreciated. Fernando Gomez Assuming your transactions are in col. A, put this in col. B and fill down: =IF(SUM(COUNTIF(A1,{"*rent*","*rental*"}))>0,1,"") Any row with a 1 next to it is means "rent" or "rental" appears. HTH Jason Atlanta, GA >-----Original Message----- >I am analyzing a report with about 10000 transactions (Cheques paid) and ...

How do I get rid of new lines?
I have an excel spreadsheet that has a column where the text has been entered in multiple lines. I need to convert this spreadsheet to a .dbf or an excel document. Whenever I try, I lose the data after the first line. Any ideas on how to keep all of the multi-line data? Thanks. Melanie To clarify, your excel document has "carriage returns" (multiple lines of data) within a single excel cell? Excel's CLEAN function should strip out the carriage returns and leave you with a single, long line of text. Syntax is simple =CLEAN(A1) takes out the carriage returns in cell A1, al...

Network deleted file
Hello, Any one can tell me, if i delte the file or folder to network machine, so where the deleted folder will go in which recycle bin,network machin or local machine. Hello Jiwan, None of both, if you delete a file on a network share it is complete deleted. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT email, only reply to Newsgroups ** HELP us help YOU!!! http://www.blakjak.demon.co.uk/mul_crss.htm > Hello, > > Any one can tell me, if i delte the file ...

insert blank line when text changes
Hi, I was wondering if there was a way to write a macro or something else... so while scrolling down a list everytime the text changes (in this case a name) a blank line would be inserted. Hi try the following macro. It tests column A and inserts a blank row if the values change Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "A").Value <> Cells(row_index + 1, "A").Value Then Cells(row_index + 1, "A").Ent...

Show only Journal entries in Delete bin, sort by deletion date
I posted a number of times about the problems I encountered with Ctrl- D (delete a Journal item) being right beside Ctrl-S (saving a journal item). On occasions where I actually notice an accidental deletion, one thing that could help is to be able to view only journal items in the Delete bin. Is there a way to do this? Another thing that might help is to be able to sort by deletion date. Is there a way to do this? Sometimes, I accidentally delete a journal entry from way back, and that basically means its buried in the Delete bin (which is normally sorted by date). Even if the d...

INDEX-MATCH with cell text driven names
Hi, I've read through a few posts that are close to what I'm trying to do but i can't get it to work. I'm constructing a fantasy F1 spreadsheet while i'm bored off work sick. Sheetnames in workbook DriverSummary, AUS, MAL, CHN, BHR,.....for each round/country of the season. Each sheet race sheet has a column for car number in called AUSarray, MALarray etc. AUSREF is cell A1 on the race sheets My current formula in the diver summary works well and is shown below. I have N() to return 0 when a race hasn't happened yet so that my sum() works. =N(OFFSET(AUSREF;MATCH($A4;AUS...

Prevent users from deleting mail
Hello all...... I'm running Exchange 2003, on Windows 2003. Is it possible to configure a select group of users to prevent them from deleting any of their own mail? If so, how? Thank you for your time. Ed "Ed" <ed.jackson@americantower.com> wrote in news:OU6gtr0bEHA.4032@TK2MSFTNGP11.phx.gbl: > I'm running Exchange 2003, on Windows 2003. Is it possible to > configure > a select group of users to prevent them from deleting any of their own > mail? If so, how? > Thank you for your time. Odd request. I can't think of a way to accomplis...

How do I delete attachments from received emails in Outlook 2007?
In Outlook 2003 you can click on an attachment in an email that you have received and delete or remove it. It doesn't seem that this is possible in Outlook 2007. It still works in 2007. Open message, right click on attachment. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM How ...

Rule for matching *.mydomain.com emails
My mail server sends me all the mail for my domain. How do I create a rule that routes all mail that that does *not* match certain addresses to a particular folder? For example, I want mail sent to me@mydomain.com and info@mydomain.com to go to my inbox, but anythingelse@mydomain.com to go to a different folder. I'm using current versions of Outlook and Exchange. create a rule that moves all mail to mydomain.com to a folder except if to the specific addresses. http://www.outlook-tips.net/howto/rules.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 ...

Deleting company in GP 9
I was creating a company in GP9 when I had a system error so the company was never fully created. Is there any way to delete this company so that GP utilities doesn't keep asking me to update the company? In addition to GP 9, I am running Windows Server 2003 and SQL Server 2005. Inside of GP, the company doesn't exist so that I can't delete it from the tools menu. If I go into SQL Server Management Studio, I can delete the associated databases but GP utilities still asks me to upgrade the company. I can not create a company with the same name in GP utilities so that I can s...

dlookup with multiple criteria
I have a form that needs to look up a "goal" by matching several fields in a table. I can't figure out how to do dlookup with multiple criteria frmManualTaskDataEntry [employee] [date] [mailcode] [state] [disabilityind] [volumecode] tblMailCodeTasks mailcode state disabilityind state goal Buzzmcduffie - You use AND to connect the multiple criteria, and must include proper delimeters for text and date fields. It will look something like this (untested): DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" &...

Error when opening certain Appointments
We just upgraded from CRM 1.2 to 3.0 and everything went smotthly except for one user, whenever we open appointments she created we get the "An error Has occured, Please contact you System Administrator" All her completed appointments are fine, phone calls e-mails all fine. Neither her nor anyone else can open those appointments but if we look into her Outlook Calendar, all the info is in there. She can make new appointments just fine, and can delete these old ones. No other users are experiancing this. Any ideas, are there log files that offer more detail to the error? ...

Delete Pages in Publisher 2003
Is there a way to delete many pages at once (More than 2) from a Publisher Doc? This solution was posted by eezzell. I delete multiple pages using a macro First, make a copy of your publisher document. Then, in your copy, create a macro with a name like deletepages. For example to delete pages 10 to 40 you would run this macro: Sub deletepages() Dim firstpage, lastpage As Long firstpage = 10 lastpage = 40 For i = firstpage To lastpage ThisDocument.Pages.Item(firstpage).Delete Next End Sub The first time I tried this, I used the line ThisDocument.Pages.Item(i).Delete forgetting that after a...

React on page deletion
Hi all, my drawings contain a table of contents. I would like to automatically update this table of contents in case the user deletes a page. Can anybody show me how I detect that the user deleted a page (perhaps with some example code)? My trials failed ... Thank you in advance. Regards, Michi ...

Loss of pictures, lines and background shading
Pictures, lines and background shading are not always visible. When the cursor points to where those images are, a label is displayed under the cursor noting what is actually there. The picture's outer border is visible but no picture. When changing screen size, the pictures, lines and shading appear, but go away when scrolling. Must constantly adjust screen size to get the images back. Problem started after loading adobe and creating pdf files from publisher. Tried restoring Publisher but same result. -- Ira Ira T wrote: > Pictures, lines and background shading are not alw...

Embed a 'match' statement in sumproduct?
Excel 2007 I'm trying to help a coworker who wants to generate a few summary pieces of data (like a pivot table, but for only a few categories). The raw data includes both valid and invalid product codes, so these summaries need to exclude the rows with invalid codes and their associated data. Sheet1: Location of this formula. Formula is looking for a total for a specific location Sheet2: Raw data including valid and invalid product code sales Sheet3: List of invalid product codes This formula works, but does not exclude the invalid products: =SUMPRODUCT(('Sheet 2...

Cannot Delete Business Units & Teams
Is it possible to delete Business Units & Teams? No "Somesh Raut" <SomeshRaut@discussions.microsoft.com> wrote in message news:602A3E36-55C7-4EEE-8AFF-6ABC06890E6A@microsoft.com... > Is it possible to delete Business Units & Teams? ...

Line Spacing #2
How do I vary line spacing within a Cel? Bernie Beal. Hi Bernie, Varying line spacing within a cell can be done by hitting the Alt key followed by the Enter key within the cell to force a wrap around of text in that cell. To add more free lines, just keep hitting Alt- Enter. Hope that helps. ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Kim Hartnet www.excelwell-excel-templates.110mb.com "The best place on the net for free & inexpensive Excel templates, macros and spreadsheets" ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~...

how to move lines in conjunction with graphs
Hi, I've drawn a dotted line across my scatter graph but whenever i move the graphs the dotted line stays in its original place. is there a way to make the line part of the graph so that it resizes and moves in conjunction with the graph? Thanks -- Shoque ------------------------------------------------------------------------ Shoque's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36320 View this thread: http://www.excelforum.com/showthread.php?threadid=564595 I assume you mean the line is derived from the Drawing tool bar. To have the chart serve as th...

how to delete 'undeleteable' folder?
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01CB11DA.AC33A6C0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Had a situation where an invalid folder was created. The folder looked = like an ASCII ATL-1 symbol. This is a multiboot system ... could not = delete it in XP or W2K, had to use DOS scandisk, which deleted the = folder.=20 An invalid folder (or file) has been created on occasion before so the = question is, what utility can be used to delete an invalid folder in an = NT OS? Thanks -...

Sheet grid lines
I can see the gridlines in the worksheet, and I have Show and Print gridlines checked, but a print preview or print do not produce the gridlines. What am I missing? Bob Using XL 2007? Sounds like a printer problem. Have you tried 1) Office | Excel Options -> change gridline colour 2) Using borders rather than gridlines best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "The Dog Breeders List" <rltutt@earthlink.net> wrote in message news:5BA13316-2E3A-48FA-BB66-E1F8A89765D1@microsoft.com... >I can see the grid...

Sending mail through pop-up menu to certain recipient
Hello, I send enormous amount of e-mails to just one recipient. In each e-mail is attachment. One in every mail. Do not ask me why. It has to be done in this way. Is it possible to automate a little that process? Something like: click right mouse button, choose 'Send to' and pick 'mail to <my address>'. Outlook window appears and I just need to click 'Send'. Or maybe this can be done even faster? Ideal situation would be if I were able to mark all files, which I want to send, right clicked them, chose mail recipient and the rest (dividing attachments in...