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

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
187
Below is an example data set:

Period</SPAN>5302</SPAN>1048</SPAN>1028</SPAN>1037-1</SPAN>5107</SPAN>1022</SPAN>5101</SPAN>
Release Period 1</SPAN>GoodNot ClearGoodGoodGoodNot ClearGood
Release Period 2</SPAN>GoodGoodGoodGoodGoodGoodGood
Release Period 3</SPAN>GoodGoodGoodNot ClearGoodGoodGood

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=5></COLGROUP>



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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Below is an example data set:

Period5302104810281037-1510710225101
Release Period 1GoodNot ClearGoodGoodGoodNot ClearGood
Release Period 2GoodGoodGoodGoodGoodGoodGood
Release Period 3GoodGoodGoodNot ClearGoodGoodGood

<tbody>
</tbody>



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.

Sheet1

*ABCDEFGH
1Period5302104810281037-1510710225101
2Release Period 1GoodNot ClearGoodGoodGoodNot ClearGood
3Release Period 2GoodGoodGoodGoodGoodGoodGood
4Release Period 3GoodGoodGoodNot ClearGoodGoodGood
5********
6********
7********
8Release Period 31037-1******

<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>
</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:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,306
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
 

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
187
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.

Sheet1

*
A
B
C
D
E
F
G
H
1
Period
5302
1048
1028
1037-1
5107
1022
5101
2
Release Period 1
Good
Not Clear
Good
Good
Good
Not Clear
Good
3
Release Period 2
Good
Good
Good
Good
Good
Good
Good
4
Release Period 3
Good
Good
Good
Not Clear
Good
Good
Good
5
*
*
*
*
*
*
*
*
6
*
*
*
*
*
*
*
*
7
*
*
*
*
*
*
*
*
8
Release Period 3
1037-1
*
*
*
*
*
*

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

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256

ADVERTISEMENT

Did you confirm Control-Shift-Enter?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,510
Office Version
  1. 365
Platform
  1. Windows
=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))),"")
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:

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
187

ADVERTISEMENT

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,306
Hello to all. I was just wondering if anyone has tried the macro I suggested in Post# 3.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,510
Office Version
  1. 365
Platform
  1. Windows
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?
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.



Hello to all. I was just wondering if anyone has tried the macro I suggested in Post# 3.
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,803
Members
416,983
Latest member
LessThanAverageUser

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
Top