Formula to bring Last Values of the multiple columns

[Daiwik]

Hi,

I am looking for a suitable trick to get last values of the multiple columns, as shown in the table below.
I am using a following formula (which is just half being a trial) which is working fine but it would be too lengthy. Just want to get an appropriate one. Thanks you!

=IF(B30<>"",B30,IF(B29<>"",B29,IF(B28<>"",B28,IF(B27<>"",B27,IF(B26<>"",B26,IF(B25<>"",B25,IF(B24<>"",B24,IF(B23<>"",B23,IF(B22<>"",B22,IF(B21<>"",B21,""))))))))))

Date	List 1	List 2	List 3	List 4	List 5	List 6	List 7	List 8	List 9	List 10
01-02-2016	45	57	33	48	37	61	49	46	38	50
02-02-2016	44	57	35	44	45	55	30	56	57	47
03-02-2016	36	44	55	34	62	31	52	60	30	44
04-02-2016	44	37	61	32	61	45	35	43	46	52
05-02-2016	44	37	61	32	61	45	35	43	46	52
06-02-2016	34	48	47	62	40	33	62	58	51	31
07-02-2016	23	31	57	37	49	39	59	30	31	40
08-02-2016	43	31	50	58	46	30	35	45	48	54
09-02-2016	22	39	44	45	41	33	62	33	60	60
10-02-2016	29	49	35	58	40	35	57	31	36	37
11-02-2016	43	52	61	57	61	40	46	38	34	40
12-02-2016	42	50	62	56	58	50	50	57	46	32
13-02-2016	29	36	39	49	58	41	34	46	45	44
14-02-2016	38	41	31	33	34	35	35	30	41	58
15-02-2016	45	54	47	35	42	43	61	45	60	40
16-02-2016	50	43	58	35	32	50	45	51	58	40
17-02-2016	30	56	33	55	49	49	36	34	53	41
18-02-2016	33	35	49	62	34	60	41	59	58	57
19-02-2016	24	31	60	52	43	49	33	42	60	59
20-02-2016	39	34	37	38	38	43	54	59	51	58
21-02-2016	45	33	44	48	51	32	48	52	59	40
22-02-2016	33	47	52	37		59	48	34	47
23-02-2016	31	45	53	59			34	36	48
24-02-2016	49	51	54	40			60		31
25-02-2016	30	50		53					57
26-02-2016				42
27-02-2016
28-02-2016
29-02-2016
Last Figure.	30	50	54	42	51	59	60	36	57	40

<colgroup><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[BarryL]

try

=LOOKUP(MAX(B1:B30)+1,B1:B30)

 

[Daiwik]

Yey!

Thanks BarryL.

=LOOKUP(MAX(B1:B30)+1,B1:B30) worked great !!

 

[Daiwik]

Hi BarryL,

just want to know why did you chose MAX formula with +1, What's the concept behind it? Thanks in advance.

 

[michaelsmith559]

Here is a macro to do it. I assume you want last figure at the end of your date column. Set up your sheet like the first screenshot, run the macro. Second Screenshot shows the results.

