The sheet is all words I want to sort all the cells by a name like
"Bob" but bob is not always the first word in the cell. What I really
need to do is list all the cells with the name bob in a column and be
able to cut and paste them into another spreadsheet column. Thanks

When you say 'a name like Bob' did you mean a palindrome, a 3 letter
word, a word starting with B, the middle word in the cell or some other
definition.

Can you use Text-to-columns to extract your sort-word?

Any further clues?

--

Wrote:
The sheet is all words I want to sort all the cells by a name like
"Bob" but bob is not always the first word in the cell. What I really
need to do is list all the cells with the name bob in a column and be
able to cut and paste them into another spreadsheet column. Thanks


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=532139

you could use a helper column. say your data begins in cell B1, enter in A1

=ISERROR(SEARCH("bob", B1,1))

and copy down as far as necessary. this will return TRUE if bob is not
found and FALSE if it is found (counterintuitive, I know)

sort your data using Column A or use Autofilter to group the TRUE/FALSE
values and copy to another worksheet.



" wrote:

The sheet is all words I want to sort all the cells by a name like
"Bob" but bob is not always the first word in the cell. What I really
need to do is list all the cells with the name bob in a column and be
able to cut and paste them into another spreadsheet column. Thanks

Forgot one thing - this would also return a hit for "Bobby", "bobcat", etc


"JMB" wrote:

you could use a helper column. say your data begins in cell B1, enter in A1

=ISERROR(SEARCH("bob", B1,1))

and copy down as far as necessary. this will return TRUE if bob is not
found and FALSE if it is found (counterintuitive, I know)

sort your data using Column A or use Autofilter to group the TRUE/FALSE
values and copy to another worksheet.



" wrote:

The sheet is all words I want to sort all the cells by a name like
"Bob" but bob is not always the first word in the cell. What I really
need to do is list all the cells with the name bob in a column and be
able to cut and paste them into another spreadsheet column. Thanks

This might work, only problem is that I need to find this one word
throughout the spreadsheet. I was hoping I could put an equation in a
column that can pull each individual cell into one column. Your way
might work if there is a way other than manually to drag all the cells
into one column.

Joe,

How many columns might contain the word you are looking for?

--

Wrote:
This might work, only problem is that I need to find this one word
throughout the spreadsheet. I was hoping I could put an equation in a
column that can pull each individual cell into one column. Your way
might work if there is a way other than manually to drag all the cells
into one column.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=532139

wrote
This might work, only problem is that I need to find this one word
throughout the spreadsheet. I was hoping I could put an equation in a
column that can pull each individual cell into one column. Your way
might work if there is a way other than manually to drag all the cells
into one column.

One interp & play to tinker with ..

Assume the source data/words are within A1 to D50

Put in F1:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("bob",A1:D1))))0,ROW(),"")

Put in G1:
=IF(ISERROR(SMALL($F:$F,ROW(A1))),"",
IF(INDEX(A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0))= 0,"",
INDEX(A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0))))
Copy G1 across 4 cols to J1

Select F1 to J1, fill down to J50

G1:J50 will auto-return only the lines with "bob" from A1:D50,
with all lines neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I'd apply Data|Filter|Autofilter to that column (or the whole range???).

Then use the dropdown arrow in that column's header
Choose Custom
Contains Bob

Copy and paste to the other sheet.

Then Data|Filter|Show all
to see everything again.

wrote:

The sheet is all words I want to sort all the cells by a name like
"Bob" but bob is not always the first word in the cell. What I really
need to do is list all the cells with the name bob in a column and be
able to cut and paste them into another spreadsheet column. Thanks

--

Dave Peterson

maybe 30. It's a calendar spreadsheet where I'm looking to quantify the
number of times a task is mentioned. I've been manually cutting and
pasting it and figured there's got to be a better way. If I could also
log the date of each occurrence that would be optimal. The date is
listed on the first row of each column.

Thanks for this Dave but if I can I'd like to be able to apply this
filter to the whole sheet. I can only do this per column. Is there a
way to use the advanced filter to apply this to the entire sheet? Seems
like there should be.

Max I tried to do this but I'm not sure I know how to autofill or if I
put in the second formula correctly. All I got when I tried it was a
number 38808. I grabbed the corner of the cell and dragged it the
appropriate number of columns then hilighted the correct number of rows
down and clicked "fill formatting only" but nothing happened. I tried
"fill cells" also.

I didn't see that (I missed your followup).

You could use a helper column and use a formula like:

=countif(a2:ad2,"*bob*")0

Then filter on True/Falses



wrote:

Thanks for this Dave but if I can I'd like to be able to apply this
filter to the whole sheet. I can only do this per column. Is there a
way to use the advanced filter to apply this to the entire sheet? Seems
like there should be.

--

Dave Peterson

Sorry Dave, I'm an Excel novice. I don't understand how to use that
formula or where to put it.

