CODEWITHSUNDEEP
functionexcelconcatenationvba
pwlm
pwlm

Reputation: 184

Concatenate alternating fixed column headers and row values

I have a spreadsheet with fixed column headers and variable row data, I would like to create a simple tool (lets aim for 1 click) that will concatenate the column values and row data entered by the user into an attribute string (with '=' after each header and ';' after each value).

Before looks like this:

Before

After looks like this:

After

The output is required in a separate worksheet and if possible saved as a value. The number of columns could vary so a solution that uses a defined table would be useful.

Formula used:

=CONCATENATE(before!$A$1,"=",before!$A2,";",before!$B$1,"=",before!$B2,";",before!$C$1,"=",before!$C2,";")

Any assistance would be greatly appreciated.

Upvotes: 0

Views: 1360

Answers (2)

jsanchezs
jsanchezs

Reputation: 2070

You can use this macro code temporary and assuming there's only one row of values :

Sub Macro1()

' Macro1 Macro

Range("A1").Select

Dim r As Byte
Dim c as Byte

Dim stringunion As String

r = 1

c = 1

Do While Cells(r, c) <> ""

    stringunion = stringunion & Cells(r, c).Value & "=" & Cells(r + 1, c) & ";"

    c = c + 1

Loop

MsgBox stringunion


End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

The following UDF will do it:

Function unionText(ttl As Range, rng As Range) As String
Dim i As Long
If ttl.Cells.Count <> rng.Cells.Count Or _
    ttl.Rows.Count <> 1 Or rng.Rows.Count <> 1 Then
    unionText = CVErr(xlErrValue)
    Exit Function
End If

For i = 1 To ttl.Cells.Count
    unionText = unionText & ttl(i) & "=" & rng(i) & ";"
Next i
End Function

It is called in the sheet like this:

=unionText(before!$A$1:$C$1,before!A2:C2)

Then copied down

enter image description here

Mine is obviously on the same sheet but the formula above uses your sheet reference.

Upvotes: 2

Related Questions