Convert multiple cell rows into one unique row

[jamiguel77]

HI
I have this data, the idea is to detect on column A, which data is repeated, triplicated, quadrupled.... and generate 1 single row.

LCCH MD ejemplo bmw Pedimentos de importacion v1.xlsx
	A	B	C	D
1
2	Invoice1	Invoice Sales	GUID	Qty
3	5045	A7859	9609AB33-46E3-493B-B397-136BFFFC2004	10
4	5089	A3242	E26D032C-870F-48D5-AEB9-EFD2797C0425	5
5	5089	A3278	A612ED53-5DF8-4EDB-AA76-B02615D630CC	18
Hoja1

the expected result is:

LCCH MD ejemplo bmw Pedimentos de importacion v1.xlsx
	E	F	G
1	EXPECTED RESULT
2	Invoice Sales	GUID	Qty
3	A7859	9609AB33-46E3-493B-B397-136BFFFC2004	10
4	A3242, A3278	E26D032C-870F-48D5-AEB9-EFD2797C0425, A612ED53-5DF8-4EDB-AA76-B02615D630CC	5,18
5
Hoja1

is possible with formula? or need VBA?

thanks

 

[DanteAmor]

Try this macro:

Sub convert_into_row()
  Dim i As Long
  Dim f As Range
  
  For i = 3 To Range("A" & Rows.Count).End(3).Row
    Set f = Range("A2:A" & i - 1).Find(Range("A" & i).Value, , xlValues, xlWhole)
    If f Is Nothing Then
      Range("E" & i).Resize(1, 3).Value = Range("B" & i).Resize(1, 3).Value
    Else
      Range("E" & f.Row).Value = Range("E" & f.Row).Value & ", " & Range("B" & i).Value
      Range("F" & f.Row).Value = Range("F" & f.Row).Value & ", " & Range("C" & i).Value
      Range("G" & f.Row).Value = Range("G" & f.Row).Value & ", " & Range("D" & i).Value
    End If
  Next
End Sub

 

[lrobbo314]

A way using Power Query.

EXCEL
	A	B	C	D	E	F	G	H
1	Invoice1	Invoice Sales	GUID	Qty		Invoice Sales	GUID	Qty
2	5045	A7859	9609AB33-46E3-493B-B397-136BFFFC2004	10		A7859	9609AB33-46E3-493B-B397-136BFFFC2004	10
3	5089	A3242	E26D032C-870F-48D5-AEB9-EFD2797C0425	5		A3242,A3278	E26D032C-870F-48D5-AEB9-EFD2797C0425,A612ED53-5DF8-4EDB-AA76-B02615D630CC	5,18
4	5089	A3278	A612ED53-5DF8-4EDB-AA76-B02615D630CC	18
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols = List.Skip(Table.ColumnNames(Source),1),
    Group = Table.SelectColumns(Table.Group(Source, {"Invoice1"}, List.Transform(Cols, (col)=> {col, each Text.Combine(List.Transform(Table.Column(_,col),Text.From),",")})),Cols)
in
    Group

 

[jamiguel77]

Try this macro:

Sub convert_into_row()
  Dim i As Long
  Dim f As Range
 
  For i = 3 To Range("A" & Rows.Count).End(3).Row
    Set f = Range("A2:A" & i - 1).Find(Range("A" & i).Value, , xlValues, xlWhole)
    If f Is Nothing Then
      Range("E" & i).Resize(1, 3).Value = Range("B" & i).Resize(1, 3).Value
    Else
      Range("E" & f.Row).Value = Range("E" & f.Row).Value & ", " & Range("B" & i).Value
      Range("F" & f.Row).Value = Range("F" & f.Row).Value & ", " & Range("C" & i).Value
      Range("G" & f.Row).Value = Range("G" & f.Row).Value & ", " & Range("D" & i).Value
    End If
  Next
End Sub

Sorry Work, if the data is duplicated N times, sample 100 times, the code work? thanks

 

[DanteAmor]

sample 100 times, the code work?

Of course

Here an example:

varios 09ene2024.xlsm
	A	B	C	D	E	F	G
1					EXPECTED RESULT
2	Invoice1	Invoice Sales	GUID	Qty	Invoice Sales	GUID	Qty
3	5045	A7859	a	10	A7859, A7859, A7859, A7859	a, d, h, k	10, 10, 10, 10
4	5089	A3242	b	5	A3242, A3278, A3242, A3242, A3242, A3278, A3242, A3242	b, c, e, g, i, j, l, n	5, 18, 5, 5, 5, 18, 5, 5
5	5089	A3278	c	18
6	5045	A7859	d	10
7	5089	A3242	e	5
8	1515	A7859	f	10	A7859, A7859	f, m	10, 10
9	5089	A3242	g	5
10	5045	A7859	h	10
11	5089	A3242	i	5
12	5089	A3278	j	18
13	5045	A7859	k	10
14	5089	A3242	l	5
15	1515	A7859	m	10
16	5089	A3242	n	5
Hoja1

 

[alansidman]

An alternative is with Power Pivot.
check out this link on how to achieve with Power Pivot.

https://www.sfmagazine.com/articles/2018/july/excel-reporting-text-in-a-pivot-table/

Book6
	A	B	C	D	E	F	G	H	I
2	Invoice1	Invoice Sales	GUID	Qty		Row Labels	InvoiceSales	GUIDs	Total Quanties
3	5045	A7859	9609AB33-46E3-493B-B397-136BFFFC2004	10		5045	A7859	9609AB33-46E3-493B-B397-136BFFFC2004	10
4	5089	A3242	E26D032C-870F-48D5-AEB9-EFD2797C0425	5		5089	A3242, A3278	E26D032C-870F-48D5-AEB9-EFD2797C0425, A612ED53-5DF8-4EDB-AA76-B02615D630CC	5. 18
5	5089	A3278	A612ED53-5DF8-4EDB-AA76-B02615D630CC	18
6
Sheet1