looking for Excel function that... - WickedFire - Affiliate Marketing Forum

rgordon83 · 07-25-2008, 01:17 AM

deletes entire ROWS that contain a certain keyword anywhere in the row(not just cell contents)

anyone know if this function exists and what it's called?

Skittled · 07-26-2008, 06:12 AM

Sounds more database-y than excel-esque - could you import it into access/other database?

Long story short, not many functions can edit cells other than themselves, so deleting a row doesn't sound like a function thing. I know nothing about macros, but you/someone might, and I assume they could do the job.

Another alternative; export as CSV, write a quick program in your language of choice to do the dirty work, open up again in excel.

Enigmabomb · 07-26-2008, 06:46 AM

I second the CSV. One line of PHP along the lines of:

Your CSV:

Column1,Column2,Column3

PHP:
preg_replace('/\b,\b,YourValue.*?$/',"",file_get_contents($file));

In english: Search for a word, a comma, YourValue optionally followed by other things, as many of them as you'd like, until the end of the line.

This is untested, but should be close.

Enigmabomb · 07-26-2008, 07:02 AM

Im feeling generous.

Here is your code, tested.

<?php

$g = "23,245,34\n35,23,12\n45,45,45";
$g = preg_replace("/\d*,\d*,45$/","",$g);

$g = "Joe,Jim,Rob\nJosh,Jon,Tom\n";
$g = preg_replace("/\w.*,\w.*,Rob/","",$g);

echo $g;

?>

The one caveat is it leaves blank lines where the old ones were, but that wont show up when you re-import to excel.

Enigmabomb · 07-26-2008, 07:05 AM

Wait. Anywhere in the row?! Stand by...

Enigmabomb · 07-26-2008, 07:16 AM

This is dirty, but it works.

$g = preg_replace("/(.*?)Rob(.*?)?/","",$g);

Enigmabomb · 07-26-2008, 07:29 AM

Ok, that actually breaks if Rob starts the line. This works, but again, dirty.

$g = preg_replace("/^(.*?),?Rob,?(.*?)\n/","",$g);

Which in english reads:

