Extract duplicate parameters and sum up the associated column values for the URLs

[ekguru]

I am trying to find out the urls by extracting common parameters (bolded in black) in the following sample urls and add the views/visits column values of these URLs that have the black bolded variables in common to essentially eliminate duplicate rows (as shown below)...have tried multiple things and yet not able to achieve the desired results..can someone please help me with this?

Couple of things:

1. The sequence of the parameters might be in different for each URL
2. Some of the parameters might have junk values associated (link traces from referral links) as you can see in the last two examples that will always be similar to the last two examples mentioned and might strip the & symbol from the URL
3. You can ignore any other parameters in the URLs
4. I can use VBA

Thanks

URL : Visits - Views

http://www.abc.com/index.ac?nav=price&queryProduct=ABC&request_locale=en&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=ABC&request_locale=en&tab=1 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=CDE&request_locale=en&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=ABC&request_locale=en&source=un&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=prod&queryProduct=ABC&request_locale=en&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=CDE&request_locale=en&tab=1 : 100 - 200
http://www.abc.com/index.ac?request_locale=en/abc_index.htmltab=0/abc_index.htmlqueryProduct=ABC/abc_index.htmlnav=prod : 100 - 200
http://www.abc.com/index.ac?lang=E&request_locale=en/sub/cde/index2.jsptab=0/sub/cde/index2.jspqueryProduct=ABC/sub/cde/index2.jspnav=price?Curr=C : 100 - 200
​

 

[ekguru]

I was able to extract the parameters using this formula - IFERROR(MID(A1, SEARCH(B1 & "=", A1) + LEN(B1) + 1, IFERROR(SEARCH("&", A1, SEARCH(B1 & "=", A1)) - SEARCH(B1 & "=", A1) - LEN(B1) - 1, LEN(A1))), "")


Can someone help with writing a macro to achieve the desired results.

I am trying to find out the urls by extracting common parameters (bolded in black) in the following sample urls and add the views/visits column values of these URLs that have the black bolded variables in common to essentially eliminate duplicate rows (as shown below)...have tried multiple things and yet not able to achieve the desired results..can someone please help me with this?

Couple of things:

1. The sequence of the parameters might be in different for each URL
2. Some of the parameters might have junk values associated (link traces from referral links) as you can see in the last two examples that will always be similar to the last two examples mentioned and might strip the & symbol from the URL
3. You can ignore any other parameters in the URLs
4. I can use VBA

Thanks

URL : Visits - Views

http://www.abc.com/index.ac?nav=price&queryProduct=ABC&request_locale=en&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=ABC&request_locale=en&tab=1 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=CDE&request_locale=en&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=ABC&request_locale=en&source=un&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=prod&queryProduct=ABC&request_locale=en&tab=0 : 100 - 200
http://www.abc.com/index.ac?nav=price&queryProduct=CDE&request_locale=en&tab=1 : 100 - 200
http://www.abc.com/index.ac?request_locale=en/abc_index.htmltab=0/abc_index.htmlqueryProduct=ABC/abc_index.htmlnav=prod : 100 - 200
http://www.abc.com/index.ac?lang=E&request_locale=en/sub/cde/index2.jsptab=0/sub/cde/index2.jspqueryProduct=ABC/sub/cde/index2.jspnav=price?Curr=C : 100 - 200
​

 

[ekguru]

Bump..

I was able to extract the parameters using this formula - IFERROR(MID(A1, SEARCH(B1 & "=", A1) + LEN(B1) + 1, IFERROR(SEARCH("&", A1, SEARCH(B1 & "=", A1)) - SEARCH(B1 & "=", A1) - LEN(B1) - 1, LEN(A1))), "")


Can someone help with writing a macro to achieve the desired results.

 

[ekguru]

No one can help??? This shouldn't be a difficult question for the experts out here...

Bump..