Set up Sheet:

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K
1	Date	List 1	List 2	List 3	List 4	List 5	List 6	List 7	List 8	List 9	List 10
2	1/2/2016	45	57	33	48	37	61	49	46	38	50
3	2/2/2016	44	57	35	44	45	55	30	56	57	47
4	3/2/2016	36	44	55	34	62	31	52	60	30	44
5	4/2/2016	44	37	61	32	61	45	35	43	46	52
6	5/2/2016	44	37	61	32	61	45	35	43	46	52
7	6/2/2016	34	48	47	62	40	33	62	58	51	31
8	7/2/2016	23	31	57	37	49	39	59	30	31	40
9	8/2/2016	43	31	50	58	46	30	35	45	48	54
10	9/2/2016	22	39	44	45	41	33	62	33	60	60
11	10/2/2016	29	49	35	58	40	35	57	31	36	37
12	11/2/2016	43	52	61	57	61	40	46	38	34	40
13	12/2/2016	42	50	62	56	58	50	50	57	46	32
14	13-02-2016	29	36	39	49	58	41	34	46	45	44
15	14-02-2016	38	41	31	33	34	35	35	30	41	58
16	15-02-2016	45	54	47	35	42	43	61	45	60	40
17	16-02-2016	50	43	58	35	32	50	45	51	58	40
18	17-02-2016	30	56	33	55	49	49	36	34	53	41
19	18-02-2016	33	35	49	62	34	60	41	59	58	57
20	19-02-2016	24	31	60	52	43	49	33	42	60	59
21	20-02-2016	39	34	37	38	38	43	54	59	51	58
22	21-02-2016	45	33	44	48	51	32	48	52	59	40
23	22-02-2016	33	47	52	37		59	48	34	47
24	23-02-2016	31	45	53	59			34	36	48
25	24-02-2016	49	51	54	40			60		31
26	25-02-2016	30	50		53					57
27	26-02-2016				42
28	27-02-2016
29	28-02-2016
30	29-02-2016
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Macro:

Sub Get_Last_Values_Across_Columns()
Dim i As Long, c As Long
Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0).Value = "Last Figure"
lastColumn = Range("B1").End(xlToRight).Column
For c = 2 To lastColumn
lastRow = Cells(2, c).End(xlDown).Row
i = Cells(1, 1).End(xlDown).Row
Cells(i, c).Value = Cells(lastRow, c).Value
Next c
End Sub

If using excel 2007 or greater, change the Sheets("Sheet1").Range("A65536") to Sheets("Sheet1").Range("A1048576")

Results of running macro:

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K
1	Date	List 1	List 2	List 3	List 4	List 5	List 6	List 7	List 8	List 9	List 10
2	1/2/2016	45	57	33	48	37	61	49	46	38	50
3	2/2/2016	44	57	35	44	45	55	30	56	57	47
4	3/2/2016	36	44	55	34	62	31	52	60	30	44
5	4/2/2016	44	37	61	32	61	45	35	43	46	52
6	5/2/2016	44	37	61	32	61	45	35	43	46	52
7	6/2/2016	34	48	47	62	40	33	62	58	51	31
8	7/2/2016	23	31	57	37	49	39	59	30	31	40
9	8/2/2016	43	31	50	58	46	30	35	45	48	54
10	9/2/2016	22	39	44	45	41	33	62	33	60	60
11	10/2/2016	29	49	35	58	40	35	57	31	36	37
12	11/2/2016	43	52	61	57	61	40	46	38	34	40
13	12/2/2016	42	50	62	56	58	50	50	57	46	32
14	13-02-2016	29	36	39	49	58	41	34	46	45	44
15	14-02-2016	38	41	31	33	34	35	35	30	41	58
16	15-02-2016	45	54	47	35	42	43	61	45	60	40
17	16-02-2016	50	43	58	35	32	50	45	51	58	40
18	17-02-2016	30	56	33	55	49	49	36	34	53	41
19	18-02-2016	33	35	49	62	34	60	41	59	58	57
20	19-02-2016	24	31	60	52	43	49	33	42	60	59
21	20-02-2016	39	34	37	38	38	43	54	59	51	58
22	21-02-2016	45	33	44	48	51	32	48	52	59	40
23	22-02-2016	33	47	52	37		59	48	34	47
24	23-02-2016	31	45	53	59			34	36	48
25	24-02-2016	49	51	54	40			60		31
26	25-02-2016	30	50		53					57
27	26-02-2016				42
28	27-02-2016
29	28-02-2016
30	29-02-2016
31	Last Figure	30	50	54	42	51	59	60	36	57	40
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[michaelsmith559]

You also may need to change Sheet("Sheet1") to your appropriate sheet name.

 

[Aladin Akyurek]

Faster option, which is also robust against interspersed error values...

=LOOKUP(9.99999999999999E+307,B1:B30)