Thread: Finding Unique URL's (values), thier total occurrences (in numbers) based on keyword

Alright, please download this excel sheet here http://www.mediafire.com/?nfi7ycvnza3ieit - Its only 24kb.

Now this is a very real world situation and will surely help thousands of people. In optimizing a website, we do keyword analysis. When we are done with final keywords, there comes the stage of competition analysis.

For that purpose, I've made this excel sheet and need some help from excel gurus. Column B from Sheet 1 will have all the final keywords. Column C shows the top 10 URL's for these keywords. Right now, sample data is used but just so you can understand what needs to be done.

Now on sheet 2, in Column A, it should show all the unique URL's from Sheet 1 - Column C. On Sheet 2, in Column B, it should show the number of occurrences for each URL. Once that is done, we need to list the keywords from C3 automatically, from Sheet 1 Column B. When all of these keywords are listed, it should show Sheet 1 Column D ranking position for each URL under the respective keywords cell. If some URL is not listed for any keyword, it should be empty.

Hope that sounds doable? It sounds very difficult to me but am a new member here and from the kind of experts I've seen here. I have high hopes friends!

Thank you so much.

Hello excelkid,

Since forum has been allowed to attach files, please attach your sample file on the forum rather than a link to other websites. Most of the members are not following the link to other websites.

For attaching sample file, Go to Advanced (in the bottom) then down click Manage Attachments & upload your file.

I am not sure, Is this do you need??? See the attached. Used some dynamic named range with relative reference.

it can show like "1 & 4"

If you want to show multiple instances in a single cell separated by comma or any other character, that would require VBA. I am sorry i don't know about VBA. hope expert will assist you.

Attached is an alternate way. You can select a keywords from the list, then you can see it's different ranks in multiple columns, If any. All the formulas you can copy down & across, also data is in dynamic. You can add new data, it will update automatically.

Hope this helps.

I have found a code to concatenate, insert it & use as,

C4, copy down & across.

=LOOKUP(Big_Str,CHOOSE({1,2},"",CONCATIF(INDEX(Curr_Range,0,2),$A4,INDEX(Curr_Range,0,3),", ",TRUE)))

CONCATIF code

Function CONCATIF(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like SUMIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(CONCATIF, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    CONCATIF = CONCATIF & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    CONCATIF = Mid(CONCATIF, Len(Delimiter) + 1)
End Function

See the attached.