Need a formula to lookup column header based on data results within a table

F

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Below is an example data set:

[TABLE="width: 673"]
<TBODY>[TR]
[TD]Period</SPAN>[/TD]
[TD]5302</SPAN>[/TD]
[TD]1048</SPAN>[/TD]
[TD]1028</SPAN>[/TD]
[TD]1037-1</SPAN>[/TD]
[TD]5107</SPAN>[/TD]
[TD]1022</SPAN>[/TD]
[TD]5101</SPAN>[/TD]
[/TR]
[TR]
[TD]Release Period 1</SPAN>[/TD]
[TD]Good[/TD]
[TD]Not Clear[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Not Clear[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Release Period 2</SPAN>[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Release Period 3</SPAN>[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Not Clear[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=5></COLGROUP>[/TABLE]



Basically, I want to create a formula where a result table will already have the row headers "Release Period 1", "Release Period 2", etc. and will return to the right each column header that has a "Not Clear" value within the correspodning matching row.

For example, for my formula in my results table to the right of "Release Period" 1, it would return in the adjacent cells to the right, all the column headers that have a "Not Clear" value within that period, in which case would be 1048, and the next cell would be 1022.

For Release Period 2, no column headers would be returned, since there are no Not Clear's in Release Period 2.

For Release Period 3, the adjacent cell would return 1037-1.

Any idea how to put this into a formula? Where it queues off the row header and searchs for "Not Clear's" to return the column header from the original data table? I assume I'll need it as an array formula.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
freeb1893 said:
Below is an example data set:

[TABLE="width: 673"]
<tbody>[TR]
[TD]Period[/TD]
[TD]5302[/TD]
[TD]1048[/TD]
[TD]1028[/TD]
[TD]1037-1[/TD]
[TD]5107[/TD]
[TD]1022[/TD]
[TD]5101[/TD]
[/TR]
[TR]
[TD]Release Period 1[/TD]
[TD]Good[/TD]
[TD]Not Clear[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Not Clear[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Release Period 2[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Release Period 3[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Not Clear[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]



Basically, I want to create a formula where a result table will already have the row headers "Release Period 1", "Release Period 2", etc. and will return to the right each column header that has a "Not Clear" value within the correspodning matching row.

For example, for my formula in my results table to the right of "Release Period" 1, it would return in the adjacent cells to the right, all the column headers that have a "Not Clear" value within that period, in which case would be 1048, and the next cell would be 1022.

For Release Period 2, no column headers would be returned, since there are no Not Clear's in Release Period 2.

For Release Period 3, the adjacent cell would return 1037-1.

Any idea how to put this into a formula? Where it queues off the row header and searchs for "Not Clear's" to return the column header from the original data table? I assume I'll need it as an array formula.
Click to expand...

Sheet1

*ABCDEFGH
Period
Release Period 1GoodNot ClearGoodGoodGoodNot ClearGood
Release Period 2GoodGoodGoodGoodGoodGoodGood
Release Period 3GoodGoodGoodNot ClearGoodGoodGood
********
********
********
Release Period 3******

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:87px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]5302[/TD]
[TD="align: right"]1048[/TD]
[TD="align: right"]1028[/TD]
[TD="align: right"]1037-1[/TD]
[TD="align: right"]5107[/TD]
[TD="align: right"]1022[/TD]
[TD="align: right"]5101[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1037-1[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B8{=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$4,MATCH($A$8,$A$2:$A$4,0),0)="not clear",COLUMN($B$2:$H$4)-ROW($B$2)+1),COLUMNS($A$2:A2))),"")}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Drag accross.
 
Last edited:
Upvote 0
VBA approach: The results will appear in Sheet2.
Code: 
Sub Test()
    Dim bottomA As Integer
    bottomA = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Sheets("Sheet1").Range("A1:A" & bottomA).Copy Sheets("Sheet2").Range("A1")
    For Each c In Sheets("Sheet1").Range("B2:H" & bottomA)
        If c = "Not Clear" Then
            Cells(1, c.Column).Copy Sheets("Sheet2").Range("IV" & c.Row).End(xlToLeft).Offset(0, 1)
        End If
    Next c
End Sub
 
Upvote 0
It appears something isn't clicking with this formula. It's not causing an error, but it's not providing the column header in return, it's populating with a blank space.

Robert Mika said:
Sheet1

*
A
B
C
D
E
F
G
H
Period
Release Period 1
Good
Not Clear
Good
Good
Good
Not Clear
Good
Release Period 2
Good
Good
Good
Good
Good
Good
Good
Release Period 3
Good
Good
Good
Not Clear
Good
Good
Good
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Release Period 3
*
*
*
*
*
*

<TBODY>
[TD="bgcolor: #cacaca, align: center"]1
[/TD]

[TD="align: right"]5302
[/TD]
[TD="align: right"]1048
[/TD]
[TD="align: right"]1028
[/TD]
[TD="align: right"]1037-1
[/TD]
[TD="align: right"]5107
[/TD]
[TD="align: right"]1022
[/TD]
[TD="align: right"]5101
[/TD]

[TD="bgcolor: #cacaca, align: center"]2
[/TD]

[TD="bgcolor: #cacaca, align: center"]3
[/TD]

[TD="bgcolor: #cacaca, align: center"]4
[/TD]

[TD="bgcolor: #cacaca, align: center"]5
[/TD]

[TD="bgcolor: #cacaca, align: center"]6
[/TD]

[TD="bgcolor: #cacaca, align: center"]7
[/TD]

[TD="bgcolor: #cacaca, align: center"]8
[/TD]

[TD="align: right"]1037-1
[/TD]

</TBODY>

Spreadsheet Formulas
Cell
Formula
B8
{=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$4,MATCH($A$8,$A$2:$A$4,0),0)="not clear",COLUMN($B$2:$H$4)-ROW($B$2)+1),COLUMNS($A$2:A2))),"")}

