Concatenate with new entry

[Zubair]

Hi Experts,

I am trying to apply formula =CONCATENATE($A$1,"/",A1) to bring set of A1&A1 = SS/SS, A1&A2 = SS/TT ......, at the end of row 5, I need to again change $ sign from A1 to A2 & A1. Need 2 actions please,

1. How can I use a single formula to drag down instead of changing a new row with $ so B6 may bring automatically TT/TT.
2. If any new addition in cell A6 and below comes like for example XX, YY same formula should capture in column B

SS	SS/SS
TT	SS/TT
UU	SS/UU
VV	SS/VV
WW	SS/WW
	TT/TT
	TT/UU
	TT/VV
	TT/WW

 

[Zubair]

Other than concatenate formula also welcome

 

[Zubair]

Please help

 

[Ezguy4u]

Zubair we have to start some place. I am guessing a formula won't work. It should be a program. Now there is a chance some of the A+ students might weigh in and help out.

Sub Concat()

Dim LastRw As Integer
Dim cnt As Integer
Dim cnt1 As Integer

Application.ScreenUpdating = False
Range("B:B").ClearContents
LastRw = Cells(Rows.Count, 1).End(xlUp).Row

cnt = 1
cnt1 = 1

For i = 1 To LastRw
cnt1 = i

Do While Cells(cnt1, 1) <> ""

Cells(1, 3) = Cells(i, 1)
Cells(1, 4) = "/"
Cells(1, 5) = Cells(cnt1, 1)

Cells(cnt, 2).Formula = "=concatenate(C1,D1,E1)"

Cells(cnt, 2).Copy
Cells(cnt, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
cnt = cnt + 1
cnt1 = cnt1 + 1

Loop

Next i

Cells(1, 3).ClearContents
Cells(1, 4).ClearContents
Cells(1, 5).ClearContents
Range("A1").Select
Application.ScreenUpdating = True
End Sub

22-01-30 con ex 1.xlsm
	A	B
1	SS	SS/SS
2	TT	SS/TT
3	UU	SS/UU
4	VV	SS/VV
5	WW	SS/WW
6		TT/TT
7		TT/UU
8		TT/VV
9		TT/WW
10		UU/UU
11		UU/VV
12		UU/WW
13		VV/VV
14		VV/WW
15		WW/WW
Data

 

[Zubair]

Hi Ezguy4u

I have put the above VBA but what formula to put in B1 ?

 

[Zubair]

Hi Ezguy4u

Excellent, yes I run the Macro its working perfectly, very good and many thanks

 

[Ezguy4u]

Zubair, Thank you for the feedback and I am happy to help out.