excel vba macro for searching/sorting

Custom / Coolermaster 690ii advanc...
June 20, 2013 at 15:14:05
Specs: Windows 7 pro -stacked with GUI/accessibility apps, i5 2500K @ 4.8 GHz / 2x 4GB DDR3-1866 G.Skill
I need some help creating a macro that can accomplish the 2 following:

1) searching a list of multiple string text in column A (or text-to-columns first), returns the matches and t words before and/or after the match, where t is a variable defined elsewhere. t will contain multiple variables, or this will be run for multiple passes.
eg
ADATA Xpg V2 AX3U2600W4G11-DGV 8GB 2X4GB DDR3-2600 CL11-13-13-35 1.65V Gold Memory Kit (AX3U2600W4G11-DGV)

search for "DDR3" with variable t -2 returns DDR3-2600 and 2 positions before it:
8GB 2X4GB DDR3-2600


2) (hopefully easier) to count the total # of individual text strings in a given row, eg separated by spaces. so this can be after text to columns them and count the range, if that is easier, or if there is a way without this step that would be better. then return the whole row parsed like this, #s indicate which "word" position in the target row:

1+2, 3+4, 5+6... 2+3, 4+5, 6+7... 1+2+3, 2+3+4, .... 1234, 2345, 3456...

it needs to count them first so that it wont run on blanks

so the example:

ADATA Xpg V2 AX3U2600W4G11-DGV 8GB 2X4GB DDR3-2600 CL11-13-13-35 1.65V Gold Memory Kit (AX3U2600W4G11-DGV)

so this data I need to make it (double dash indicate separate returns):

ADATA Xpg -- V2 AX3U2600W4G11-DGV -- 8GB 2X4GB -- DDR3-2600 CL11-13-13-35 -- ...
Xpg V2 -- AX3U2600W4G11-DGV 8GB -- 2X4GB DDR3-2600 -- CL11-13-13-35 1.65V Gold --....
ADATA Xpg V2 -- AX3U2600W4G11-DGV 8GB 2X4GB -- DDR3-2600 CL11-13-13-35 1.65V --...

I just need help getting started, I will be able to fill in the correct formulas myself if someone can help provide me some examples that will accomplish what im describing. its going to be a really long list of variables and formulas so I don't want to list it here.

its taking all these returns and output them on a separate sheet. also it is huge data and will take a really long time so the fastest way is the best way.

thanks for any help!

(this is a continuation of work on a previous macro, thread here)


See More: excel vba macro for searching/sorting

Report •


#1
June 20, 2013 at 16:43:46
I believe that this code will accomplish the first part of your task, i.e. return the search string plus t1 strings before it and t2 strings after it.

It's not pretty, but I assume you can modify it to fit your needs. As written, it displays the output in a MsgBox. You can write the output to anywhere you want.

Since you seem to want to keep the original data intact (OrgData), the code copies the data to a new sheet (ParseData), performs a Text-To-Column on the new sheet and does its searches on the parsed data.

The only "error checking" that the code does is to make sure that the value entered for the "before" variable isn't larger than the actual number of columns available before the search string. In other words, if DDR3 is found in Column 7, and the user wants to include 8 strings before it, the code will not allow it since there are only 6 strings available.

As far as your second task, not only don't I have time to work on it right now, I don't really understand what you are trying to do and I don't even have time to try and figure it out. Perhaps you could clarify it and I can work on it but probably not before the weekend.

Anyway, here's the first piece of code for you to try:

Sub BuildStrings()
'Delete ParseData Sheet if it exists
   For sht = 1 To Sheets.Count
     If Sheets(sht).Name = "ParseData" Then
       Application.DisplayAlerts = False
        Sheets("ParseData").Delete
       Application.DisplayAlerts = True
       Exit For
     End If
   Next
'Copy Original Data to new Sheet, perform Text-To-Columns
    Sheets("OrgData").Copy After:=Sheets("OrgData")
        With ActiveSheet
          .Name = "ParseData"
          .Range("A:A").TextToColumns Destination:=Range("A1"), _
             Space:=True
        End With
 'Get Variables for building output string
    myText = Application.InputBox("Enter Search String", Type:=2)
    fore_t = Application.InputBox("Enter t Before String", Type:=1)
    aft_t = Application.InputBox("Enter t After String", Type:=1)
 'Search for Partial String
  With Sheets("ParseData").Cells
   Set Part = .Find(myText, lookat:=xlPart)
'If String is found...
    If Not Part Is Nothing Then
'Make sure fore_t variable is valid, Exit Sub if not
      If Part.Column <= fore_t Then
        MsgBox "There are not enough Columns before " & Part
        Exit Sub
      End If
'Build string from cells before String
      For t1 = fore_t To 1 Step -1
        tmpstring1 = tmpstring1 & Cells(Part.Row, Part.Column - t1) & " "
      Next
'Build String from cells after String
      For t2 = 1 To aft_t
        tmpstring2 = tmpstring2 & Cells(Part.Row, Part.Column + t2) & " "
      Next
    End If
  End With
'Build entire output String and Display
       FullString = tmpstring1 & Part & " " & tmpstring2
       MsgBox FullString
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 20, 2013 at 17:29:51
thanks so much I will check this out asap and get back to you.

the 2nd part is actually easier and I think my explanation wasn't very good. i'll try to give a better example:

I need to chop up the rows into chunks of 2, 3, 4 words, all possible combinations while retaining order. edit* only the words beside eachother

"solid red corner bump protector for display cases"
---1---2-----3------4-------5-------6-----7------8---- <--word #

