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

karthik104

New Member
Joined
Dec 4, 2015
Messages
7
Hi,

I have data in a pivot table:

Row Labels
12/27/2015
1/3/2016
1/10/2016
1/17/20161/24/20161/31/20162/7/20162/14/2016
2/21/20162/28/20163/6/2016
ABB
0
0310
400
500
22
2223
2232
323
6565
655
ABC
777
777
777
777
777
787
7989899989829895698956
XYZ
0
0
328
328
328
328
428
58789878964654
ZMN
0
000000500
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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
Here is a way. The formula may be copied downwards but may need to be adjusted for your data ranges.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">Row Labels</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">12/27/2015</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">1/3/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">1/10/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">1/17/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">1/24/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">1/31/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">2/7/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">2/14/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">2/21/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">2/28/2016</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">3/6/2016</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">ABB</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">310</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">400</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">500</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">22</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">2223</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">2232</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">323</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">6565</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">655</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">ABC</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">777</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">777</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">777</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">777</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">777</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">787</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">798</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">9899</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">98982</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">98956</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">98956</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">XYZ</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">328</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">328</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">328</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">328</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">428</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">5</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">878</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">98789</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">64654</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">ZMN</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">0</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">500</td><td style="text-align: center;color: #574123;background-color: #FFFFFF;;">500</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Row Labels</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ABB</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1/10/2016</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ABC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/27/2015</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">XYZ</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1/10/2016</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ZMN</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2/14/2016</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet8</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=INDEX(<font color="Blue">B$1:L$1,IF(<font color="Red">TYPE(<font color="Green">MATCH(<font color="Purple">0,B2:L2,1</font>)+1</font>)=16,1,MATCH(<font color="Green">0,B2:L2,1</font>)+1</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">X</th><td style="text-align:left">=trendlines!$A$7:$A$26</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Y</th><td style="text-align:left">=trendlines!$B$7:$B$26</td></tr></tbody></table></td></tr></table><br />
 

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
In my above post, ignore the workbook defined names. They are part of another project I am working on.
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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