+ Reply to Thread
Results 1 to 9 of 9

formula to combine like cell and results

  1. 09-04-2014, 05:12 PM #1
    bacardi510
    bacardi510 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    Bay Area, CA
    MS-Off Ver
    2013
    Posts
    53

    formula to combine like cell and results

    Hi
    I have a workbook with a pivot table and the results feed into another sheet. My problem is that the results have multiple of the same results but different formats. For example it will show www.la.blogshopper.com, www.bronx.blogshopper.com, www.sanfrancisco.blogshopper.com and each will have a different amount it reports. I need to have all of the sites report as blogshopper.com and the total of all of the sites on one line. Right now they each have a row and the result is in the cell to the right of their name. I have this for hundreds of sites. Some show up as www.websitename.com and some show up as websitename.com and I also have the scenario described above. Any help with this one will be greatly appreciated!
    Reply With QuoteRegister To Reply
  2. 09-04-2014, 05:29 PM #2
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: formula to combine like cell and results

    can you create a helper column in your raw data that would have just the websitename....the MID() function could work for that...then you could include that in your PT and "collect" all the website together....just a thought
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....
    Reply With QuoteRegister To Reply
  3. 09-04-2014, 05:38 PM #3
    Cerbera
    Cerbera is offline
    Forum Contributor Cerbera's Avatar
    Join Date
    07-06-2010
    Location
    Rotorua, New Zealand
    MS-Off Ver
    Excel 2007, 2013, 2016 & 365
    Posts
    137

    Re: formula to combine like cell and results

    If all of the sites were .com domains you could input the below formula in a helper column (it assumes the address is in A1):

    Please Login or Register  to view this content.
    This should pull out just the top level domain.

    If the domain was for example .co.uk you would hit a problem. In this case you'd need to check if the domain was a .x.x extension, in which case you'd alter the above formula to a "-2" at the end. So you'd probably need some logic to search a list of domain extensions, and have an if statement in the formula to apply a different formula depending on the extension type.
    Last edited by Cerbera; 09-04-2014 at 05:42 PM.
    Reply With QuoteRegister To Reply
  4. 09-04-2014, 08:59 PM #4
    bacardi510
    bacardi510 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    Bay Area, CA
    MS-Off Ver
    2013
    Posts
    53

    Re: formula to combine like cell and results

    Thank you for the help! I get a #VALUE error when I copy and paste. I altered the formula to A6 as that is were the website addresses begin. Do you know what I am doing wrong?
    Reply With QuoteRegister To Reply
  5. 09-04-2014, 08:59 PM #5
    bacardi510
    bacardi510 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    Bay Area, CA
    MS-Off Ver
    2013
    Posts
    53

    Re: formula to combine like cell and results

    Not sure what MID() is or how to use it. I will do some research and see what I can find. Thank you!
    Reply With QuoteRegister To Reply
  6. 09-04-2014, 10:18 PM #6
    Cerbera
    Cerbera is offline
    Forum Contributor Cerbera's Avatar
    Join Date
    07-06-2010
    Location
    Rotorua, New Zealand
    MS-Off Ver
    Excel 2007, 2013, 2016 & 365
    Posts
    137

    Re: formula to combine like cell and results

    Hi Bacardi,

    Not sure how your data is set out so I've attached an example with formulas and code - should help to work it out.

    The first column has example addresses, the second the formula and the result.

    The third and fourth show the parts of the formula that I used to get the result.

    web addresses.xlsx
    Reply With QuoteRegister To Reply
  7. 09-04-2014, 10:28 PM #7
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: formula to combine like cell and results

    like this I hope..
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Check the attached file..
    Attached Files Attached Files
    Last edited by Vikas_Gautam; 09-04-2014 at 10:35 PM.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com
    Excel is not a matter of Experience, its a matter of Application.
    Say Thanks, Click * Add Reputation
    Reply With QuoteRegister To Reply
  8. 09-04-2014, 11:36 PM #8
    bacardi510
    bacardi510 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    Bay Area, CA
    MS-Off Ver
    2013
    Posts
    53

    Re: formula to combine like cell and results

    Hey Cerbera,

    Thank you for taking the time to explain that! It works like a charm for 90% of my data. The only issue it appears to have are the sites that are in the list as website.com..... they appear to give an error since I think the formula is looking for two "." Do you know how I can adjust the formula so if a site is website.com the result would be website.com and if it is blah.website.com or www.blah.website.com or even www.website.com it would return website.com? I believe it would be an IF formula mixed in but I am already in over my head Thanks for the help again!!
    Reply With QuoteRegister To Reply
  9. 09-07-2014, 07:18 PM #9
    Cerbera
    Cerbera is offline
    Forum Contributor Cerbera's Avatar
    Join Date
    07-06-2010
    Location
    Rotorua, New Zealand
    MS-Off Ver
    Excel 2007, 2013, 2016 & 365
    Posts
    137

    Re: formula to combine like cell and results

    Hi Bacardi,

    Try changing to formula to: 

    Please Login or Register  to view this content.
    ** This will still not work with extensions such as .com.au or .co.nz as they have 2 "."s.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can't combine multi-criteria lookup that also displays multiple results in one cell
    By klk252 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2013, 10:05 AM
  2. How to combine results of several if clauses in one formula?
    By DaJackal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2013, 10:01 PM
  3. Combine Value of 5 Cells and List Results
    By Andrew.Trevayne in forum Excel General
    Replies: 3
    Last Post: 06-29-2011, 08:30 AM
  4. Formula to combine 2 results
    By ssdsibes in forum Excel General
    Replies: 6
    Last Post: 11-17-2010, 06:49 PM
  5. Combine Coinciding Results.
    By zealot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2009, 07:51 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1