+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

    Question 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.
    Last edited by excelkid; 11-19-2011 at 08:10 PM.

  2. #2
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Bump, high hopes to have this solved from any excel expert...anyone any ideas please?

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

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

    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.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    @Haseeb, Thank you very much for your valuable input. Here you go, hope this will help.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

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

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

  6. #6
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Wow Haseeb! - Are you a some kind of genius? You sure are! Thanks mate. Used some dynamic named range with relative reference. That sounds so easy but I so want to know how did you do this.

    But before that, it has some slight issues, if you can find some solution for that too. I'd like the keywords and the competitors to be flexible. For example, keywords can be 20 or only 4. Similarly, for each keyword, it supports upto 24 competitors I believe. Can we make that flexible too?

    Now, the most important part, if you repeat competitor-1 for example for the same keyword. On sheet 2, it only shows the position for the first instance of competitor-1.

    Please see attached file. See keyword-12. In this case, if on sheet 2, it can show like "1 & 4".

    I think I'll be able to implement some basic sort functions from here, also evaluate the top competitors and some other simple tasks. I am having a lot of other ideas too but unfortunately don't know advanced excel at all. At the same time, am hesitating to ask for more help.

    But whenever you are free, please let me know. I'll request you for some additions to this sheet. Thank you sooooooo much Haseeb!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Maybe instead of an "1 & 4" - I think "1,4" would be better. Just because, I think it will help in further calculations if needed I believe.

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

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

    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Alright, no problem. Thanks a lot. I think last way of doing this was more appropriate but I can understand that you don't know VBA. So yes, I appreciate this too.

    Last one made more sense to me just because am thinking to take it a little further. But never mind, I think i can take it from here.

    Thanks so much!

  10. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

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

    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

    Please Login or Register  to view this content.
    See the attached.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Thanks so much Haseeb. You're truly very helpful. I don't understand the code frankly but I do see the file.

    I wanted to upgrade to Excel 2010, so that I could do something more simply with the latest version. However, it seems like 2010 installation is no good with my Windows 7 machine.

    I tried uninstalling 2003 and right now, I have nothing with me. I'll check this ASAP though.

    Thanks a bunch.

  12. #12
    Registered User
    Join Date
    11-19-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Just checked this file, it works perfect. It doesn't add keywords on sheet 2 automatically however, its as good as that, because once you add another more columns, it works.

    Thanks mate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1