Excel search and rank code

June 10, 2009 at 06:24:54
Specs: Windows XP pro SP3
Does anyone have a code for searching for 4-5 codes within columns in a single excel worksheet then returning the list of matching rows, ordered by greatest number of matches first. the total dataset is only around 200 rows

Many thanks

Or can anyone recommend a basic online course for learning how to write codes for excel etc

See More: Excel search and rank code

Report •

June 10, 2009 at 09:25:44
show sample of your excel file, then describe what you want the output to look like.

Report •

June 10, 2009 at 11:41:41
Not sure how to upload a file?

I want to search for a code A1,B2,C3 for all matches in column B, I want the result to show all matches, so from the list below the result would be: number 4, (as exact match) then 1,3 (as they both have 2 matches) and finally 2 (with just one match)

1 A3,B2,C3
2 A2,B2,C2
3 A1,B1,C3
4 A1,B2,C3
5 A2,B1,C1

This is the code I am using at the moment: to match code in J14 with codes in Column B, but it only shows an exact match, how do I make it list all part matches and rank them?

Sub Search()
Range("F25").CurrentRegion.ClearContents: Range("F25") = "Matches"
FindWhat = UCase(Range("J14"))
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Cell.Offset(, -1).Resize(, 3).Copy Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub

Report •

Related Solutions

Ask Question