CODEWITHSUNDEEP
excelexcel-formula
frazman
frazman

Reputation: 33223

Advanced Excel sorting

I have a quick question.

I have an Excel sheet.. which contains descriptions of certain items:For example:

1) Awesome mirror
2) Another Awesome mirror
3) beautiful table
4) Pretty beautiful lovely sofa
5) one more mirror

and so on...

So lets say, I want to place all the mirrors together, all the tables together... and so on... so basically something which can return me all the instances which contain the word "mirror".

Any ideas on how to solve about this?

Upvotes: 2

Views: 228

Answers (4)

brettdj
brettdj

Reputation: 55672

You could use a formula solution as below:

=SUM(COUNTIF(A1,"*"&{"table","mirror","sofa"}&"*")*{1,100,1000})

will give
table a score of 1
mirror a score of 100
sofa a score of 1000

allowing an easy numerical sort.

If it was possible that a cell could contain both mirror and sofa then it would get a score of 101. In this case you may either:

  • be happy to have a separate list of multi-matches
  • I could further adapt the formula if you can provide how you would like a multi-match handled.

enter image description here

Upvotes: 3

Robert Ilbrink
Robert Ilbrink

Reputation: 7953

If you just want to show all "Tables" in your list, why not use the Autofilter end type Table in the search field. This way only items with the word "Table" in the string will show up. All other rows will be hidden.

Regards,

Robert

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91326

Another possibility is ADO. This will return two rows when an item occurs twice. It would also be possible to play around with another column in ToFind that allowed a Not Like : Like '%' & [ToFind] & '%' And Not Like '%' & [NotToFind] & '%'

Input

enter image description here

Result

enter image description here

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''[ToFind] is a named range, but it does not have to be.
strSQL = "SELECT DISTINCT [List], [ToFind] " _
       & "FROM [Sheet1$A:A] a, " _
       & "[ToFind] b " _
       & "WHERE List Like '%' & [ToFind] & '%'"

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166156

You can create a new column and use this UDF:

Function WhatIsIt(LineItem As Range, AllThings As Range) As String
    Dim rv As String, c As Range
    Dim v As String, thing As String

    v = UCase(LineItem.Cells(1).Value)
    rv = ""

    If Len(v) > 0 Then
        For Each c In AllThings.Cells
            thing = c.Value
            If Len(thing) > 0 And InStr(v, UCase(thing)) > 0 Then
                rv = thing
                Exit For
            End If
        Next c
    End If
    WhatIsIt = rv
End Function

"AllThings" is a range with a list of what you want to look for. Make sure to put longer terms first: ie. "sofa table" should come before "sofa" or "table".

Note it could use some improvement: it will also return matches when a term is only part of another word in the item description.

Upvotes: 0

Related Questions