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

**red5030** · 06-12-2014, 02:58 PM

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

**ranman256** · 06-12-2014, 03:35 PM

Try this macro

reverse matrix.xlsm

**red5030** · 06-12-2014, 03:49 PM

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....

**FlameRetired** · 06-12-2014, 05:53 PM

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:

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:

Please Login or Register  to view this content.

Ugh!! I prefer the first formula.

Hope this helps.

**FlameRetired** · 06-13-2014, 04:06 PM

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

Formula:

Please Login or Register  to view this content.

Enter into C16:C20.

I tried it on your file. It worked.

**FlameRetired** · 06-13-2014, 05:14 PM

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

**red5030** · 07-08-2014, 10:45 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

How to lookup values on a table and get column headers for filtered criteria?

Pivot table not pulling column headers

Macro to copy row values based on a identical column headers

[SOLVED] Return Column Headers based on row rank with duplicate values in row

[SOLVED] How to select from a table of values based upon column and row headers

Bookmarks

Bookmarks

Posting Permissions