+ Reply to Thread
Results 1 to 7 of 7

Need help pulling column headers based on table values (not max or min)

  1. #1
    Registered User
    Join Date
    06-12-2014
    Posts
    7

    Need help pulling column headers based on table values (not max or min)

    Hi folks:

    I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.

    I've attached an example spreadsheet for reference. Basically, I need two formulas:

    1. Formula to pull column header (Receiver name) based on Sender. This is a necessity.
    2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).

    My actual data file has 80+ senders and 100+ receivers.

    Please advise,
    RD
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,121

    Re: Need help pulling column headers based on table values (not max or min)

    Try this macro

    reverse matrix.xlsm

  3. #3
    Registered User
    Join Date
    06-12-2014
    Posts
    7

    Re: Need help pulling column headers based on table values (not max or min)

    That macro seems to work in the excel file provided, but now how do I get that marco into my source workbook?

    Two other noteworthy items:
    A. I'm working on a Mac
    B. The actual source workbook has additional (erroneous) data that must be included in the final product but is not used within this objective.

    Lastly, I have no prior experience writing macros, which is why I was seeking formulas....
    Last edited by red5030; 06-12-2014 at 03:56 PM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Need help pulling column headers based on table values (not max or min)

    This works on my PC (Excel 2013); don't know how it will wok on a Mac:
    1. Select $A$3:$E$8
    2. Press Ctrl+Shift+F3 (or Mac equivalent); this presents dialogue box "Create Names from Selection" (Also in the menu you are using this should be available wherever "Name Manager" is stored.)
    3. Select from "Create names from values in:" Top Row and Left Column. Click OK.

    This will insert "_" in your 'Division' names. Inconvenient, but this formula works. Starting in C16 enter this and copy down to C20:

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


    Note the space between ")" and "INDIRECT" midway through the formula. As I understand it, this is a 'new' shortcut method for returning the intersection of the two named ranges.
    If you wish to simply type the individual names in, use for example:

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


    Ugh!! I prefer the first formula.

    Hope this helps.
    Last edited by FlameRetired; 06-12-2014 at 06:16 PM.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Need help pulling column headers based on table values (not max or min)

    This one is probably better than my previous "formula":


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


    Enter into C16:C20.

    I tried it on your file. It worked.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Need help pulling column headers based on table values (not max or min)

    Sorry red5030; I misread your request. Please ignore my last two posts. I will spend more time with this one.

  7. #7
    Registered User
    Join Date
    06-12-2014
    Posts
    7

    Re: Need help pulling column headers based on table values (not max or min)

    Bump.

    Hi All- I've revised the request in which I'm seeking help as documented below:

    he Objective: Calculate gross profit % (which is cost/selling price recorded in different accounts) for each combination of sellers and buyers from a data set using account level detail. Total detail is A1:Q33441.

    The Components: Sellers (Column C); Buyers (Column D); numeric codes for the related cost account 1, cost account 2, cost account 3, and the sole sales account (Column M); the dollar amounts of column M (in column Q).

    Below please find my initial though pattern regarding formulas (trying to avoid using a macro):

    Step 1: Pull all of the seller & buyer combos from columns C & D. Note that for each seller there are multiple buyers.
    Step 2a: Combine VLOOKUP and SUMIF (or CHOOSE and SUMIF?) formulas to pull the $$ sum of all 3 costs accounts for each buyer/seller combo (column M & Q).
    Step 2b: perform Step 2a for the selling $$ (column M & Q).
    Step 3: VLOOKUP 2a and divide it into VLOOKUP 2b to calculate the gross profit % for each buyer/seller combo.

    This analysis will need to be completed for 54 individual months of data, which is why I'm seeking help at this point in time.

    Any help would be greatly appreciated (either in total or in specific steps).

    Cheers,
    Ross

+ 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. How to lookup values on a table and get column headers for filtered criteria?
    By MichaelGaribaldi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-19-2013, 03:30 PM
  2. Pivot table not pulling column headers
    By joyhampton in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-18-2013, 11:21 PM
  3. Macro to copy row values based on a identical column headers
    By Vijaya Pratap in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2013, 04:12 AM
  4. [SOLVED] Return Column Headers based on row rank with duplicate values in row
    By carlwin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-31-2013, 12:24 AM
  5. [SOLVED] How to select from a table of values based upon column and row headers
    By Peteryoull in forum Excel General
    Replies: 4
    Last Post: 07-30-2012, 05:58 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