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

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.

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

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

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.

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

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

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

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.

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.

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