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.

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!

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
'===========

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jbm" wrote:

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!

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jbm" wrote:

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.

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.

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.

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).
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jbm" wrote:

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,
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.