If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
how can I sort all cells in a sheet by a value in that cell?
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? |
#22
|
|||
|
|||
how can I sort all cells in a sheet by a value in that cell?
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 --- |
#23
|
|||
|
|||
how can I sort all cells in a sheet by a value in that cell?
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 --- |
#24
|
|||
|
|||
how can I sort all cells in a sheet by a value in that cell?
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 --- |
#25
|
|||
|
|||
how can I sort all cells in a sheet by a value in that cell?
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 |
#26
|
|||
|
|||
how can I sort all cells in a sheet by a value in that cell?
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Daily Totals on a summary sheet | Allewyn | Worksheet Functions | 10 | June 27th, 2006 04:47 PM |
Combining data from cells from several excel sheets to a new sheet | Rik | General Discussion | 4 | February 22nd, 2006 09:16 AM |
Possible Lookup Table | Karen | Worksheet Functions | 5 | June 8th, 2005 09:43 PM |
Sort by name - populate cell on diff sheet with that name | mrsimps | Worksheet Functions | 1 | December 20th, 2003 01:29 AM |
Finding cell references | Gary Thomson | Worksheet Functions | 0 | December 4th, 2003 12:08 PM |