How to combine rows where column is equal. Weird source data layout.

[Jemmick]

I am querying a database that is setup in a weird fashion. It has a work order field, a index field, and a text field. So below work order "9" says "DISPENSER 1 MIDGRADE PUMP NOT WORKING" and as you can see 9 has two sequence numbers. I need to find a way to combine these into 1 row that would have just the number 9 and the text. Anyone have any recommendations?

[FONT=&quot]3[/FONT]​	[FONT=&quot]1[/FONT]​	[FONT=&quot]Test for External Customer.[/FONT]	[FONT=&quot]W[/FONT]
[FONT=&quot]9[/FONT]​	[FONT=&quot]2[/FONT]​	[FONT=&quot]WORKING.[/FONT]	[FONT=&quot]W[/FONT]
[FONT=&quot]10[/FONT]​	[FONT=&quot]2[/FONT]​	[FONT=&quot]working.[/FONT]	[FONT=&quot]W[/FONT]
[FONT=&quot]16[/FONT]​	[FONT=&quot]2[/FONT]​	[FONT=&quot]fell off.[/FONT]	[FONT=&quot]W[/FONT]

<tbody>
[TD="bgcolor: #333399"] [FONT=&quot]smwd_wo_quote_nbr[/FONT]
[/TD]
[TD="bgcolor: #333399"] [FONT=&quot]smwd_seq[/FONT]
[/TD]
[TD="bgcolor: #333399"] [FONT=&quot]smwd_text[/FONT]
[/TD]
[TD="bgcolor: #333399"] [FONT=&quot]smwd_flag[/FONT]
[/TD]

[TD="bgcolor: #ccccff"]

[FONT=&quot]1[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"]

[FONT=&quot]1[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]breakaway off of pump #2[/FONT]
[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]W[/FONT]
[/TD]

[TD="bgcolor: #ccccff"]

[FONT=&quot]9[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"]

[FONT=&quot]1[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]DISPENSER 1 MIDGRADE PUMP NOT[/FONT]
[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]W[/FONT]
[/TD]

[TD="bgcolor: #ccccff"]

[FONT=&quot]10[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"]

[FONT=&quot]1[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]Dispenser on midgrade not[/FONT]
[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]W[/FONT]
[/TD]

[TD="bgcolor: #ccccff"]

[FONT=&quot]16[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"]

[FONT=&quot]1[/FONT]​

[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]Dispenser 1 not pumping. Hose[/FONT]
[/TD]
[TD="bgcolor: #ccccff"][FONT=&quot]W[/FONT]
[/TD]

</tbody>

 

[RasGhul]

Hi Jemmick,

Can you use this? Does the "smwd_text" rows ever exceed 2 rows?

Book1
	A	B	C	D	E
1	smwd_wo_quote_nbr	smwd_seq	smwd_text	smwd_flag	Helper
2	1	1	breakaway off of pump #2	W	breakaway off of pump #2
3	3	1	Test for External Customer.	W	Test for External Customer.
4	9	1	DISPENSER 1 MIDGRADE PUMP NOT	W	DISPENSER 1 MIDGRADE PUMP NOT WORKING.
5	9	2	WORKING.	W
6	10	1	Dispenser on midgrade not	W	Dispenser on midgrade not working.
7	10	2	working.	W
8	16	1	Dispenser 1 not pumping. Hose	W	Dispenser 1 not pumping. Hose fell off.
9	16	2	fell off.	W
Sheet1

Cell Formulas
Range	Formula
E2	{=IF(A2=A1,"",IF(COUNTIF($A$2:$A$9,A2)=2,INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=A2,ROW($A$2:$A$9)-ROW($A$2)+1),1))&" "&INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=A2,ROW($A$2:$A$9)-ROW($A$2)+1),2)),INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=A2,ROW($A$2:$A$9)-ROW($A$2)+1),1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jemmick]

Hi Jemmick,

Can you use this? Does the "smwd_text" rows ever exceed 2 rows?

Book1
	A	B	C	D	E
1	smwd_wo_quote_nbr	smwd_seq	smwd_text	smwd_flag	Helper
2	1	1	breakaway off of pump #2	W	breakaway off of pump #2
3	3	1	Test for External Customer.	W	Test for External Customer.
4	9	1	DISPENSER 1 MIDGRADE PUMP NOT	W	DISPENSER 1 MIDGRADE PUMP NOT WORKING.
5	9	2	WORKING.	W
6	10	1	Dispenser on midgrade not	W	Dispenser on midgrade not working.
7	10	2	working.	W
8	16	1	Dispenser 1 not pumping. Hose	W	Dispenser 1 not pumping. Hose fell off.
9	16	2	fell off.	W
Sheet1

Cell Formulas
Range	Formula
E2	{=IF(A2=A1,"",IF(COUNTIF($A$2:$A$9,A2)=2,INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=A2,ROW($A$2:$A$9)-ROW($A$2)+1),1))&" "&INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=A2,ROW($A$2:$A$9)-ROW($A$2)+1),2)),INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=A2,ROW($A$2:$A$9)-ROW($A$2)+1),1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Yes it has up to 54 rows on one record

 

[sandy666]

maybe with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]smwd_wo_quote_nbr[/td][td=bgcolor:#5B9BD5]smwd_seq[/td][td=bgcolor:#5B9BD5]smwd_text[/td][td=bgcolor:#5B9BD5]smwd_flag[/td][td][/td][td=bgcolor:#70AD47]smwd_wo_quote_nbr[/td][td=bgcolor:#70AD47]smwd_text[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]breakaway off of pump #2[/td][td=bgcolor:#DDEBF7]W[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]breakaway off of pump #2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td]

1​

[/td][td]Test for External Customer.[/td][td]W[/td][td][/td][td]

3​

[/td][td]Test for External Customer.[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

9​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]DISPENSER 1 MIDGRADE PUMP NOT[/td][td=bgcolor:#DDEBF7]W[/td][td][/td][td=bgcolor:#E2EFDA]

9​

[/td][td=bgcolor:#E2EFDA]DISPENSER 1 MIDGRADE PUMP NOT WORKING.[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

9​

[/td][td]

2​

[/td][td]WORKING.[/td][td]W[/td][td][/td][td]

10​

[/td][td]Dispenser on midgrade not working.[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]Dispenser on midgrade not[/td][td=bgcolor:#DDEBF7]W[/td][td][/td][td=bgcolor:#E2EFDA]

16​

[/td][td=bgcolor:#E2EFDA]Dispenser 1 not pumping. Hose fell off.[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10​

[/td][td]

2​

[/td][td]working.[/td][td]W[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

16​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]Dispenser 1 not pumping. Hose[/td][td=bgcolor:#DDEBF7]W[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

16​

[/td][td]

2​

[/td][td]fell off.[/td][td]W[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"smwd_wo_quote_nbr"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "smwd_text", each Table.Column([Count],"smwd_text")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"smwd_text", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"})
in
    #"Removed Columns"[/SIZE]