Get last data formula from column greater is than 1

motilulla · Mar 9, 2014

Hi everyone,

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

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

4

JR20

3

1

2

5

JR21

1

3

6

JR22

2

1

7

JR23

1

8

JR24

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

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

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

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

4

JR20

3

1

2

5

JR21

1

3

6

JR22

2

1

7

JR23

1

8

JR24

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

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

8

7

6

12

14

22

2

3

6

Data Result

Thanks And Regards,
Moti

Aladin Akyurek · Mar 9, 2014

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

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

In that case, one of...

Rich (BB code):

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

Rich (BB code):

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

markmzz · Mar 9, 2014

You can try this too:

Code:

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

Markmzz

motilulla · Mar 9, 2014

Thank you markmzz, for your kind help your both formula are resulting as required

Code:

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

Code:

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

Thank you Aladin Akyurek , for your kind help your one of formula below is giving a perfect result

Code:

[FONT=lucida console][SIZE=2]=IF(COUNTIF(B2:B20,">1"),IF(LOOKUP(9.99999999999999E+307,B2:B20)>1,
  LOOKUP(9.99999999999999E+307,B2:B20),""),"")[/SIZE][/FONT]

Thank you mole999, for your kind help problem is resolved

I appreciate to all of you for your help and time

Regards,
Moti

motilulla · Mar 9, 2014

Hi, Well after checking thoroughly with real data occurred one more problem 

Really, I need to get grater than 1 value only from the last used row in this case below last row is A16 

If data are find in cells F6, F7, F8 also get last column result grater than 1 in cell F22

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

4

JR20

3

1

2

5

JR21

1

3

6

JR22

2

1

7

JR23

2

1

8

JR24

1

3

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

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

8

7

6

12

14

22

2

3

2

3

6

Data Result

Can it be eliminated?
And get result as shown below

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

13

JR29

3

14

JR30

1

4

15

JR31

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

8

7

6

12

14

22

2

3

6

Data Result

Thanks And Regards,
Moti

Aladin Akyurek · Mar 9, 2014

motilulla said:
Hi, Well after checking thoroughly with real data occurred one more problem

Really, I need to get grater than 1 value only from the last used row in this case below last row is A16

If data are find in cells F6, F7, F8 also get last column result grater than 1 in cell F22

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 1
7 JR23 2 1 1
8 JR24 1 3 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 3 2 2 3 3 6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Data Result

Can it be eliminated?
And get result as shown below

A B C D E F G H I J K L M N O
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 3 6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Data Result

Thanks And Regards,
Moti

Is the row of A16 given, that is, is it always that row?

markmzz · Mar 9, 2014

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		1
JR23					2			1			1
JR24				1	3	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			6
*****	****	****	****	****	****	****	****	****	****	****	****	****	****	****

Formula

Code:

In B22

=IF(INDEX(B$2:B$20,MAX(INDEX(($B$2:$O$20<>"")*(ROW($B$2:$B$20)-ROW($B$2)+1),)))>1,
INDEX(B$2:B$20,MAX(INDEX(($B$2:$O$20<>"")*(ROW($B$2:$B$20)-ROW($B$2)+1),))),"")

And copy to the right.

Markmzz

motilulla · Mar 9, 2014

markmzz said:
Maybe this: Formula

Code:

In B22

=IF(INDEX(B$2:B$20,MAX(INDEX(($B$2:$O$20<>"")*(ROW($B$2:$B$20)-ROW($B$2)+1),)))>1,
INDEX(B$2:B$20,MAX(INDEX(($B$2:$O$20<>"")*(ROW($B$2:$B$20)-ROW($B$2)+1),))),"")

And copy to the right.

Thank you markmzz, for your kind support your above formula is working like a magic It is giving 100% perfect result of the last entered values when it is grater than 1

I appreciate your help and time 

Regards,
Moti

motilulla · Mar 9, 2014

Aladin Akyurek said:
Is the row of A16 given, that is, is it always that row?