Is there a way I can amend this formula to apply to multiple columns
instead of just one? If not is there a quick way I can turn 30 columns
into one column?

You could modify

=IF(ISERROR(FIND("Bob",A1&B1&C1&D1&E1&F1)),"",FIND
("Bob",A1&B1&C1&D1&E1&F1))

to take 30 columns, then auto-filter and show non-blanks (or show and
delete = blanks)

after &F1 do &G1&H1&i1&J1 etc

This is also case-sensitive on the Bob and won't find bobcat etc.

--

Wrote:
Is there a way I can amend this formula to apply to multiple columns
instead of just one? If not is there a quick way I can turn 30 columns
into one column?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=532139

You said you could have 30 columns that may contain the characters "bob".

I guessed that those 30 columns were columns A:AD.

So you could put this in AE2
=countif(a2:ad2,"*bob*")0

=countif(a2:ad2,"*bob*")
will count the number of cells in A2:AD2 that contain "bob".

=countif(a2:ad2,"*bob*")0
will return true or false depending on if that count is 0 or greater than 0.

Then drag this formula down that column (AE) and filter by that column.

wrote:

Sorry Dave, I'm an Excel novice. I don't understand how to use that
formula or where to put it.

--

Dave Peterson

wrote
maybe 30. It's a calendar spreadsheet where I'm looking to quantify the
number of times a task is mentioned. I've been manually cutting and
pasting it and figured there's got to be a better way. If I could also
log the date of each occurrence that would be optimal. The date is
listed on the first row of each column.

The post deepens .. <g

Here's a set-up which might satisfy ..

A sample construct is available at:
http://www.savefile.com/files/8637617
Count task occurence n List dates of occurence.xls

In sheet: X,

Source table is assumed within B1:AF10 (31 cols),
header dates in B1:AF1 , data in row2 to 10

Using 31 empty cols to the right (AH to BL)

In AH2, copied to BL2, filled down:
=IF(TRIM(Y!$A$1)="","",IF(ISNUMBER(SEARCH(TRIM(Y!$ A$1),B2)),COLUMN(),""))

(AH1:BL1 is left empty)

In sheet: Y,

The item to search will be input in A1

In A2, copied down:
=IF(TRIM(A1)="","",SUMPRODUCT(--(ISNUMBER(SEARCH(TRIM($A$1),X!$B2:$AF2)))))

In B2, copied across to say, K2*, then filled down:
=IF(ISERROR(SMALL(Z!$AH2:$BL2,COLUMN(A1))),"",
INDEX(Z!$B$1:$AF$1,MATCH(SMALL(Z!$AH2:$BL2,COLUMN( A1)),Z!$AH2:$BL2,0)))

*assuming a max of up to 10 dates is expected per item input in A1
(to cover the full show, copy B2 across by 31 cols)

A2:A10 will return the occurences count
of the input item in A1, eg: bob
within rows 2 - 10 in the source table in X.

And the corresponding dates for the occurences
will be listed next to the counts, all dates bunched neatly to the left
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Joe,

I've posted a revised set-up (plus a link to a sample)
in response to your reply to Bryan
where (I thought <g) your set-up/intents were better described:

maybe 30. It's a calendar spreadsheet where I'm looking to quantify the
number of times a task is mentioned. I've been manually cutting and
pasting it and figured there's got to be a better way. If I could also
log the date of each occurrence that would be optimal. The date is
listed on the first row of each column.

Take a look over there ..

--
number 38808

This number is probably a date (1st Apr 2006),
which would appear if we just format the cell as a date
via: Format Cells
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

wrote:
.. I grabbed the corner of the cell and dragged it the
appropriate number of columns then
highlighted the correct number of rows
down and clicked "fill formatting only" but nothing happened.
I tried "fill cells" also.

To copy/fill across/down,
just point n left-click (drag) the bottom right corner*
of the start cell with the formula down or across
*the fill handle

Of course, the above would also copy the cell formats
of the start cell to the destination cells
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max, it's incredibly kind of you to do all this work and put together
the example spreadsheet but I'm embarrassed to say that I cannot figure
it out. Are the two different tabs supposed to work in tandem? How
exactly do I apply these formulas to my spreadsheet. I know I'm a lost
cause but I didn't think this kind of sorting would be so complicated.

Dave I got this to work but the problem with it is that it returns all
the rows that include the keyword but it also includes everything else
in that row, so I still end up searching the spreadsheet for the
keyword with my eyes. Is there anything I can add that will eliminate
the rest of the cells that do not include the keyword?

wrote
Max, it's incredibly kind of you to do all this work and put together
the example spreadsheet but I'm embarrassed to say
that I cannot figure it out.

Are the two different tabs supposed to work in tandem?

Yes, of course.

X is presumed to be where the source table lies,
and Y is another sheet to extract/display the final results neatly

