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]smwd_wo_quote_nbr[/FONT]	[FONT=&quot]smwd_seq[/FONT]	[FONT=&quot]smwd_text[/FONT]	[FONT=&quot]smwd_flag[/FONT]
[FONT=&quot]1[/FONT]​	[FONT=&quot]1[/FONT]​	[FONT=&quot]breakaway off of pump #2[/FONT]	[FONT=&quot]W[/FONT]
[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]1[/FONT]​	[FONT=&quot]DISPENSER 1 MIDGRADE PUMP NOT[/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]1[/FONT]​	[FONT=&quot]Dispenser on midgrade not[/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]1[/FONT]​	[FONT=&quot]Dispenser 1 not pumping. Hose[/FONT]	[FONT=&quot]W[/FONT]
[FONT=&quot]16[/FONT]​	[FONT=&quot]2[/FONT]​	[FONT=&quot]fell off.[/FONT]	[FONT=&quot]W[/FONT]

<tbody>
</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

smwd_wo_quote_nbr	smwd_seq	smwd_text	smwd_flag		smwd_wo_quote_nbr	smwd_text
1​	1​	breakaway off of pump #2	W		1​	breakaway off of pump #2
3​	1​	Test for External Customer.	W		3​	Test for External Customer.
9​	1​	DISPENSER 1 MIDGRADE PUMP NOT	W		9​	DISPENSER 1 MIDGRADE PUMP NOT WORKING.
9​	2​	WORKING.	W		10​	Dispenser on midgrade not working.
10​	1​	Dispenser on midgrade not	W		16​	Dispenser 1 not pumping. Hose fell off.
10​	2​	working.	W
16​	1​	Dispenser 1 not pumping. Hose	W
16​	2​	fell off.	W

[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]