Delete - shift left - all cells meeting a specific criteria in a large spreadsheet

[JasonMerritt]

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.

 

[Andrew Poulsom]

Welcome to MrExcel.

Can you post some sample data and the expected results please?

 

[JasonMerritt]

Hi Andrew

Sample below. Apologies I can't work out how to any attachment on here. The HTML maker download doesn't exist anymore. I would expect the results to leave only data in cells where the initial 2/3 characters equal 56= .... , 34 = ..... , 43 = ...... . The data structure varies over the whole spreadsheet but this gives a basic example. Thanks Jason

8=FIX.4.2

9=425

35=8

49=TESTX

56=TESTA

142=DMAEU

34=582

43=N

97=N

52=20130408-06:59:59

369=249

5018=DMA

1011=XLIS

1007=TESTCo

1002=OMS42Sell

8=FIX.4.2

9=426

35=8

49=TESTX

56=TESTA

142=DMAEU

34=583

43=N

97=N

52=20130408-06:59:59

369=249

5018=DMA

1011=XLIS

1007=TESTCo

1002=OMS42Sell

8=FIX.4.2

9=436

35=8

49=TESTX

50=IEQ

56=TESTA

116=Test.Client

34=586

43=N

97=N

52=20130408-06:59:59

369=249

5018=Z20832

1011=XAMS

1002=OMSFilter

8=FIX.4.2

9=434

35=8

49=TESTX

50=IEQ

56=TESTA

116=Test.Client

34=587

43=N

97=N

52=20130408-06:59:59

369=249

5018=Z20834

1011=XPAR

1002=OMSFilter

8=FIX.4.2

9=374

35=8

49=TESTX

56=TESTA

128=TESTC

34=691

43=N

97=N

52=20130408-07:00:04

369=249

9740=TESTC2SMG0000010001

5018=30890

1002=OMSSell

1001=OMSBuy

8=FIX.4.2

9=488

35=8

49=TESTX

56=TESTA

142=DMAEU

34=799

43=N

97=N

52=20130408-07:00:06

369=249

9740=TESTCLIENT-U_00007497533001

5018=DMA

1011=XLON

1010=TEST001C

8=FIX.4.2

9=426

35=8

49=TESTX

56=TESTA

142=DMAEU

34=803

43=N

97=N

52=20130408-07:00:06

369=249

9740=TESTC2SMG00000B0001

9580= XXXX0000123

1007=TESTCo

1002=OMSTESTCSell

8=FIX.4.2

9=465

35=8

49=TESTX

56=TESTA

142=DMAEU

34=1034

43=N

97=N

52=20130408-07:00:11

369=249

9740=TESTCLIENT-U_00007497530001

5018=DMA

1011=XLON

1010=TEST001C

8=FIX.4.2

9=379

35=8

49=TESTX

56=TESTA

128=TESTC

34=1906

43=N

97=N

52=20130408-07:01:03

369=251

9740=00027515957GOLO0

5018=20815

1002=OMSSell

1001=OMSBuy

<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

What's the logic that determines the entries you want to keep?

The HTMLMaker is available here:

https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

 

[JasonMerritt]

Thanks for that. Each cell will always start with a number followed by a = i.e 56=. In total there about 100 iterations. I will be looking to keep around 30 of them, including 32=, 207=, 15=, 50. Hope that makes it a bit clearer.

 

[Andrew Poulsom]

Sorry, that doesn't make it any clearer. About, around and including aren't words that VBA understands.

 

[Peter_SSs]

1. Are there headings in row 1?

2. Are there any blank cells currently among the data? If so, what should happen, if anything, to those blank cells?

3. What is the name of the main data sheet?

4. Does the list of prefixes to keep, or the list of prefixes to delete, exist in the workbook somewhere? If so, where?

 

[JasonMerritt]

Hi Andrew, not sure whether you would prefer this in MrExcelHtml. If so please let me know. All the cells will be begin with one of the below tags. Those in red are on the ones I want to keep whilst deleting all the others. Each row will always have at least some of those in red and none of the cells will be blank

1=

8=

9=

11=

15=

17=

18=

19=

20=

22=

31=

32=

34=

35=

37=

38=

39=

40=

43=

44=

47=

48=

49=

50=

52=

54=

55=

56=

57=

58=

59=

60=

64=

65=

66=

76=

97=

109=

115=

116=

126=

128=

129=

142=

150=

151=

167=

198=

207=

369=

1001=

1002=

1007=

1010=

1011=

5018=

9580=

9740=

9752=

9753=

<tbody>
</tbody><colgroup><col span="3"><col span="3"><col span="3"><col span="3"><col><col><col span="6"><col span="2"><col><col><col><col span="2"><col span="4"><col><col span="5"><col><col span="5"><col span="2"><col><col><col><col><col span="11"></colgroup>

 

[JasonMerritt]

Hi Peter,
1. No headings
2. No blank cells.
3. The main data sheet is rather originally called "data"
4. The list is above in my previous post, its not shown anywhere but could easily be incorporated into a seperate sheet.
Regards
Jason

 

[Peter_SSs]

Try this in a copy of your workbook.
If you are using Excel 2007, a tweak might be needed to cover this problem.

Sub DeleteUnwanted()
  Dim aUnwanted
  Dim i As Long
  
  Const sUnwanted As String = "1 8 9 18 19 20 34 37 38 39 40 43 44 49 52 56 57 58 59 64 65 66 " & _
      "76 97 115 116 126 128 129 151 198 369 1001 1002 1007 1010 1011 5018 9580 9740 9752 9753"
  
  Application.ScreenUpdating = False
  aUnwanted = Split(sUnwanted)
  With Sheets("data").Range("A1").CurrentRegion
    For i = 0 To UBound(aUnwanted)
      .Replace What:=aUnwanted(i) & "=*", Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False
    Next i
    .Resize(.Rows.Count + 1).SpecialCells(xlBlanks).Delete Shift:=xlToLeft
    .EntireColumn.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub