Pulling Multiple Column Headers

[sfm456]

A B C D E F ID Name Plan Design Build Test 1 Apple LATE LATE 2 Orange LATE COMPLETE 3 Banana LATE 4 Pear LATE COMPLETE 5 Strawberry LATE <tbody> </tbody> I am working with the table above and am trying to use an index array to get to this output: ALL LATE ITEMS ID Scope Name Phase 1 Apple Design 1 Apple Build 2 Orange Design 3 Pear Plan 4 Banana Plan 5 Strawberry Plan <colgroup><col><col><col></colgroup><tbody> </tbody> 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))),"")

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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

A	B	C	D	E	F
ID	Name	Plan	Design	Build	Test
1	Apple		LATE	LATE
2	Orange		LATE	COMPLETE
3	Banana	LATE
4	Pear	LATE		LATE
5	Strawberry		LATE

<tbody>
</tbody>

J	K	L
ID	Scope Name	Phase
1	Apple	Design
1	Apple	Build
2	Orange	Design
3	Pear	Plan
4	Banana	Plan
5	Strawberry	Design

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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.