Join Lines

[Romulo Avila]

Goodnight,


I have a spreadsheet with data as per the image (general data), I need a way for the data to be as per the image (concatenated).

how to proceed

Thank you for your help

30876

[June7]

One way is to build a recordset of the rows using aggregation SQL then CopyFromRecordset method writes data to another location. Like:


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";HDR=Yes';"
rs.Open "SELECT NomeA, NomeC, Max(MM42) AS M42, Max(MM46) AS M46, Max(MASS) AS MSS FROM [Sheet1$] GROUP BY NomeA, NomeC", cn, adOpenStatic, adLockOptimistic, adCmdText
rs.MoveFirst
Sheet2.Range("A2").CopyFromRecordset rs

[p45cal]

Power Query can do this too (see attached). Unpivot then repivot.
Note that I added a couple of data rows to the original to test what happens if there are no Xs agains a name: that name doesn't feature at all in the results table, however this can rectified if necessary.
30881
This is the M-code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"MM43", "MM46", "MASS"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Only Selected Columns", List.Distinct(#"Unpivoted Only Selected Columns"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
If you change the source table, right-click the results table and choose Refresh to update.