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. Both of them. Join Date: Dec 2007 Location: Chi-town Posts: 719	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? __________________ [EXCLUSIVE] Coupon for 20% off phpBay Pro Try LogicalMedia for legit freebie offers. (Not shady scams.)

07-26-2008, 06:46 AM	#3 (permalink)
Enigmabomb Senior Member Join Date: Feb 2007 Posts: 495	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. __________________ Web comedy designed to make you feel more normal.

07-26-2008, 07:02 AM	#4 (permalink)
Enigmabomb Senior Member Join Date: Feb 2007 Posts: 495	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. __________________ Web comedy designed to make you feel more normal.

07-26-2008, 07:05 AM	#5 (permalink)
Enigmabomb Senior Member Join Date: Feb 2007 Posts: 495	Wait. Anywhere in the row?! Stand by... __________________ Web comedy designed to make you feel more normal.

07-26-2008, 07:16 AM	#6 (permalink)
Enigmabomb Senior Member Join Date: Feb 2007 Posts: 495	This is dirty, but it works. $g = preg_replace("/(.?)Rob(.?)?/","",$g); __________________ Web comedy designed to make you feel more normal.

07-26-2008, 06:12 AM	#2 (permalink)
Skittled Member Join Date: Oct 2007 Posts: 69	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.

07-26-2008, 07:29 AM	#7 (permalink)
Enigmabomb Senior Member Join Date: Feb 2007 Posts: 495	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. __________________ Web comedy designed to make you feel more normal.

07-26-2008, 08:53 AM	#8 (permalink)
abhorrent Senior Member Join Date: Feb 2008 Location: Illinois Posts: 591	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

07-26-2008, 06:50 PM	#9 (permalink)
ImagesAndWords Gonna be riotch, biotch.. Join Date: Nov 2006 Location: Nørwåy Posts: 1,260	I've actually looked for something like this in Excel too. Sometimes filtering or removing certain rows in Excel can be a biotch. Thanks! __________________

07-27-2008, 02:30 AM	#10 (permalink)
rgordon83 It's a wig. Both of them. Join Date: Dec 2007 Location: Chi-town Posts: 719	thanks Enigma and Abhorent +reps __________________ [EXCLUSIVE] Coupon for 20% off phpBay Pro Try LogicalMedia for legit freebie offers. (Not shady scams.)

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