Match Concatenate and Adjust Row in Macro Excel

[nurj]

Hi all...

I have series of data in Sheet1 and Sheet2. I want to match row from sheet1 to sheet2 with the condition. If the result of concatenate in Sheet1 and Sheet2 is match, then the data in that row is adjusted. I am stuck at adjusting the rows

Here is my data and the output

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	B	C	D	E	1	1	1	F	G	H	BCG
2	A	B	C	D	E	1	1	1	F	G	H	BCG
3	S	D	W	E	R	1	1	1	F	V	A	DWV
4	S	D	W	E	R	1	1	1	F	V	A	DWV
5	T	R	E	B	N	1	1	1	A	S	Q	RES
6	D	F	Q	L	K	1	1	1	Q	V	B	FQV
7	V	B	R	T	U	1	1	1	A	H	L	BRH
8	V	B	R	T	U	1	1	1	A	H	L	BRH
9	L	G	E	B	X	1	1	1	K	R	P	GER
10	A	C	V	E	T	1	1	1	H	M	A	CVM
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	B	C	D	E	1	1	1	F	G	H	BCG
2	S	D	W	E	R	1	1	1	F	V	A	DWV
3	T	R	E	B	N	1	1	1	A	S	Q	RES
4	D	F	Q	L	K	1	1	1	Q	V	B	FQV
5	V	B	R	T	U	1	1	1	A	H	L	BRH
6	L	G	E	B	X	1	1	1	K	R	P	GER
7	B	F	Q	W	M	1	1	1	M	Q	D	FQQ
8	A	C	V	E	T	1	1	1	H	M	A	CVM
Sheet2

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	A	B	C	D	E	1	1	1	F	G	H	BCG			A	B	C	D	E	1	1	1	F	G	H	BCG
2	A	B	C	D	E	1	1	1	F	G	H	BCG
3	S	D	W	E	R	1	1	1	F	V	A	DWV			S	D	W	E	R	1	1	1	F	V	A	DWV
4	S	D	W	E	R	1	1	1	F	V	A	DWV
5	T	R	E	B	N	1	1	1	A	S	Q	RES			T	R	E	B	N	1	1	1	A	S	Q	RES
6	D	F	Q	L	K	1	1	1	Q	V	B	FQV			D	F	Q	L	K	1	1	1	Q	V	B	FQV
7	V	B	R	T	U	1	1	1	A	H	L	BRH			V	B	R	T	U	1	1	1	A	H	L	BRH
8	V	B	R	T	U	1	1	1	A	H	L	BRH
9	L	G	E	B	X	1	1	1	K	R	P	GER			L	G	E	B	X	1	1	1	K	R	P	GER
10															B	F	Q	W	M	1	1	1	M	Q	D	FQQ
11	A	C	V	E	T	1	1	1	H	M	A	CVM			A	C	V	E	T	1	1	1	H	M	A	CVM
Compare

Thank you for you help...

 

[Yongle]

I was not sure whether the row in sheet1 or sheet2 or both was being amended based on concatenation matching up
Code below writes a value to column AA in the matched row in both sheets - so take your pick
The code assumes that concatenated values are already in column L in both sheets and that the actual data starts in row 2 in both sheets

Put code in a module on its own

Option Explicit
'make available to more than one procedure

Private w1 As Worksheet, w2 As Worksheet, rng1 As Range, rng2 As Range, row1 As Long, row2 As Long
Sub AlterRows()
    Dim cel As Range, row1 As Long, row2 As Long
    SetRanges
    For Each cel In rng1
        row2 = GetMatch(cel.Text)
        If row2 > 0 Then
            row1 = cel.Row
            w1.Cells(row1, "AA") = "amended1"
            w2.Cells(row2, "AA") = "amended2"
        End If
    Next cel
End Sub

Private Function GetMatch(concat As String) As Long
    On Error Resume Next
    If concat <> "" Then GetMatch = rng2.Find(concat, lookat:=xlWhole).Row
End Function

Private Sub SetRanges()
    Set w1 = Sheets("Sheet1")
    Set w2 = Sheets("Sheet2")
    Set rng1 = w1.Range("L2", w1.Range("L" & Rows.Count).End(xlUp))
    Set rng2 = w2.Range("L2", w2.Range("L" & Rows.Count).End(xlUp))
End Sub