Display the first non-zero occurence in a row in a Pivot table

[karthik104]

Hi,

I have data in a pivot table:

Row Labels	12/27/2015	1/3/2016	1/10/2016	1/17/2016	1/24/2016	1/31/2016	2/7/2016	2/14/2016	2/21/2016	2/28/2016	3/6/2016
ABB	0	0	310	400	500	22	2223	2232	323	6565	655
ABC	777	777	777	777	777	787	798	9899	98982	98956	98956
XYZ	0	0	328	328	328	328	428	5	878	98789	64654
ZMN	0	0	0	0	0	0	0	500	500	500	500

<colgroup><col span="2"><col span="10"></colgroup><tbody>
</tbody>

I looking to extract the column (in this case the date) with the first non-zero occurrence for each label:

Output should be like this:

Row Labels	Date
ABB	1/10/2016
ABC	12/27/2015
XYZ	1/10/2016
ZMN	2/14/2016

<tbody>
</tbody>

Any thoughts/suggestions on how to do this?

 

[tybaltlives]

Here is a way. The formula may be copied downwards but may need to be adjusted for your data ranges.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Row Labels	12/27/2015	1/3/2016	1/10/2016	1/17/2016	1/24/2016	1/31/2016	2/7/2016	2/14/2016	2/21/2016	2/28/2016	3/6/2016
2	ABB	0	0	310	400	500	22	2223	2232	323	6565	655
3	ABC	777	777	777	777	777	787	798	9899	98982	98956	98956
4	XYZ	0	0	328	328	328	328	428	5	878	98789	64654
5	ZMN	0	0	0	0	0	0	0	500	500
6
7	Row Labels	Date
8	ABB	1/10/2016
9	ABC	12/27/2015
10	XYZ	1/10/2016
11	ZMN	2/14/2016
Sheet8

Cell Formulas
Range	Formula
B8	=INDEX(B$1:L$1,IF(TYPE(MATCH(0,B2:L2,1)+1)=16,1,MATCH(0,B2:L2,1)+1))

Named Ranges
Name	Refers To	Cells
X	=trendlines!$A$7:$A$26
Y	=trendlines!$B$7:$B$26

 

[tybaltlives]

In my above post, ignore the workbook defined names. They are part of another project I am working on.

 

[bosco_yip]

Using tybaltlives's table in post#2, in B8, formula copy down :

=INDEX(B$1:L$1,INDEX(MATCH(TRUE,OFFSET(A$1,MATCH(A8,A$2:A$5,0),1,1,11)>0,0),0))

Regards

 

[bosco_yip]

Some clarification of the formula used

1] If criteria name are same as the source table order (as post #.1 example):

In B8, formula copy down :

=INDEX(B$1:L$1,INDEX(MATCH(TRUE,B2:L2>0,0),0))

2] If criteria name are not same as the source table order :

In B8, formula copy down :

=INDEX(B$1:L$1,INDEX(MATCH(TRUE,OFFSET(A$1,MATCH(A8,A$2:A$5,0),1,1,11)>0,0),0))

Regards

 

[karthik104]

I'll try it out. Thank you for the quick response.