Using an "If" statement to move data from one column to another

[Jbm]

Hi,
Tried looking this one up in the archives, and while I'm sure it's there I
couldn't find it.
I have data in column B, around 5000 rows. I'm wondering how to move any
cells from column B to column G if and only if they contain certain words.
Additionally, I'd like for the values copied into G to be put in rows 1, 2,
3, 4 etc. instead of the rows they're in now. An example here might help --
I have something like:

Column B
Hardwood Floors
Carpeted Floors
Laminate Floors
Oak Wood Floors
Tile Floors
Beechwood Floors

Supposing I wanted to pull out any cells with the word "wood" in them, I
would want column G to look like this:

Column G
Hardwood Floors
Oak Wood Floors
Beechwood Floors

in consecutive rows starting from the top. Essentially, the more automated
this process is the better, since I have a new sheet with similar data every
week for that week. Any help would be appreciated, thanks.

 

[Jbm]

I realized an oversight in my question (is there no edit function on posts?):
I also have numerical data in Column C that I'd like to move with its
corresponding value in Column B. So with the previous example, if the number
26 occupied cell C4 corresponding to Oak Wood Floors, I'd like cell H2 to
then show 26 when Oak Wood Floors moved. Also I'm running Excel 2007. Sorry
for the length of this question and my forgetfulness, but thanks for any help!

 

[Luke M]

I would suggest using this macro. Press Alt+F11 to open VBE, then Alt+I, M.
(you should now have a blank module. Paste this coding in:

'==========
Sub DataMove()
KeyWord = InputBox("What word do you want to look for?", "Key Word")
If KeyWord = "" Then Exit Sub
RowCount = 1
For Each c In Range("B:B")
If c.Value Like "*" & KeyWord & "*" Then
Cells(RowCount, "G").Value = c.Value
RowCount = RowCount + 1
End If
Next
End Sub
'===========


Back in your workbook, you can play the macro (Alt+F8). You'll be prompted
for the word you want, and it will then move over your data from B to G,
starting in row 1.

 

[Luke M]

Modified coding (and I agree, a edit function would be most helpful!):

'==========
Sub DataMove()
KeyWord = InputBox("What word do you want to look for?", "Key Word")
If KeyWord = "" Then Exit Sub
RowCount = 1
For Each c In Range("B:B")
If c.Value Like "*" & KeyWord & "*" Then
'Copies value with Key Word in it
Cells(RowCount, "G").Value = c.Value
'Copies corresponding value
Cells(RowCount, "H").Value = c.Offset(0,1).Value
RowCount = RowCount + 1
End If
Next
End Sub
'===========

 

[Jbm]

Luke,
Thanks for the quick response and good macro. There are three things
however that I would like to modify if I can. First, is there a way to have
the macro look for the word itself, instead of requiring input from me after
the macro is run? So perhaps the word "wood" would be needed somewhere in
the text of the macro itself? Second, can I do multiple words at once, like
"wood" and "tile"? Finally, and it seems this is entirely my fault since you
responded so quickly you didn't see my "edit," is there a way for the macro
to copy the corresponding data in the column next to B? You've already been
a great help, these changes would just refine the operation to automate this
process as much as possible. Thanks again.

 

[Jbm]

I didn't want you to waste time if you look at this later, so here's an
update. I played around with the code for a while (I'm new at macros and
learning as I go) and came up with almost what I want to do. The only thing
I haven't figured out is how to get the macro to do the operation for several
terms. I have "If c.Value Like "Wood" Then" and I need to do it for Wood and
Tile. Whether you can help me with that or not, I really do appreciate all
the help to this point, without your suggested macro I'd be nowhere on this,
but because of it I've figured out a workable (if messy) macro for my problem.

 

[Luke M]

You can add multiple criteria using Or(s).

If c.value like "*wood*" Or _
c.value like "*tile*" Or _
c.value like "*ceramic*" Then
'rest of coding...


Note the use of asterisks as wildcards to search for your word(s).

 

[Jbm]

Luke,
That is quite simply perfect. For reference, the final macro I used looked
something like this:

Sub DataMove()
RowCount = 2
For Each c In Range("B:B")
If c.Value Like "*wood*" Or _
c.Value Like "*tile*" Or _
c.Value Like "*soft*" Or _
c.Value Like "*hard*" Or _
c.Value Like "*light*" Or _
c.Value Like "*dark*" Or _
c.Value Like "*medium*" Then
Cells(RowCount, "H").Value = c.Value
Cells(RowCount, "I").Value = c.Offset(0, 1).Value
Cells(RowCount, "J").Value = c.Offset(0, 2).Value
Cells(RowCount, "K").Value = c.Offset(0, 3).Value
Cells(RowCount, "G").Value = c.Offset(0, -1).Value
RowCount = RowCount + 1
End If
Next
End Sub

Not only did you answer my question, but I learned a great deal about
writing macros this morning. Really, thank you very much, the help is
endlessly appreciated.

 

[Luke M]

Glad to help, and thanks for the feedback!

 

[Rick Rothstein]

Your "over one row, up one column" statement is a little confusing (usually
you go up rows and over columns), so I'm not totally sure where the data is
to go. I have assumed you meant if the keyword was found in, say, A3, then
you want the it moved to B4. This movement is controlled by the values in
the Offset property call (first argument is row offset, second argument is
column offset, both values can be plus or minus as needed). I also wasn't
sure if you really meant that you wanted to delete the entire column (as
your description seems to say) or just the value in the original cell (as I
think you meant)... I assumed the latter which is what the C.Clear statement
does.

Sub DataMove()
KeyWord = InputBox("What are you looking for?", "Key Word")
If KeyWord = "" Then Exit Sub
RowCount = 1
For Each C In Range("A:A")
If C.Value Like "*" & KeyWord & "*" Then
'Copies value with Key Word in it
C.Offset(1, 1).Value = C.Value
RowCount = RowCount + 1
C.Clear
End If
Next
End Sub

 

[Rick Rothstein]

Thank you for letting us know the issue has been resolved.