+ Reply to Thread
Results 1 to 13 of 13

Return all Product Names listed against a single Account

  1. #1
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Return all Product Names listed against a single Account

    I have a list of all sold deals by specific Account IDs, which lists multiple products by name.

    In a separate summary sheet I have a listing of Accounts (and IDs), for which I am trying to return all the different product type names into one cell entry, for that particular account

    There are over 80 accounts in 1 report, and 250+ deals in the other sheet.

    How can I lookup the Account ID in the sold deals sheet, and return all the different product name entries.

    Thanks in advance
    Regards
    Rohan
    Last edited by RohanF; 10-17-2016 at 05:17 AM. Reason: Added attachment for reference

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,033

    Re: Return all Product Names listed against a single Account

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Return all Product Names listed against a single Account

    Hi there, thanks for the quick response.

    I have added a sample reference sheet to illustrate what i am trying to achieve.

    The summary sheet tab has red columns for the data i trying to return. I seem to struggle returning values that are not numerical; as the green columns are numerical values, which I used SUMIFS to return the correct values.

    The sold deal data tab is a brief sample of the data range i am trying to lookup.

    Thanks again for any support .

    Regards
    Rohan
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,033

    Re: Return all Product Names listed against a single Account

    I don't see any attempted formulae in your attachment or expected outcomes. The latter are essential, please, and the former helpful to see.

  5. #5
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Return all Product Names listed against a single Account

    Hi ALi,

    Apologies for not adding attempted formula, I am not exactly sure the best formula to use to return the matches i'm looking for

    Regards
    Rohan

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,033

    Re: Return all Product Names listed against a single Account

    Fine, but we MUST know what your expected outcomes are! Please add at least some of these to your sample file.

  7. #7
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Return all Product Names listed against a single Account

    What would be handy to know would be what is the equivalent of using a SUMIFS formula to find word matches.

    For example to match Annual recurring revenue in the attached sheet (green column), is used SUMIFS to return the result.

    What im struggling to do is do the same find, match and return for the red columns).

    Thanks again

    Regards
    Rohan
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,033

    Re: Return all Product Names listed against a single Account

    I'm sorry, Rohan, but you are not giving the information I am asking for! In those red columns, I want to see your expected outcomes - exactly as they should appear, not as vague statements. You have not even indicated where in the source sheet you expect the information to be found. In addition, I don't think that your sample data is extensive enough: the information in your first red column suggests that there will be multiple rows per customer in the source data, but your sample data doesn't show this.

    I know it is obvious to YOU what you want, but I'm not clairvoyant and I need a bit more specific information, please.

  9. #9
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Return all Product Names listed against a single Account

    On the latest file attached i have stated in the red boxes the result I am trying to achieve,m but i just don't know which formula will work best.

    Kind regards
    Rohan
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Return all Product Names listed against a single Account

    My apologies that i am not conveying the query message across. I'm not to good at this.

    Thanks again
    Rohan

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,033

    Re: Return all Product Names listed against a single Account

    OK, so it seems to me that a lot of these columns can be organised using this type of function:

    =IF(COUNTIF(lookup_array,value)>0,value_if_true,value_if_false)

    But again, you haven't given expected outcomes. Type in the red columns the results you expect on each of those rows.

  12. #12
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Return all Product Names listed against a single Account

    Hi Ali,

    I really do appreciate the help.

    Its getting very late in Melbourne tonight, and i have early start with kids and work; will try again when back at work.

    Thanks again and I'll let you know how i go...

    Regards
    Rohan

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,033

    Re: Return all Product Names listed against a single Account

    OK - come back to it tomorrow when you're fresh. When you do, please attach a copy of the file with the detail I've requested, and then, hopefully, you'll get a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return multiple names into a single cell
    By Wikster7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-14-2016, 02:14 PM
  2. Replies: 2
    Last Post: 09-10-2014, 08:56 AM
  3. [SOLVED] Look up and return top 3 ranked product names from table range
    By dma1976 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2014, 07:38 AM
  4. if Account number = return Account Name
    By mom2carisa in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-04-2013, 06:03 PM
  5. Return Account Nickname if Account Number Matches
    By spacle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-03-2013, 11:04 AM
  6. Writing outcomes and then names listed below
    By parachutistbynight in forum Excel General
    Replies: 6
    Last Post: 08-02-2009, 08:53 PM
  7. [SOLVED] Count If-e store names listed.
    By Jim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2005, 11:05 AM

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