Finding second positive number in row

[Tdorman]

The formula I used below allows me to find the first positive number in the row. I cant seem to get it to help me find the second positive number after. I would also need it to find the third, fourth, fifth, and so on.

=INDEX($J$8:$S$8,MATCH(1,INDEX(--($J$8:$S$8>0),1,0),0))

 

[Fluff]

Not sure what you mean as the formula has nothing to do with years.

 

[Tdorman]

So for 2009 we used the 1,000,000 and the 5,000,000. In 2010 there is also a negative amount and the formula would pull the same 1 and 5 million over to that year as well. Is there a way to have it only put that amount once

 

[Fluff]

But the formula col J will return all +ve numbers, so there is nothing for any of the other columns.

 

[Tdorman]

CBT-100U Workpapers 6-5TEST.xlsm
	J	K	L	M	N	O	P	Q	R	S
5
6	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
7
8	(33,688,598)	(29,587,046)	1,000,000	(28,027,007)	(15,752,937)	(28,579,386)	5,000,000	(50,355,149)	(43,146,535)	(40,980,658)
9	1,000,000	1,000,000	1,000,000	1,000,000	1,000,000	1,000,000	1,000,000	1,000,000	1,000,000	1,000,000
10	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000
11
12
13
14
NOL-TEMPLATE

Cell Formulas
Range	Formula
Q8:S8,M8:O8,J8:K8	J8	=-(IFERROR(INDEX('DATA 500U-P-22'!$G$12:$G$3000,MATCH(1,INDEX(('DATA 500U-P-22'!$C$12:$C$3000=$A$6)*(YEAR('DATA 500U-P-22'!$F$12:$F$2000)=J6),0),0),0),0))
J9:S10	J9	=IFERROR(INDEX($J$8:$S$8,AGGREGATE(15,6,(COLUMN($J$8:$S$8)-COLUMN($J$8)+1)/($J$8:$S$8>0),ROWS(J$9:J9))),"")

When the formula is used in subsequent years it pulls the first positive number, even though it has already been used. I wasn't sure if there was a way to use this formula to pick up if its already been used previously.

 

[Fluff]

The formula does exactly what you asked for, all you need to do is drag it down further & it will show all positive values. I don't understand why you want to put the exact same formula in multiple columns.

 

[Tdorman]

What needs to happen is that the positive amounts on row 8 need to offset the negative amounts. What was done for column J needs to be done for column k and so on. However, the amounts that are used in previous columns cant also be used in the next set of columns. Meaning the amounts listed in column J that were pulled from row 8 cant then be used in column K and on. The 1 and 5 million amounts in J9 and J10 cant be in K9 and K10

 

[awoohaw]

What needs to happen is that the positive amounts on row 8 need to offset the negative amounts. What was done for column J needs to be done for column k and so on. However, the amounts that are used in previous columns cant also be used in the next set of columns. Meaning the amounts listed in column J that were pulled from row 8 cant then be used in column K and on. The 1 and 5 million amounts in J9 and J10 cant be in K9 and K10

I think you need to show a flow chart or a worksheet that really shows the process you want this to go through.

 

[Fluff]

How about

Fluff.xlsm
	J	K	L	M	N	O	P	Q	R	S
5
6	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
7
8	-33,688,598	-29,587,046	1,000,000	-28,027,007	-15,752,937	-28,579,386	5,000,000	50,355,149	-43,146,535	-40,980,658
9	1,000,000	50,355,149
10	5,000,000
Data

Cell Formulas
Range	Formula
J9:J10	J9	=IFERROR(INDEX($J$8:$S$8,AGGREGATE(15,6,(COLUMN($J$8:$S$8)-COLUMN($J$8)+1)/($J$8:$S$8>0),ROWS(J$9:J9))),"")
K9:S10	K9	=IFERROR(INDEX($J$8:$S$8,AGGREGATE(15,6,(COLUMN($J$8:$S$8)-COLUMN($J$8)+1)/($J$8:$S$8>0),ROWS(J$9:J9)+COUNT($J$9:J$12))),"")

 

[Tdorman]

It should look like this with the current amounts

CBT-100U Workpapers 6-5TEST.xlsm
	J	K	L	M	N	O	P	Q	R	S
5
6	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
7
8	(33,688,598)	(29,587,046)	1,000,000	(28,027,007)	(15,752,937)	(28,579,386)	5,000,000	(50,355,149)	(43,146,535)	(40,980,658)
9	1,000,000		(1,000,000)
10	5,000,000						(5,000,000)
11
12
13
14
15
16
17
18
NOL-TEMPLATE

Cell Formulas
Range	Formula
J8:K8,Q8:S8,M8:O8	K8	=-(IFERROR(INDEX('DATA 500U-P-22'!$G$12:$G$3000,MATCH(1,INDEX(('DATA 500U-P-22'!$C$12:$C$3000=$A$6)*(YEAR('DATA 500U-P-22'!$F$12:$F$2000)=K6),0),0),0),0))
J9:J10	J9	=IFERROR(INDEX($J$8:$S$8,AGGREGATE(15,6,(COLUMN($J$8:$S$8)-COLUMN($J$8)+1)/($J$8:$S$8>0),ROWS(J$9:J9))),"")

Something alittle more detailed

CBT-100U Workpapers 6-5TEST.xlsm
	J	K	L	M	N	O	P	Q	R	S
5
6	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
7
8	(33,688,598)	(29,587,046)	1,000,000	32,000,000	(15,752,937)	(28,579,386)	5,000,000	(50,355,149)	(43,146,535)	(40,980,658)
9	1,000,000		(1,000,000)
10	32,000,000			(32,000,000)
11	688,598						(688,598)
12		4,311,402					(4,311,402)
13
14
15
NOL-TEMPLATE

Cell Formulas
Range	Formula
J8:K8,Q8:S8,N8:O8	K8	=-(IFERROR(INDEX('DATA 500U-P-22'!$G$12:$G$3000,MATCH(1,INDEX(('DATA 500U-P-22'!$C$12:$C$3000=$A$6)*(YEAR('DATA 500U-P-22'!$F$12:$F$2000)=K6),0),0),0),0))
J9:J10	J9	=IFERROR(INDEX($J$8:$S$8,AGGREGATE(15,6,(COLUMN($J$8:$S$8)-COLUMN($J$8)+1)/($J$8:$S$8>0),ROWS(J$9:J9))),"")

 

[Fluff]

I'm sorry, but I haven't got a clue what you are asking for, but as this has nothing to do with your original question, you need to atrt a new thread. Thanks