- Apr 14, 2013
I have a large spreadsheet (70k rows and 61 Columns). In any of the columns can appear values I want to delete and shift left, for instance 6= xxxx, 15 = xxxxx, 21 = xxxx etc. I want to be able to delete every occurrence in a cell where these values exist and shift left. The values I want to delete will always begin with a certain criteria i.e 15=, while those values I want to keep will begin with 9=, 11=. I have tried a loop but this takes too long and bombs out after a while. I tried using filters but these contain the full data i.e 11 = xxxxxxxx, 11=xxyyynnn, 11=ffffffff etc. In a nutshell I want to standardise the data in each row. At the moment 1 row could contain - 1=xxxx, 2= yyyyy, 3 = aaaaa, 4 = bbbbb in 4 cells. Row 2 could contain 2 = xxxxx, 3 = bbbbbm 4 = ggggg, 5 = hhhhh. I would want to delete 1 = in row 1 and 1= & 3 = in Row 2and shift left as it doesn't my criteria. Any help would much appreciated. I thought of using a wild card in filters or arrays but not too sure how to put it together but there is probably a lot better way.