How exactly do I apply these formulas to my spreadsheet.
I know I'm a lost cause
but I didn't think this kind of sorting would be so complicated

Could you upload a small sample of your actual** file
and paste the *link* to it in your reply here ?
**sanitized if necessary

I'll take a look at your sample, apply whatever's possible <g
and then post a reply/link to it back here.

Use either of these 2 free n easy-to-use filehosts
to upload your sample:

http://www.flypicture.com/
http://cjoint.com/index.php

For cjoint.com (it's in French), just click "Browse" button,
navigate to your folder select the sample file Open,
then click the button centred in the page below
(labelled "Creer le lien Cjoint") and it'll generate the
link. Then just copy & paste the generated link as part
and parcel of your response here.

But kindly note that no attachments
should be posted *directly* to the newsgroup
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

From your latest response to Dave in the other branch,

wrote
Dave I got this to work but the problem with it is that it returns all
the rows that include the keyword but it also includes everything else
in that row, so I still end up searching the spreadsheet for the
keyword with my eyes. Is there anything I can add that will eliminate
the rest of the cells that do not include the keyword?

it seems like you're after a sort-of "columnwise" filter

Here's a slightly revised version to the earlier construct posted
which could achieve your intents ..

http://www.savefile.com/files/4205600
Count task occurence n List dates_tasks.xls

Revision made:

In sheet: Y,

In B2, copied across to say, K2*, then filled down:
=IF(ISERROR(SMALL(X!$AH2:$BL2,COLUMN(A1))),"",TEXT (INDEX(X!$B$1:$AF$1,MATCH(
SMALL(X!$AH2:$BL2,COLUMN(A1)),X!$AH2:$BL2,0)),"dd-mmm-yyyy")&CHAR(10)&INDEX(
X!$B2:$AF2,MATCH(SMALL(X!$AH2:$BL2,COLUMN(A1)),X!$ AH2:$BL2,0)))

Format B2 to wrap text
(via: Format Cells Alignment tab Check Wrap Text OK)

*assuming a max of 10 dates per item input in A1

Now, the corresponding dates & text for the occurences
will be listed next to the counts in A2:A10
(instead of just the dates)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Format B2 to wrap text
(via: Format Cells Alignment tab Check Wrap Text OK)

Do the formatting in B2 before you copy B2 across/down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I thought you wanted to copy the rows if a cell in that row contained your text.

If you just want to see the word better...

Option Explicit
Option Compare Text
Sub testme()

Application.ScreenUpdating = False

Dim myWords As Variant
Dim myRng As Range
Dim foundCell As Range
Dim iCtr As Long 'word counter
Dim cCtr As Long 'character counter
Dim FirstAddress As String
Dim AllFoundCells As Range
Dim myCell As Range

'add other words here
myWords = Array("widgets", "assemblies", "another", "word", "here")

Set myRng = Selection

On Error Resume Next
Set myRng = Intersect(myRng, _
myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please choose a range that contains text constants!"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
Set foundCell = Nothing
With myRng
Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

If foundCell Is Nothing Then
MsgBox myWords(iCtr) & " wasn't found!"
Else
Set AllFoundCells = foundCell
FirstAddress = foundCell.Address
Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = foundCell
Else
Set AllFoundCells = Union(foundCell, AllFoundCells)
End If
Set foundCell = .FindNext(foundCell)

Loop While Not foundCell Is Nothing _
And foundCell.Address < FirstAddress
End If

End With

If AllFoundCells Is Nothing Then
'do nothing
Else
For Each myCell In AllFoundCells.Cells
For cCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _
= myWords(iCtr) Then
With myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr)))
.Font.ColorIndex = 3
.Font.Bold = True
End With
End If
Next cCtr
Next myCell
End If
Next iCtr
Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This portion:

With myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr)))
.Font.ColorIndex = 3
.Font.Bold = True
End With

Changes the color and the boldness.

You may want to save first--so you can close without saving if you don't want to
keep the formatting changes. Remember this kind of formatting only works on
text cells--not formulas--not numbers.

wrote:

Dave I got this to work but the problem with it is that it returns all
the rows that include the keyword but it also includes everything else
in that row, so I still end up searching the spreadsheet for the
keyword with my eyes. Is there anything I can add that will eliminate
the rest of the cells that do not include the keyword?

--

Dave Peterson

Ps. What does eliminate mean?

You could use a helper worksheet and some formulas.
in A1 of that helper sheet:

=If(countif(sheet1!a1,"*bob*")0,sheet1!a1,"")

Drag down as far as you need and then drag as far right as you need.

wrote:

Dave I got this to work but the problem with it is that it returns all
the rows that include the keyword but it also includes everything else
in that row, so I still end up searching the spreadsheet for the
keyword with my eyes. Is there anything I can add that will eliminate
the rest of the cells that do not include the keyword?

--

Dave Peterson