I been searching for but can't find the answer to this question.

I have a list of names in column A:

Jim Jones
Jane Smith Barney
Todd Davis
Pete Smith
Ron Jones
etc.

Is there a function that I can use that would search for "Smith" names and
move them to Column B then perhaps using a macro repeat and have all
"Jones" names moved to Column C so that eventually I would end up with -

Col A Col B Col C
Todd Davis Jane Smith Barney Jim Jones
Pete Smith Ron Jones


I would greatly appreciate any help with this!

You could try this:

With the list of names in Column A, starting in A2,
Enter the sir names in Row1 of each column, i.e.:
B1 - Smith
C1 - Jones
D1 - Davis
etc.,

Then, enter this formula in B2:

=IF(ISNUMBER(SEARCH(B$1,$A2)),$A2,"")

And copy across as many columns as needed,
Then, select the formula cells in Row 2, and copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"J.J." wrote in message
et...
I been searching for but can't find the answer to this question.

I have a list of names in column A:

Jim Jones
Jane Smith Barney
Todd Davis
Pete Smith
Ron Jones
etc.

Is there a function that I can use that would search for "Smith" names and
move them to Column B then perhaps using a macro repeat and have all
"Jones" names moved to Column C so that eventually I would end up with -

Col A Col B Col C
Todd Davis Jane Smith Barney Jim Jones
Pete Smith Ron Jones


I would greatly appreciate any help with this!

This seemed to work ok for me:

I used 3 columns though (and deleted column A later).

In B1, I put this:
=IF(COUNTIF(C1:D1,"#n/a")=2,A1,NA())

In c1, I put this:
=IF(ISNUMBER(SEARCH("smith",A1)),A1,NA())

In D1, I put this:
=IF(ISNUMBER(SEARCH("Jones",A1)),A1,NA())

And I dragged down the range. I ended up with something that looked like:

Jim Jones #N/A #N/A Jim Jones
Jane Smith Barney #N/A Jane Smith Barney #N/A
Todd Davis Todd Davis #N/A #N/A
Pete Smith #N/A Pete Smith #N/A
Ron Jones #N/A #N/A Ron Jones

Then I selected columns C:E
edit|copy
Edit|paste special|Values

And with C:E still selected
Edit|goto|special
Constants (but only leave Errors checked)
Then ok out of that dialog

Edit|delete|shift cells up

And I deleted column A and I ended up with:

Todd Davis Jane Smith Barney Jim Jones
Pete Smith Ron Jones




"J.J." wrote:

I been searching for but can't find the answer to this question.

I have a list of names in column A:

Jim Jones
Jane Smith Barney
Todd Davis
Pete Smith
Ron Jones
etc.

Is there a function that I can use that would search for "Smith" names and
move them to Column B then perhaps using a macro repeat and have all
"Jones" names moved to Column C so that eventually I would end up with -

Col A Col B Col C
Todd Davis Jane Smith Barney Jim Jones
Pete Smith Ron Jones

I would greatly appreciate any help with this!

--

Dave Peterson

Both ideas work - Thanks for the help!

JJ