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

[joe54345]

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?

 

[Max]

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]

From your latest response to Dave in the other branch,

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]

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

---

 

[Dave Peterson]

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.

 

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