Deleting Specific Data

**fitkhan** · 04-22-2012, 05:50 PM

Hi,
I have a formula for Column G that Matches column G with Columns H to X and highlights those cells that have same value or part of it is same like Column G.. But Rows wise....

Formula is

=SUM(COUNTIF(G1,"*"&TRIM(MID(SUBSTITUTE(TRIM($G1)," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(TRIM($G1))-LEN(SUBSTITUTE($G1," ",""))+1))*99-98,99))&"*"))

See the results in the screen shot when i applies it

http://dl.dropbox.com/u/12584890/New%20folder/m1.jpg

Now I want to delete all the irrelevant data that doesn't match with column G.. (That is in normal font and color)
I need the same results as shown in the following screen shot...

http://dl.dropbox.com/u/12584890/New%20folder/m2.jpg

Let me know Should I delete them manually or a formula can solve my problem???
Thanks a lot for your time
Cheers

**FDibbins** · 04-22-2012, 05:58 PM

there is no formula that will delete data, at best it will show "nothing", but the formula will still be there.

having said that, you could add a helper column, that uses a formula to select those rows that you want to keep. you then convert the answers to values, sort everything based on those values, and then delete (manually) all those rows you dont want.

however, without an actual workbook to look at, instead of just a picture, it would be harder to offer suggestions on what that formula should be...so maybe upload the workbook instead of a picture?

**Pete_UK** · 04-22-2012, 05:58 PM

A formula can't delete any data - it can just hide it or mark it for you to delete it manually.

Pete

**fitkhan** · 04-22-2012, 06:16 PM

Thanks FDibbins and Pete for your replies and suggestions..
FDibbins that will be great. Actually it is very difficult to delete them one by one.. So if i can get the required data in 1 column. or select them and copy them to some other place.. or whatever which can save my time.. That will be great.. because the files are really big ones...
Attach is the sheet and the above formula is applied to it..

Thanks

**FDibbins** · 04-22-2012, 07:29 PM

after seeing your file, what i suggested probably wont work. what are you basing your "coloring" on?

my suggestion will work on a column-by-column basis, but it removes the duplicates and then moves everything below up to fill in the gaps

In the attached, i added a helper column at A, and added =IF(ISTEXT(VLOOKUP(B2,B3:$B$50,1,FALSE)),1,0)

SAVE YOUR FILE beforee to do the next steps, in case you make a mistake, or it doesnt do what you need!!!!!!!

the next step is to value those answers in column A, and then sort your entire table on A. delete all the rows with 1 in column A. you can then delete column A if you want

let me know if this gives you what you need?

edit: file attached