If you have a start of the line, Followed by anything, then an optional comma (So in case we're first column) Then our search term Rob Followed by an optional comma (In case were last row) followed by anything until the line break, replace it with nothing.

Sorry about all the posts, but I cant edit after 10 mins. Please let me know how this works for you, I've spent some time on it, and the payoff will be hearing your results.

abhorrent · 07-26-2008, 08:53 AM

This works.

Code:

Sub Find_YOUKEYWORD()
    Dim rng As Range
    Dim what As String
    what = "YOUKEYWORD"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
        End If
    Loop
End Sub

ImagesAndWords · 07-26-2008, 06:50 PM

I've actually looked for something like this in Excel too. Sometimes filtering or removing certain rows in Excel can be a biotch.

Thanks!

rgordon83 · 07-27-2008, 02:30 AM

thanks Enigma and Abhorent +reps

LotsOfZeros · 07-27-2008, 02:32 AM

Quote:

Originally Posted by abhorrent

This works.

Code:

Sub Find_YOUKEYWORD()
    Dim rng As Range
    Dim what As String
    what = "YOUKEYWORD"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
        End If
    Loop
End Sub

nice move

07-25-2008, 01:17 AM	#1 (permalink)
rgordon83 it's a wig Join Date: Dec 2007 Posts: 1,313 iTrader: 5 / 100%	looking for Excel function that... deletes entire ROWS that contain a certain keyword anywhere in the row(not just cell contents) anyone know if this function exists and what it's called? Share Share this post on Digg Del.icio.us Technorati Twitter __________________ Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nam cursus. Morbi ut mi. Nullam enim leo, egestas id, condimentum at, laoreet mattis, massa.

07-26-2008, 06:12 AM	#2 (permalink)
Skittled Member Join Date: Oct 2007 Posts: 86 iTrader: 0 / 0%	Sounds more database-y than excel-esque - could you import it into access/other database? Long story short, not many functions can edit cells other than themselves, so deleting a row doesn't sound like a function thing. I know nothing about macros, but you/someone might, and I assume they could do the job. Another alternative; export as CSV, write a quick program in your language of choice to do the dirty work, open up again in excel. Share Share this post on Digg Del.icio.us Technorati Twitter

07-26-2008, 06:46 AM	#3 (permalink)
Enigmabomb Banned Join Date: Feb 2007 Posts: 1,916 iTrader: 11 / 100%	I second the CSV. One line of PHP along the lines of: Your CSV: Column1,Column2,Column3 PHP: preg_replace('/\b,\b,YourValue.*?$/',"",file_get_contents($file)); In english: Search for a word, a comma, YourValue optionally followed by other things, as many of them as you'd like, until the end of the line. This is untested, but should be close. Share Share this post on Digg Del.icio.us Technorati Twitter __________________ SEO Keyword Tracking For Professionals (Or just a slob with clients) How To Write Link Bait Stop moving in slow motion:Full Speed SEO

07-26-2008, 07:02 AM	#4 (permalink)
Enigmabomb Banned Join Date: Feb 2007 Posts: 1,916 iTrader: 11 / 100%	Im feeling generous. Here is your code, tested. <?php $g = "23,245,34\n35,23,12\n45,45,45"; $g = preg_replace("/\d,\d,45$/","",$g); $g = "Joe,Jim,Rob\nJosh,Jon,Tom\n"; $g = preg_replace("/\w.,\w.,Rob/","",$g); echo $g; ?> The one caveat is it leaves blank lines where the old ones were, but that wont show up when you re-import to excel. Share Share this post on Digg Del.icio.us Technorati Twitter __________________ SEO Keyword Tracking For Professionals (Or just a slob with clients) How To Write Link Bait Stop moving in slow motion:Full Speed SEO

07-26-2008, 07:05 AM	#5 (permalink)
Enigmabomb Banned Join Date: Feb 2007 Posts: 1,916 iTrader: 11 / 100%	Wait. Anywhere in the row?! Stand by... Share Share this post on Digg Del.icio.us Technorati Twitter __________________ SEO Keyword Tracking For Professionals (Or just a slob with clients) How To Write Link Bait Stop moving in slow motion:Full Speed SEO

07-26-2008, 07:16 AM	#6 (permalink)
Enigmabomb Banned Join Date: Feb 2007 Posts: 1,916 iTrader: 11 / 100%	This is dirty, but it works. $g = preg_replace("/(.?)Rob(.?)?/","",$g); Share Share this post on Digg Del.icio.us Technorati Twitter __________________ SEO Keyword Tracking For Professionals (Or just a slob with clients) How To Write Link Bait Stop moving in slow motion:Full Speed SEO

07-26-2008, 07:29 AM	#7 (permalink)
Enigmabomb Banned Join Date: Feb 2007 Posts: 1,916 iTrader: 11 / 100%	Ok, that actually breaks if Rob starts the line. This works, but again, dirty. $g = preg_replace("/^(.?),?Rob,?(.?)\n/","",$g); Which in english reads: If you have a start of the line, Followed by anything, then an optional comma (So in case we're first column) Then our search term Rob Followed by an optional comma (In case were last row) followed by anything until the line break, replace it with nothing. Sorry about all the posts, but I cant edit after 10 mins. Please let me know how this works for you, I've spent some time on it, and the payoff will be hearing your results. Share Share this post on Digg Del.icio.us Technorati Twitter __________________ SEO Keyword Tracking For Professionals (Or just a slob with clients) How To Write Link Bait Stop moving in slow motion:Full Speed SEO

07-26-2008, 08:53 AM	#8 (permalink)
abhorrent Senior Member Join Date: Feb 2008 Location: Illinois Posts: 789 iTrader: 4 / 100%	This works. Code: Sub Find_YOUKEYWORD() Dim rng As Range Dim what As String what = "YOUKEYWORD" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End Sub Share Share this post on Digg Del.icio.us Technorati Twitter

07-26-2008, 06:50 PM	#9 (permalink)
ImagesAndWords Gonna be riotch, biotch.. Join Date: Nov 2006 Location: Wåshingtøn Ståte Posts: 2,183 iTrader: 1 / 100%	I've actually looked for something like this in Excel too. Sometimes filtering or removing certain rows in Excel can be a biotch. Thanks! Share Share this post on Digg Del.icio.us Technorati Twitter __________________

07-27-2008, 02:30 AM	#10 (permalink)
rgordon83 it's a wig Join Date: Dec 2007 Posts: 1,313 iTrader: 5 / 100%	thanks Enigma and Abhorent +reps Share Share this post on Digg Del.icio.us Technorati Twitter __________________ Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nam cursus. Morbi ut mi. Nullam enim leo, egestas id, condimentum at, laoreet mattis, massa.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IP Geo Targeting Function	Brad101	Design, Development & Programming	12	03-25-2008 12:45 AM
Significance of backlinks with "link:" function on Google?	Aveligand	Newbie Questions	1	02-13-2008 08:05 AM
php function for splitting text?	Stanley	Design, Development & Programming	2	11-16-2007 05:49 PM
Excellent PHP Tips/Tricks/Techniques To Follow	Aequitas	Design, Development & Programming	47	10-16-2007 07:46 PM
Coding needed - on page search function	nachoninja	Design, Development & Programming	0	08-04-2007 09:20 PM