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
Apple
Grapes
Orange
Apple
Grape
Orange

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Table 1 in Sheet 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 6,565.00[/TD]
[TD="align: right"] 1,321.00[/TD]
[TD="align: right"] 16,516.00[/TD]
[TD="align: right"] 8,491.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] 4,819.00[/TD]
[TD="align: right"] 51,651.00[/TD]
[TD="align: right"] 15,651.00[/TD]
[TD="align: right"] 1,984.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 8,191.00[/TD]
[TD="align: right"] 151,651.00[/TD]
[TD="align: right"] 1,561.00[/TD]
[TD="align: right"] 89,459.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Table 2 in Sheet 2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] 8,491.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] 1,984.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"] 89,459.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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