+ Reply to Thread
Results 1 to 7 of 7

Return value for mapped rows

  1. #1
    Registered User
    Join Date
    02-23-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    2016
    Posts
    24

    Return value for mapped rows

    Hi all,

    I'm having trouble with a way to return the value for mapped rows. Example workbook attached.

    Basically I want there to be a way to return the figure from the Num column (F) to the Group 1 and Group 2 columns (G and H) with the formula in the G and H columns.

    So for example, because Group 1 is mapped to Row A and Row B only the values of 21 and 27 is returned in the Group 1 column. Because Group 2 is mapped to Row B and Row C, the values of 27 and 30 is returned in the Group 2 column.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by jkerrigan93; 07-28-2020 at 02:21 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Return value for mapped rows

    This may be perfectly obvious to you, but to me, it's as clear as mud, sorry

    Which table is the data table, and which 1 is the answer table? Walk me through a few examples
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-23-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    2016
    Posts
    24

    Re: Return value for mapped rows

    Sorry I probably wasn't clear enough in my post.

    The data and answer table are both Table2. The data column is F and the answer columns are G and H. Table1 is just used for the calculation.

    It's a bit hard to explain so I had another crack at it and nearly have the correct formula. I've updated the attached workbook now.

    The problem here is that 'Row B' is doubling up and returning 54 instead of 27.

    Just to walk through my example, in Table2 I have a some figures in column F which I want to return in columns G and H. However I only want to return those figures based on the mapping I have in Table1. In Table1 I am saying Group 1 consists of Row A and Row B so I only want to return those 2 values (i.e. exclude Row C). In Group 2 I've set it up similarly except I'm excluding Row A. My formula seems to be close but it's doubling up values in Row B (most likely due to it appearing twice in Table 1).

    Also I would post the formula here but I keep getting an error due to not enough posts on my account
    Last edited by AliGW; 07-28-2020 at 02:48 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Return value for mapped rows

    In G2 copied down:

    =IF(COUNTIFS(Table1[Group],Table2[[#Headers],[Group 1]],Table1[Rows],[@Text])>0,[@Num],0)

    In H2 copied down:

    =IF(COUNTIFS(Table1[Group],Table2[[#Headers],[Group 2]],Table1[Rows],[@Text])>0,[@Num],0)

    It is only weblinks that you are not permitted to post before you have 10 posts here.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Return value for mapped rows

    Try in G2:

    Please Login or Register  to view this content.
    Drag down and accross
    Quang PT

  6. #6
    Registered User
    Join Date
    02-23-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    2016
    Posts
    24

    Re: Return value for mapped rows

    Quote Originally Posted by AliGW View Post
    It is only weblinks that you are not permitted to post before you have 10 posts here.
    Wow so simple thanks so much!

    Thanks as well bebo. The countifs is working so just going to use that. Will mark as solved.

    Thanks

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Return value for mapped rows

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. vba code for mapped drive
    By tshepolt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2019, 07:12 AM
  2. Mapped XML Import refreshes and formulas jump rows
    By Tails1979 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-03-2017, 11:46 PM
  3. Help with macro to return a Mapped drive Address.
    By fkinsinger in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2013, 02:12 PM
  4. Excel 2007 : Mapped dependencies and filtering?
    By stoiss in forum Excel General
    Replies: 1
    Last Post: 02-29-2012, 08:46 PM
  5. mapped driver macro option
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-09-2009, 01:47 PM
  6. ODBC from not mapped Drive
    By iancallaghan87 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-14-2007, 10:53 AM
  7. Test to see if a server is mapped
    By Tempy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2005, 10: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