Return non-zero data and associated row header from an array

[MrNal]

I will be grateful for help with the following

I have an array in the following manner:

31/01/2018 28/02/2018 31/03/2018 30/04/2018 31/05/2018 30/06/2018......
First 120 0 145 0 0 20
2nd 0 10 0 6 0 0
.
.

Using a formula I will like to return any non-zero value with their associated date e.g. for First I will like to return the following

120 31/01/2018
145 31/03/2018
20 30/06/2018

The numbers in the array are derived by formula and I can't use traditional index/match because I don't know the date that the non-zero will occur beforehand. I know the column headers that will have the non zero- values

Will be grateful for help with formula that can achieve what I want.

Many thanks.

 

[Domenic]

For this example, let's assume that A1:G3 contains the data. For the First, try...

I2:

=COUNTIF(B2:G2,">0")

J2, copied down:

=IF(K2<>"",INDEX($B$2:$G$2,MATCH(K2,$B$1:$G$1,0)),"")

K2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(K$2:K2)<=$I$2,INDEX($B$1:$G$1,SMALL(IF($B$2:$G$2>0,COLUMN($B$2:$G$2)-COLUMN($B$2)+1),ROWS(K$2:K2))),"")

Note that the formulas in Column J will only return an empty string ("") until the formulas in Column K have been entered.

Hope this helps!

 

[Peter_SSs]

A slightly different version.
I1 and K1 manually entered.
Formulas in I2:J2 copied down for as many rows as there are dates in the data section (so down to row 7 for this sample)
Then copy I2:J7 to K2 etc

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1		31/01/2018	28/02/2018	31/03/2018	30/04/2018	31/05/2018	30/06/2018		First		2nd
2	First	120	0	145	0	0	20		120	31/01/2018	10	28/02/2018
3	2nd	0	10	0	6	0	0		145	31/03/2018	6	30/04/2018
4	.								20	30/06/2018
5	.
Non-Zero

 

[MrNal]

Many thanks for taking the time to come up with potential solutions.

Although both suggestions do work to a point, they don't quite work well for my purpose. I need the output to be in be in single colum as I need to then manipulate the data for another purpose. The real struggle is to get the date as I can get the row reference (first 2nd easily). It will be then easy to get data in the array (120,145,20 etc) as I can use index/match to retrieve this once I have a formula return the date.

Will be grateful for suggestions.

 

[Peter_SSs]

I need the output to be in be in single colum ... The real struggle is to get the date as I can get the row reference ...

Is this what you want then?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		31/01/2018	28/02/2018	31/03/2018	30/04/2018	31/05/2018	30/06/2018
2	First	120	0	145	0	0	20			31/01/2018
3	2nd	0	10	0	6	0	0			31/03/2018
4	3rd	0	8	100	0	22	0			30/06/2018
5										28/02/2018
6										30/04/2018
7										28/02/2018
8										31/03/2018
9										31/05/2018
10
Non-Zero

 

[MrNal]

Works exactly the way I want! Thank you very much. Grateful indeed.
Non-Zero

	A	B	C	D	E	F	G	H	I	J
First
2nd
3rd

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:45px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:23px;"><col style="width:22px;"><col style="width:83px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]31/01/2018[/TD]
[TD="align: right"]28/02/2018[/TD]
[TD="align: right"]31/03/2018[/TD]
[TD="align: right"]30/04/2018[/TD]
[TD="align: right"]31/05/2018[/TD]
[TD="align: right"]30/06/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]120[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"]31/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]31/03/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]30/06/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]28/02/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]30/04/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]28/02/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]31/03/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]31/05/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula J2 =IFERROR(INDEX(B$1:G$1,RIGHT(AGGREGATE(15,6,(ROW(B2:G4)*10^6+COLUMN(B$1:G$1)-COLUMN(B$1)+1)/(B$2:G$4<>0),ROWS(J$2:J2)),6)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4 [/QUOTE]

 

[Peter_SSs]

Works exactly the way I want! Thank you very much. Grateful indeed.

You are very welcome.