<TBODY>
</TBODY>

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4

Drag accross.
Click to expand...
 
Upvote 0
Robert Mika said:
=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$4,MATCH($A$8,$A$2:$A$4,0),0)="not clear",COLUMN($B$2:$H$4)-ROW($B$2)+1),COLUMNS($A$2:A2))),"")
Click to expand...
I think Robert's formula should work for you, provided ..
1. You adjust for your actual layout (test first with the exact layout shown)
2. Remove the red $ sign shown above.
3. As indicated earlier in the thread, enter the formula without the {} but confirm it with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.
 
Last edited:
Upvote 0
Thanks! Sorry about the dupe. It's is indexing now, but it's not indexing the right data for some reason. It appears to be indexing the column header 2 columns to the right of every "Not Clear" hit, rather than the column header directly above the "Not Clear" hit. Any ideas?
Peter_SSs said:
I think Robert's formula should work for you, provided ..
1. You adjust for your actual layout (test first with the exact layout shown)
2. Remove the red $ sign shown above.
3. As indicated earlier in the thread, enter the formula without the {} but confirm it with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.
Click to expand...
 
Upvote 0
Hello to all. I was just wondering if anyone has tried the macro I suggested in Post# 3.
 
Upvote 0
freeb1893 said:
Thanks! Sorry about the dupe. It's is indexing now, but it's not indexing the right data for some reason. It appears to be indexing the column header 2 columns to the right of every "Not Clear" hit, rather than the column header directly above the "Not Clear" hit. Any ideas?
Click to expand...
That would indicate that your layout is different to Robert's. Your sample data gave no indication of what rows and/or columns it occupies so Robert has taken what appears to me to be a pretty good stab at interpreting your image.

Assuming yours is different, for a start I suggest you make a sheet exactly like Robert's to prove that his formu works, then see if you can adapt to your layout. If not, post back with more details about that.



mumps said:
Hello to all. I was just wondering if anyone has tried the macro I suggested in Post# 3.
Click to expand...
I hadn't because the OP specifically asked for a formula approach.
Since you seem to want some feedback, I have now looked and it certainly does what you say. I guess some things that may need consideration are ..
- Does Sheet2 already exist?
- If so, does it already contain data that might be over-written by the macro?
- Does the OP want the results to automatically update if data in the original table is altered, as would happen with the formula approach?



@Robert re Excel jeanie
- Might you consider turning on the jeanie gridlines to make your screen shots clearer (in my mind anyway)?
For example, with a quick glance at the "1022" value in the top row of your screen shot it isn't immediately clear whether that value is in G1 or H1. Gridlines would remove any doubt.
- Are you aware of what is causing the asterisks in your screen shot?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
438
MARZIOTULLIO
M
S
  • Solved
Changing the [@column] column number within a table
Replies
2
Views
167
sgcannon
S
trishcollins
  • Solved
Excel formula to return the formula from a table using the table header as a reference
2
Replies
17
Views
771
trishcollins
trishcollins
V
  • Solved
Is it possible to sort table column values and display the header value in the next set of table columns
Replies
7
Views
165
Peter_SSs
Peter_SSs
D
  • Solved
Lookup value in table, return the team header above
2
Replies
10
Views
729
Domenic
D

Forum statistics

Threads
1,225,477
Messages
6,185,210
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top