Excel help - Delete list of values in sheet

GaryIRL

Hi all, I really need some help with this.

I have a list of about 500 values (numbers/ID). I need these values to be removed from a column on the same worksheet. So basically a large multiple find and delete option.
However, it's a bit awkward in that the cells I'm deleting them from contain other numbers.

So I have the values 1,2,3,4,5 in one cell and 3,4,5,6,7,8,9 in another cell, and I want to delete 3,4,6,7,8 for example. How can I do this on a large scale?

I can use VBA modules although I'm not able to write them yet.
Really appreciate any help!

The Corinthian

Save the values as a CSV. Open this as a text file in a text editor. Then do a text find and replace to delete the values (e.g. "3," to ""). Reopen the CSV in Excel as save as an Excel document or copy the amended cells and paste them into the original Excel document.

GaryIRL

Thanks "The Corinthian" thats a good idea, although the only problem is that I can't do a multiple, automatic find & replace, I still have to go through and find each number and delete it. Do you know how to find >100 numbers and delete or replace with " " them?

Gordon

Not sure exactly what you want to do but sounds like TC's solution with a text editor that can apply regex would be the way to go. But it really depends on what exactly the query is.

GaryIRL

Gordon wrote: »

Not sure exactly what you want to do but sounds like TC's solution with a text editor that can apply regex would be the way to go. But it really depends on what exactly the query is.

Thanks Gordon,
I cant wrap my head around this:

My data looks like this:

Date Behaviour I.D.s To Delete
22/03/2013 T 358 429 430 1
01/05/2013 T 99 439 448 487 488 2
01/05/2013 T 99 487 488 3
14/05/2013 T 145 557 4
14/05/2013 T 145 146 557 5
14/05/2013 T 162 269 283 340 360 505 6
14/05/2013 T 145 146 162 269 283 340 360 505 7
11/06/2013 T 122 146 692 14 31 13 8
11/06/2013 T 122 259 498 590 11
11/06/2013 T 122 145 146 456 590 12
11/06/2013 T 125 628 435 13
10/07/2013 T 621 99 126 336 336 342 343 448 14
10/07/2013 T 621 99 126 162 336 336 342 343 15
11/07/2013 T 469 631 434 435 16




I want to delete the list of values on the right from column (IDs) on the left. There is about 470 values so I hope not to have to delete individually.

Any idea?

Gordon

I'm more confused!

Are you showing us rows 1 to 14 here? And for example the first row states that you need to delete rows number 358, 429, 430 and 1?

Or is there a separate id column?

GaryIRL

Sorry I don't know why I'm unable to explain this, it's really quite simple.

Yes that is rows 1-14 out of 309. The formatting didn't work correctly. What you see as date and behaviour are columns A & B, colum C is a list of IDs with multiple IDs in each cell, in the first crow; 358, 429,430 are the IDs to three individuals. The 1 at the end of the first row is the number to be deleted from the lefthand column irrespective of which row it is on. so the last numbers at each row (1,2,3,4,5,6,7,8,11,12,14,15,16....and on to 470) need to be deleted from the column with the multiple values.

Therefore, out of the first row of IDs (358, 429,430), 429 and 430 are on the "to delete" list but 358 isn't. So I want that cell to change to 358 only. Obviously you can't see them on the first 14 samples but didnt want to post it all for cluttering.

