Pulling Multiple Column Headers

[sfm456]

[TABLE="width: 783"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 6"][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Plan[/TD]
[TD]Design[/TD]
[TD]Build[/TD]
[TD]Test[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]LATE[/TD]
[TD]LATE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Orange [/TD]
[TD][/TD]
[TD]LATE[/TD]
[TD]COMPLETE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]Banana[/TD]
[TD]LATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]Pear[/TD]
[TD]LATE[/TD]
[TD][/TD]
[TD]COMPLETE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]Strawberry[/TD]
[TD]LATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am working with the table above and am trying to use an index array to get to this output:

ALL LATE ITEMS
[TABLE="width: 312"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Scope Name[/TD]
[TD]Phase[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]Design[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]Build[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Orange[/TD]
[TD]Design[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pear[/TD]
[TD]Plan[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banana[/TD]
[TD]Plan[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Strawberry[/TD]
[TD]Plan[/TD]
[/TR]
</tbody>[/TABLE]

It feels like it should be so simple and I can't figure it out!

Right now I have
=IFERROR(INDEX($C$3:$I$3,SMALL(IF(C4:I4="LATE",COLUMN($C$1:$I$1)-COLUMN($C$1)+1),ROWS($B$1:B1))),"")


[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

if there is a large amount of data, suggest you avoid formulas and work with a query or query table. I think the name (query or query table) will vary depending on your excel version

anyway, SQL for it could be like

SELECT ID, Name, 'Plan' AS [Phase]
FROM your_table
WHERE Plan = 'LATE'
UNION ALL
SELECT ID, Name, 'Design'
FROM your_table
WHERE Design = 'LATE'
SELECT ID, Name, 'Build'
FROM your_table
WHERE Build = 'LATE'

This will readily handle huge data volumes & needs to be refreshed like a pivot table. Can be set up via ALT-D-D-N and follow the wizard. Easier if you give your data a normal (non-dynamic) named range before starting. or you can use a worksheet name instead. syntax is like [sheetname$] in place of where I've written your_table

hth

 

[MandeepBaluja]

Put all ID in column J and Scope name in column K , Phase in Column L

[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[/TR]
[TR]
[TD="class: xl63"]ID[/TD]
[TD="class: xl63"]Name[/TD]
[TD="class: xl63"]Plan[/TD]
[TD="class: xl63"]Design[/TD]
[TD="class: xl63"]Build[/TD]
[TD="class: xl63"]Test[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Apple[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]LATE[/TD]
[TD="class: xl63"]LATE[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Orange[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]LATE[/TD]
[TD="class: xl63"]COMPLETE[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]Banana[/TD]
[TD="class: xl63"]LATE[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]Pear[/TD]
[TD="class: xl63"]LATE[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]LATE[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Strawberry[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]LATE[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[/TR]
[TR]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Scope Name[/TD]
[TD="class: xl65"]Phase[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"]Apple[/TD]
[TD="class: xl65"]Design[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"]Apple[/TD]
[TD="class: xl65"]Build[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"]Orange[/TD]
[TD="class: xl65"]Design[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"]Pear[/TD]
[TD="class: xl65"]Plan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65"]Banana[/TD]
[TD="class: xl65"]Plan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"]Strawberry[/TD]
[TD="class: xl65"]Design[/TD]
[/TR]
</tbody>[/TABLE]
In L2 put this formula with array

=INDEX($C$1:$F$1,SMALL(IF(INDIRECT("C"&MATCH(K2,$B$2:$B$6,0)+1&":F"&MATCH(K2,$B$2:$B$6,0)+1)="LATE",COLUMN(C2:F2)-2),COUNTIF($K$1:K2,K2)))

 

[sfm456]

I haven't been able to make this work - is there a mistype in the formula at all?

 

[MandeepBaluja]

Have you pressed ctrl+shift+enter ??? send me your email id I will send you the attachment.