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

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.