how can I sort all cells in a sheet by a value in that cell?

[joe54345]

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]

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?

--

 

[Guest]

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.

 

[Guest]

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

 

[joe54345]

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]

Joe,

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

--

 

[Max]

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",A11))))>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 A150,
with all lines neatly bunched at the top

---

 

[Dave Peterson]

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.

 

[joe54345]

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.

 

[joe54345]

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.

 

[joe54345]

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.

 

[Dave Peterson]

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

 

[joe54345]

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

 

[joe54345]

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]

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.

--

 

[Dave Peterson]

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.

 

[Max]

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]

Joe,

I've posted a revised set-up (plus a link to a sample)
in response to your reply to Bryan

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]

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

---

 

[joe54345]

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.