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 10, 2014

motilulla said:
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

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 Resulta 2 2 2 3 6
23
24 16 2 2 2 3 3 6

<tbody>
</tbody>
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

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 4
18 JR34
19 JR35
20 JR36
21 4 6 14 12 1 3 7 9 9 8 7 6 12 14
22 Resulta 4
23
24 17 2 2 2 4 3 6

<tbody>
</tbody>
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

That's the exactly the problem that you have: What row must be considered as the last row for evaluation? Delete for example some initial values from row 16. Do you still have the correct results?

motilulla · Mar 10, 2014

Aladin Akyurek said:
What row must be considered as the last row for evaluation?

Hi Aladin Akyurek

The evaluation row? Must be considered the last row in which data are found 
In the example above last evaluation row will be considered 17 because 4 is find in that row.

Aladin Akyurek said:
Delete for example some initial values from row 16. Do you still have the correct results?

No, Did you try markmzz’s formula?

Regards,
Moti

Aladin Akyurek · Mar 10, 2014

motilulla said:
Hi Aladin Akyurek

The evaluation row? Must be considered the last row in which data are found
In the example above last evaluation row will be considered 17 because 4 is find in that row.

No, Did you try markmzz’s formula?

Regards,
Moti

I think we have a communication problem here... The formula in A22 I posted looks for the highest row number and the fromula in B22 copied across takes up the A22 result as the row to evalutate. If that's all wrong, I have nothing to add...

motilulla · Mar 10, 2014

Aladin Akyurek said:
I think we have a communication problem here... The formula in A22 I posted looks for the highest row number and the fromula in B22 copied across takes up the A22 result as the row to evalutate. If that's all wrong, I have nothing to add...

Hi Aladin Akyurek, 

I think You are very right I got correct result with your formulas as per my opening post. 

But after I found another problem when I checked with my original data. And start confusion. 

I am so sorry about that

Regards,
Moti

Aladin Akyurek · Mar 10, 2014

motilulla said:
Hi Aladin Akyurek,

I think You are very right I got correct result with your formulas as per my opening post.

But after I found another problem when I checked with my original data. And start confusion.

I am so sorry about that

Regards,
Moti

No problem. If you sorted it out, I'm happy with that.

markmzz · Mar 10, 2014

motilulla said:
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

Hi Moti,

I'm glad to help and thank you for the feedback.

Markmzz

billszysz · Mar 10, 2014

Hi motilulla,

A little shorter solution I propose

in A24 Aladin's formula for MAX row

For B24

=IF(INDIRECT(ADDRESS($A$24,COLUMN()))>1,INDIRECT(ADDRESS($A$24,COLUMN())),"")

and copy right

Greetings

)

Aladin Akyurek · Mar 11, 2014

billszysz said:
Hi motilulla,

A little shorter solution I propose
in A24 Aladin's formula for MAX row

For B24

=IF(INDIRECT(ADDRESS($A$24,COLUMN()))>1,INDIRECT(ADDRESS($A$24,COLUMN())),"")

and copy right

Greetings )

That would not be very efficient for it contains too many volatile functions.

Once we have the MAX row in A22 (or A24 for that matter) and that row must be checked for entries > 1, the following is cheaper:

B22, copied across:

=IF(INDEX(B:B,$A$22)>1,INDEX(B:B,$A$22),"")

billszysz · Mar 11, 2014

Hi Aladin,

I absolutely agree with you. It was only my first (and fast) impresion of this topic.
Your formula is much better

)
Greetings.

Get last data formula from column greater is than 1

motilulla

Well-known Member

Aladin Akyurek

MrExcel MVP

Excel Facts

motilulla

Well-known Member

Aladin Akyurek

MrExcel MVP

motilulla

Well-known Member

Aladin Akyurek

MrExcel MVP

markmzz

MrExcel MVP

billszysz

Active Member

Aladin Akyurek

MrExcel MVP

billszysz

Active 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

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Active Member

MrExcel MVP

Active 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