Similarly, for the second line, only 99 is in the to delete list so I want that to be deleted and the other numbers (439 448 487 488) to remain. 2 has nothing to do with the rest of that row (the delete list is irrespective of that rows ID cell. Two separate lists and I want the numbers that are in the Righthand list to be deleted from the ID list.

Gordon

Ok so to take just the example text, only the number 14 should be removed from the 8th row?

GaryIRL

From what you can see yes, only the number 14. However the "to delete" list goes on down to ~470 (not consecutively). So somewhere on the delete list are 429 and 430, so I would want them gone from the first line also.

Do you understand?

Gordon

Hmm, yeah, makes sense now. I can't seem to do this in Excel as I can't figure out how to search a range of cells. I've been doing it by splitting everything up with delimiters eg two columns:
ID --- ToDelete
,1,---,,2,,3,,4,,5,,
,2,---,,22,,3,,44,,22,,
,3,---,,22,,3,,44,,1,,
,33,---,,22,,33,,44,,

and then an embedded formula like =IF(FIND(A:A,B1),"remove",B1) - where 'remove' would be the replace, but it only looks at the cells on the same row.

My regex isn't up to scratch for this kind of text job in a text editor. If I was doing this I'd import into SQL and do a cursor on each row, while splitting the ID into a separate column and the text to be replaced in another column.

Sorry, good luck!

The Corinthian

GaryIRL wrote: »

Thanks "The Corinthian" thats a good idea, although the only problem is that I can't do a multiple, automatic find & replace, I still have to go through and find each number and delete it. Do you know how to find >100 numbers and delete or replace with " " them?

Firstly, don't PM someone the exact same thing you've posted in thread in the hope that you'll get a faster response. You won't and you'll more likely get ignored for being pushy.

I've not completely understood your problem, it may be easier to take those values from the ID's column and paste them into a text editor. Then do a find/replace on the deliminator (which appears to be a space) to change it to a tab character. Then paste them into another sheet and each value should fall into an individual cell.

In that format it may prove easier to work with the data you want to remove.

prettygurrly

If you save as a CSV as suggested and then reinsert the data to excel, when you're inserting you may able to place the column separator in the right place to put the values you want to remove in the same column and then delete the column.

It might take a couple of tries...

The other option is using R - R basically can do anything but it's a steep learning curve and I don't know off the top of my head how to do it.

Gordon

OK so if you only have around 500 rows, here's something that'll take you less than half an hour....

Download Sublime Text: http://www.sublimetext.com/ (notepad++ will probably work also, but I don't have that to hand)

Copy and paste all your data into sublime text. I'm presuming that you have three columns and the third column has the numbers separated by spaces.

Pasted text will have the columns separated by a tab.

Do a multiple find and replace and check the .* box for 'regular expression'.

Find: T\t
Replace: T\t,

This finds the start of the number column and adds ,, to start. (presuming that T column only has T in it!) Then..

Find: (.*) ([0-9]*$)
Replace: ,\2,\t\1

This is finding a group of 'everything' followed by a group of a number, preceded by a space, at the end of the line.

It then replaces each line with ,LastNumber,tabEverythingelse

You will have something like this now:
,1, 22/3/13 T ,358 429 430
,2, 1/5/13 T ,99 439 448 487 488
,3, 1/5/13 T ,99 487 488
,4, 14/5/13 T ,145 557

Select all text, ctrl-shift L, or apple-shift L if mac to get multi-line edit. Then hit right arrow and type a comma,

Now find all spaces and replace with two commas. You should have something like this:

,1, 22/3/13 T ,358,,429,,430,
,2, 1/5/13 T ,99,,439,,448,,487,,488,
,3, 1/5/13 T ,99,,487,,488,
,4, 14/5/13 T ,145,,557,
,5, 14/5/13 T ,145,,146,,557,
,6, 14/5/13 T ,162,,269,,283,,340,,360,,505,
,7, 14/5/13 T ,145,,146,,162,,269,,283,,340,,360,,505,
,8, 11/6/13 T ,122,,146,,692,,14,,31,,13,
,11, 11/6/13 T ,122,,259,,498,,590,
,12, 11/6/13 T ,122,,145,,146,,456,,590,
,13, 11/6/13 T ,125,,628,,435,
,14, 10/7/13 T ,621,,99,,126,,336,,336,,342,,343,,448,
,15, 10/7/13 T ,621,,99,,126,,162,,336,,336,,342,,343,
,16, 11/7/13 T ,469,,631,,434,,435,


Now for each line (!) you have to select the first columns entry - ie ,1, and then ,2, and then ,3, - and with regex search:
Find: (\t.*),1,
Replace: \1

then next row
Find: (\t.*),2,
Replace: \1

Find: (\t.*),3,
Replace: \1

Find: (\t.*),4,
Replace: \1


When done with all 500 rows, just find all double commas and replace with blank, then remove all commas and copy and paste this into a new excel sheet

Extremely long winded, but as it's only 500 rows it won't take you that long.