2 words:
position 1+2, 3+4, 5+6 = solid red, corner bump, protector for
and then
position 2+3, 4+5, 6+7 = red corner, bump protector, for display
so this would be covering all 2 word combos, 12 23 34 45 56 67 78

3 words:
position 123, 234, 345, 456, 567
solid red corner, red corner bump, corner bump protector, etc...


hopefully this explains better my goal. I realise I can put a formula like A+B, B+C, for columns, but I am hoping there is a way to define a range and use a formula to define the grouping parameters instead of a REALLY long formula with all combinations manually entered since this will cause problems with the different row lengths (my data on the rows has varying amounts of strings or "words" in it).


Report •

#3
June 20, 2013 at 20:26:44
EDIT:

I think this gets you what you want, in a MsgBox format. Obviously the strings could be placed in cells, or strung together, etc.

It is based on the single example that you gave, which was placed in Row 1 and parsed via Text-To-Columns.

solid     red     corner     bump     protector     for     display     cases

For multiple rows of strings of various lengths, you would loop through the rows determining the number of columns for each parsed string before grouping them.

For groups of words greater than 4, simply follow the pattern shown for the groups of 2, 3 and 4.

Sub Grouper()
'Determine Number of Columns of Parsed Data
 numCols = Cells(1, Columns.Count).End(xlToLeft).Column
'Group words by 2's
   For grp2 = 1 To numCols - 1
    MsgBox (Cells(1, grp2) & " " & Cells(1, grp2 + 1))
   Next
'Group words by 3's
   For grp3 = 1 To numCols - 2
    MsgBox (Cells(1, grp3) & " " & Cells(1, grp3 + 1) & " " & Cells(1, grp3 + 2))
   Next
'Group words by 4's
   For grp4 = 1 To numCols - 3
    MsgBox (Cells(1, grp4) & " " & Cells(1, grp4 + 1) & " " & Cells(1, grp4 + 2) _
           & " " & Cells(1, grp4 + 3))
   Next
End Sub

Previous answer...

Still missing something.

Your example output is a single line separated by commas:

solid red, corner bump, protector for

Is that what you want the output to look like or do you want:

solid red
corner bump
protector for

Or do you want something even different?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 21, 2013 at 14:52:16
I will work with what you have provided and see what I can get. this is a great start.

I haven't figured out exactly what I want the output to look like, I needed the functions first.

the data is a single column of essentially random, alphanumeric strings of varying length, separated by spaces (or whatever I replace spaces with). between 1 and 20 words per row, say 150 chars max length, average of 4000 rows in a dataset, max maybe 40000. (chances are these functions will run slow on data this big, that's ok)

#1 will be run multiple times from a list of keywords. I think I will need to use an excel sheet with the terms to search in column A, and then the other columns used somehow to define which t parameters for each keyword. its basically an extension of the macro you helped me with a year ago that I linked thread to. instead of returning the whole row, its going to return the t variables, but im not sure yet how to format the output to be usable.

#2 will be run only one time on a given data set, and return an exponentially large output, which I will analyze as a whole. the purpose is to count occurrences of given relationships (combos). I should probably have prefaced everything with this, but I have data analysis software for performing calculations and searches on the resulting datasets we are creating, so that is why the goal is to create large outputs to then analyze, as opposed to just performing analysis in place and returning counts, etc.

it might be best if it was like, an input box with radio buttons to select the options:

eg #1) - will get back to u, I have an idea for this.
eg #2) -2 words, 3 words, 4 words etc and u push the buttons for how many u want.

the only saving grace for me on this is that its for me, so it doesn't need to be terribly user friendly, if I have to edit some sheets manually or bits of code each time I run it, its not a big deal. if I can get it working, I can design a better way to use it later.

once I have had a chance to work with the data a bit I should have a clear concept of how the output should behave and I will post back here with my progress.

thanks so much for your help :)


Report •

#5
June 21, 2013 at 17:24:57
for #2 its working great, but I am trying right now to make it output to a sheet instead of a msgbox.. I know its sad... if u can show me how to output to a sheet I will be in great shape.

Sub Grouper()
'Determine Number of Columns of Parsed Data
 numCols = Cells(1, Columns.Count).End(xlToLeft).Column
'Group words by 2's
   For grp2 = 1 To numCols - 1
    MsgBox (Cells(1, grp2) & " " & Cells(1, grp2 + 1))
   Next
'Group words by 3's
   For grp3 = 1 To numCols - 2
    MsgBox (Cells(1, grp3) & " " & Cells(1, grp3 + 1) & " " & Cells(1, grp3 + 2))
   Next
'Group words by 4's
   For grp4 = 1 To numCols - 3
    MsgBox (Cells(1, grp4) & " " & Cells(1, grp4 + 1) & " " & Cells(1, grp4 + 2) _
           & " " & Cells(1, grp4 + 3))
   Next
End Sub

thanks again


Report •

#6
June 21, 2013 at 18:48:08
I guess I assumed that you could modify the MsgBox code to output the results to a sheet since you do that multiple times in the code you linked to in your first post.

Perhaps you should use some of the techniques found in this tutorial to single step through your code so that you can watch what the code is doing, step by step.

http://www.computing.net/howtos/sho...

That said, before I could offer any modifications, I need some more information.

Output to a sheet in what manner?

All output in Column A?

Groups of 2 in Column A, groups of 3 in Column B, etc.?

I'll point out that mixing and matching 2's and 3’s, etc. is more difficult than the way is works now, with all the same sized groups being produced within their own loop.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question