Find the last value on the a row

[mark692]

hi guys i want to know the last value of a row like this on my table

	A	B	C	D	E	F	G
1
2	Table 1 in Sheet 1
3		2016	2017	2018	2019	2020
4	Apple	6,565.00	1,321.00	16,516.00	8,491.00
5	Grapes	4,819.00	51,651.00	15,651.00	1,984.00
6	Orange	8,191.00	151,651.00	1,561.00	89,459.00
7
8	Table 2 in Sheet 2
9	Apple	8,491.00
10	Grape	1,984.00
11	Orange	89,459.00
12

<tbody>
</tbody>

Sheet1​

 

[alansidman]

Here is a vba solution

Option Explicit


Sub ABC()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim r As Range, q As Range
    Dim i As Long, lr As Long, lr2 As Long, lc As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 4 To lr
        lc = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        Set r = s1.Cells(i, 1)
        Set q = s1.Cells(i, lc)
        Application.Union(r, q).Copy
        s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action complete"
End Sub

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1		2016	2017	2018	2019	2020
2	Apple	6,565.00	1,321.00	16,516.00	8,491.00
3	Grapes	4,819.00	51,651.00	15,651.00	1,984.00
4	Orange	8,191.00	151,651.00	1,561.00	89,459.00
5
Sheet1

Book1
	A	B
1
2	Apple	8,491.00
3	Grapes	1,984.00
4	Orange	89,459.00
5
Sheet2

In B2 of Sheet2 enter and copy down:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet1!$B$2:$XFD$4,MATCH(A2,Sheet1!$A$2:$A$4,0),0))