Aladin Akyurek , no row keep changing from 1 to 20 markmzz’s formula is giving perfect result. Find the last row result when it is grater than 1 correctly

Thank you for your interest and help 

Regards, Moti

Aladin Akyurek · Mar 10, 2014

motilulla said:
Aladin Akyurek , no row keep changing from 1 to 20 markmzz’s formula is giving perfect result. Find the last row result when it is grater than 1 correctly

Thank you for your interest and help

Regards, Moti

If the row the following formula...

A22, control+shift+enter, not just enter:

Rich (BB code):

=MAX(IF(ISNUMBER(B2:O20),ROW(B2:O20)))

picks out is the right one...

B22, just enter and copy across:

Rich (BB code):

=IFERROR(IF(1/(LOOKUP(9.99999999999999E+307,B2:INDEX(B:B,$A$22))>1),
  LOOKUP(9.99999999999999E+307,B2:INDEX(B:B,$A$22))),"")

if you are on a post-2003 system. Otherwise:

Rich (BB code):

=IF(COUNTIF(B2:INDEX(B:B,$A$22),">1"),
  IF(LOOKUP(9.99999999999999E+307,B2:INDEX(B:B,$A$22))>1,
  LOOKUP(9.99999999999999E+307,B2:INDEX(B:B,$A$22)),""),"")

Note 1. The foregoing set up is efficient.
Note 2. You can define BigNum as referreing to: =9.99999999999999E+307 and replace this number in the formulas with BigNum, if so desired.

motilulla · Mar 10, 2014

Hi Aladin Akyurek, 

Enter markmzz’s formula shown below in the cell B22 and copy across O22

Code:

=IF(INDEX(B$2:B$20,MAX(INDEX(($B$2:$O$20<>"")*(ROW($B$2:$B$20)-ROW($B$2)+1),)))>1,INDEX(B$2:B$20,MAX(INDEX(($B$2:$O$20<>"")*(ROW($B$2:$B$20)-ROW($B$2)+1),))),"")

1-Now enter your 1^st formula shown below in cell A24, control+shift+enter, not just enter

Code:

=MAX(IF(ISNUMBER(B2:O20),ROW(B2:O20)))

2-Now enter your 2nd formula shown below in cell B24 and copy across O24

Code:

=IF(COUNTIF(B2:INDEX(B:B,$A$24),">1"),  IF(LOOKUP(9.99999999999999E+307,B2:INDEX(B:B,$A$24))>1,  LOOKUP(9.99999999999999E+307,B2:INDEX(B:B,$A$24)),""),"")

Please take a look sheet shown below your formula track in the Cell N24 number 3 which is not find in the row 16

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

4

JR20

3

1

2

5

JR21

1

3

6

JR22

2

1

7

JR23

1

8

JR24

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

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

8

7

6

12

14

22

Resulta

2

3

6

23

24

16

2

3

6

Data

Now for example insert any number in the row 17 cells B17 O17 (I have insert 4 in L17) and see the result below markmzz’s formula result show only number 4
But your formula result shows all and change 3 to 4 in cell L24

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

4

JR20

3

1

2

5

JR21

1

3

6

JR22

2

1

7

JR23

1

8

JR24

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

2

5

16

JR32

1

2

1

2

1

2

3

6

17

JR33

4

18

JR34

19

JR35

20

JR36

21

4

6

14

12

1

3

7

9

8

7

6

12

14

22

Resulta

4

23

24

17

2

4

3

6

Data

So far I find markmzz’s formula is giving correct result for my need

I appreciate you help for looking my request to solve it, and also hope now I have explained my require correctly

Regards,
Moti

Get last data formula from column greater is than 1

motilulla

Well-known Member

Aladin Akyurek

MrExcel MVP

Excel Facts

markmzz

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

Aladin Akyurek

MrExcel MVP

markmzz

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

Aladin Akyurek

MrExcel MVP

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

Get last data formula from column greater is than 1

Well-known Member

MrExcel MVP

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock