Formula to bring Last Values of the multiple columns

Daiwik

New Member
Joined
Jan 12, 2016
Messages
15
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,""))))))))))

DateList 1List 2List 3List 4List 5List 6List 7List 8List 9List 10
01-02-201645573348376149463850
02-02-201644573544455530565747
03-02-201636445534623152603044
04-02-201644376132614535434652
05-02-201644376132614535434652
06-02-201634484762403362585131
07-02-201623315737493959303140
08-02-201643315058463035454854
09-02-201622394445413362336060
10-02-201629493558403557313637
11-02-201643526157614046383440
12-02-201642506256585050574632
13-02-201629363949584134464544
14-02-201638413133343535304158
15-02-201645544735424361456040
16-02-201650435835325045515840
17-02-201630563355494936345341
18-02-201633354962346041595857
19-02-201624316052434933426059
20-02-201639343738384354595158
21-02-201645334448513248525940
22-02-201633475237 59483447
23-02-201631455359 343648
24-02-201649515440 60 31
25-02-20163050 53 57
26-02-2016 42
27-02-2016
28-02-2016
29-02-2016
Last Figure.30505442515960365740

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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Daiwik

New Member
Joined
Jan 12, 2016
Messages
15
Hi BarryL,

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

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
842
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
ABCDEFGHIJK
1DateList 1List 2List 3List 4List 5List 6List 7List 8List 9List 10
21/2/201645573348376149463850
32/2/201644573544455530565747
43/2/201636445534623152603044
54/2/201644376132614535434652
65/2/201644376132614535434652
76/2/201634484762403362585131
87/2/201623315737493959303140
98/2/201643315058463035454854
109/2/201622394445413362336060
1110/2/201629493558403557313637
1211/2/201643526157614046383440
1312/2/201642506256585050574632
1413-02-201629363949584134464544
1514-02-201638413133343535304158
1615-02-201645544735424361456040
1716-02-201650435835325045515840
1817-02-201630563355494936345341
1918-02-201633354962346041595857
2019-02-201624316052434933426059
2120-02-201639343738384354595158
2221-02-201645334448513248525940
2322-02-20163347523759483447
2423-02-201631455359343648
2524-02-2016495154406031
2625-02-201630505357
2726-02-201642
2827-02-2016
2928-02-2016
3029-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:
Code:
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
ABCDEFGHIJK
1DateList 1List 2List 3List 4List 5List 6List 7List 8List 9List 10
21/2/201645573348376149463850
32/2/201644573544455530565747
43/2/201636445534623152603044
54/2/201644376132614535434652
65/2/201644376132614535434652
76/2/201634484762403362585131
87/2/201623315737493959303140
98/2/201643315058463035454854
109/2/201622394445413362336060
1110/2/201629493558403557313637
1211/2/201643526157614046383440
1312/2/201642506256585050574632
1413-02-201629363949584134464544
1514-02-201638413133343535304158
1615-02-201645544735424361456040
1716-02-201650435835325045515840
1817-02-201630563355494936345341
1918-02-201633354962346041595857
2019-02-201624316052434933426059
2120-02-201639343738384354595158
2221-02-201645334448513248525940
2322-02-20163347523759483447
2423-02-201631455359343648
2524-02-2016495154406031
2625-02-201630505357
2726-02-201642
2827-02-2016
2928-02-2016
3029-02-2016
31Last Figure30505442515960365740
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 />
 
Upvote 0

Forum statistics

Threads
1,187,175
Messages
5,962,039
Members
438,578
Latest member
MrJimC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top