Auto concatenate and reverse concatenate based on range & Remove duplicates .

[Anand307]

We have a workbook in which there are 2 sheets - "CP + Party" & "Auto update"



So everyday the used rows in sheet "CP + Party" changes, depending on used rows count in "CP + Party" we need to apply a straight Concatenate formula in "Auto update" sheet starting from cell A2, As =CONCATENATE('CP + Party'!B2,'CP + Party'!C2).

In the attached example workbook the used rows in "CP + Party" sheet are 301 rows(Including header), so in "Auto update" sheet we need apply straight Concatenate formula until row 301 and starting from row 302 we need to reverse the Concatenate formula as =CONCATENATE('CP + Party'!C2,'CP + Party'!B2) text 1 and text 2 are flipped & pull it until row 601 (601 row because the used rows are 301 in "CP + Party" so we are doubling the count to get complete reverse concatenate data ).



Once the above is done we can remove the duplicates in "Auto update" sheet Column A.



I have tried doing this myself with the help of google codes , but tricky part is finding a logic to reverse the concatenate formula range based on row count in "CP + Party" sheet, Help in need, please help.

 

[Chihiro]

Something like this.

Sub Test()
Dim lRow As Long, i As Long
Dim resArr As Variant

myArray = Worksheets("CP + Party").Cells(1).CurrentRegion

ReDim resArr((UBound(myArray, 1) - 1) * 2, 1)
For i = 0 To UBound(myArray, 1) - 2
    resArr(i, 0) = myArray(i + 2, 2) & myArray(i + 2, 3)
    resArr(i + UBound(myArray, 1) - 1, 0) = myArray(i + 2, 3) & myArray(i + 2, 2)
Next

With Worksheets("Auto update").Range("A2").Resize(UBound(resArr, 1))
    .Value = resArr
    .RemoveDuplicates Columns:=1, Header:=xlNo
End With


End Sub

 

[Anand307]

Thank you Chihiro! Thanks a mil It worked with 100% perfection.