Get last data formula from column greater is than 1

[motilulla]

Hi everyone,</SPAN></SPAN>

In the below sheet I have formulas and data fill in cells: B2:O20 and cells B21:O21 data comes from another source.</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	JR18				1
3	JR19	1			2						1			1
4	JR20				3				1					2
5	JR21	1												3
6	JR22	2		1
7	JR23								1			1
8	JR24				1		1		2
9	JR25		1		2				3
10	JR26				3				4
11	JR27	1							5						1
12	JR28								6	1					2
13	JR29														3
14	JR30											1			4
15	JR31		1				1				1	2			5
16	JR32	1	2		1		2		1		2	3			6
17	JR33
18	JR34
19	JR35
20	JR36
21		4	6	14	12	1	3	7	9	9	8	7	6	12	14
Data

I need formula for in cells B22:O22 that can get last data from each column is greater than 1 as shown below</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	JR18				1
3	JR19	1			2						1			1
4	JR20				3				1					2
5	JR21	1												3
6	JR22	2		1
7	JR23								1			1
8	JR24				1		1		2
9	JR25		1		2				3
10	JR26				3				4
11	JR27	1							5						1
12	JR28								6	1					2
13	JR29														3
14	JR30											1			4
15	JR31		1				1				1	2			5
16	JR32	1	2		1		2		1		2	3			6
17	JR33
18	JR34
19	JR35
20	JR36
21		4	6	14	12	1	3	7	9	9	8	7	6	12	14
22			2				2				2	3			6
Data Result

Thanks And Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Try this

=IF(ISERROR(LOOKUP(10^100,$B$2:$B$50)<=1),"",LOOKUP(10^100,$B$2:$B$50))

Thank you mole999, Now does not give any error but result are not correct</SPAN>

=IF(ISERROR(LOOKUP(10^100,B$2:B$20)<=1),"",LOOKUP(10^100,B$2:B$20))

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
21		4	6	14	12	1	3	7	9	9	8	7	6	12	14
22		1	2	1	1		2		1	1	2	3		3	6

<TBODY>
</TBODY>

Data Result​

Regards,
Moti
</SPAN>

 

[Aladin Akyurek]

Thank you mole999, Now does not give any error but result are not correct

=IF(ISERROR(LOOKUP(10^100,B$2:B$20)<=1),"",LOOKUP(10^100,B$2:B$20))

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
21		4	6	14	12	1	3	7	9	9	8	7	6	12	14
22		1	2	1	1		2		1	1	2	3		3	6

<tbody>
</tbody>

Data Result​

Regards,
Moti


Regards,
Moti

Would you also try

http://www.mrexcel.com/forum/excel-...mula-column-greater-than-1-a.html#post3740978

if you will?

 

[mole999]

I refer the honourable gentleman to my tag line. I'm never going to be able to absorb all the nuances of VBA / Excel

 

[motilulla]

Thank you mole999, as per my opening post I am getting correct result with your formula posted in the post#8, only giving error when column is empty

</SPAN></SPAN>I will go with that </SPAN></SPAN>

I appreciate you kind help</SPAN></SPAN>

Regards,
Moti</SPAN></SPAN>

 

[mole999]

did you try aladins

=IF(COUNTIF(B2:B20,">1"),LOOKUP(9.99999999999999E+307,
1/(ISNUMBER(B2:B20)*(B2:B20>1)),B2:B20),"")

?

 

[motilulla]

did you try aladins

=IF(COUNTIF(B2:B20,">1"),LOOKUP(9.99999999999999E+307,
1/(ISNUMBER(B2:B20)*(B2:B20>1)),B2:B20),"")

?

Yes mole999, but it also not resulting as I want please see the results below</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
26		4	6	14	12	1	3	7	9	9	8	7	6	12	14
27		2	2		3		2		6		2	3		3	6
Data Result

Regards,
Moti</SPAN></SPAN>

 

[mole999]

Ah I see, you only want the LAST value of the row and not the MAX of the lines above, hence why mine sort of works. I would suggest conditional formatting to hide my errrors, but I'm sure there is code that does that

 

[Aladin Akyurek]

Yes mole999, but it also not resulting as I want please see the results below

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
26		4	6	14	12	1	3	7	9	9	8	7	6	12	14
27		2	2		3		2		6		2	3		3	6

<tbody>
</tbody>

Data Result​

Regards,
Moti

B27 becomes 2 because the value of B6 (that is, JR22) is 2. Why is that wrong?

 

[mole999]

I think its because it should only be evaluating JR32 for all returns (thats my understanding)

 

[markmzz]

Maybe this:

Layout

Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14
JR18				1
JR19	1			2						1			1
JR20				3				1					2
JR21	1												3
JR22	2		1
JR23								1			1
JR24				1		1		2
JR25		1		2				3
JR26				3				4
JR27	1							5						1
JR28								6	1					2
JR29														3
JR30											1			4
JR31		1				1				1	2			5
JR32	1	2		1		2		1		2	3			6
JR33
JR34
JR35
JR36
	4	6	14	12	1	3	7	9	9	8	7	6	12	14
		2				2				2	3		3	6
*****	****	****	****	****	****	****	****	****	****	****	****	****	****	****

<tbody>
</tbody>

Formula

In B22

=IF(COUNT(B$2:B$20),IF(LOOKUP(99^99,B$2:B$20)>1,LOOKUP(99^99,B$2:B$20),""),"")

And copy to the right.

Markmzz