Combine Multiple Rows in to one

[tomexcel1]

Hi All

I have a huge data file (1000000 rows) that shows amongst other things: Customer Number, Name, Item and Value. Im wanting to reduce the data set by combining multiple rows that are for the same person and the same item. For example in the table below I would like to combine Joe Bloggs two book rows in to one etc.

I just cant think of how to do it for such a large data set, There might not be a way? Any help is much appreciated.

[TABLE="width: 360"]
<tbody>[TR]
[TD]CUSTOMER ID[/TD]
[TD]CUSTOMER NAME[/TD]
[TD]ITEM[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]1298[/TD]
[TD]Joe Bloggs[/TD]
[TD]Book[/TD]
[TD]£105.80[/TD]
[/TR]
[TR]
[TD]1298[/TD]
[TD]Joe Bloggs[/TD]
[TD]Book[/TD]
[TD]£105.80[/TD]
[/TR]
[TR]
[TD]1298[/TD]
[TD]Joe Bloggs[/TD]
[TD]Chair[/TD]
[TD]£200.10[/TD]
[/TR]
[TR]
[TD]1345[/TD]
[TD]Andrew Smith[/TD]
[TD]TV[/TD]
[TD]£2,345.00[/TD]
[/TR]
[TR]
[TD]1345[/TD]
[TD]Andrew Smith[/TD]
[TD]TV[/TD]
[TD]£2,345.00[/TD]
[/TR]
[TR]
[TD]1687[/TD]
[TD]Jane Doe[/TD]
[TD]Book[/TD]
[TD]£105.80[/TD]
[/TR]
[TR]
[TD]1687[/TD]
[TD]Jane Doe[/TD]
[TD]Chair[/TD]
[TD]£200.10[/TD]
[/TR]
[TR]
[TD]1687[/TD]
[TD]Jane Doe[/TD]
[TD]TV[/TD]
[TD]£2,345.00[/TD]
[/TR]
[TR]
[TD]1687[/TD]
[TD]Jane Doe[/TD]
[TD]Table[/TD]
[TD]£200.10[/TD]
[/TR]
</tbody>[/TABLE]

 

[dUBBINS]

Assuming you do not write VBA code, the simplest solution I can think of is to use two helper columns.

The first (in Cell E2, and drag down):
=TEXTJOIN(,TRUE,A2:D2)

The second (in Cell F2, and drag down):
=COUNTIF($E$2:E2,E2)

Filter Column F, to remove all "1"

Delete all rows that are left.

 

[tomexcel1]

Hi Dubbins

Thanks for that, unfortunately I cant use TEXTJOIN on my PC. Im open to any VBA code im not an expert but do use it regularly

Thanks

 

[sandy666]

maybe try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]CUSTOMER ID[/td][td=bgcolor:#5B9BD5]CUSTOMER NAME[/td][td=bgcolor:#5B9BD5]ITEM[/td][td=bgcolor:#5B9BD5]VALUE[/td][td][/td][td=bgcolor:#70AD47]CUSTOMER ID[/td][td=bgcolor:#70AD47]CUSTOMER NAME[/td][td=bgcolor:#70AD47]ITEM[/td][td=bgcolor:#70AD47]VALUE[/td][/tr]

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

1298​

[/td][td=bgcolor:#DDEBF7]Joe Bloggs[/td][td=bgcolor:#DDEBF7]Book[/td][td=bgcolor:#DDEBF7]L105.80[/td][td][/td][td=bgcolor:#E2EFDA]

1298​

[/td][td=bgcolor:#E2EFDA]Joe Bloggs[/td][td=bgcolor:#E2EFDA]Book[/td][td=bgcolor:#E2EFDA]

105.8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1298​

[/td][td]Joe Bloggs[/td][td]Book[/td][td]L105.80[/td][td][/td][td]

1298​

[/td][td]Joe Bloggs[/td][td]Chair[/td][td]

200.1​

[/td][/tr]

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

1298​

[/td][td=bgcolor:#DDEBF7]Joe Bloggs[/td][td=bgcolor:#DDEBF7]Chair[/td][td=bgcolor:#DDEBF7]L200.10[/td][td][/td][td=bgcolor:#E2EFDA]

1345​

[/td][td=bgcolor:#E2EFDA]Andrew Smith[/td][td=bgcolor:#E2EFDA]TV[/td][td=bgcolor:#E2EFDA]

2345​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1345​

[/td][td]Andrew Smith[/td][td]TV[/td][td]L2,345.00[/td][td][/td][td]

1687​

[/td][td]Jane Doe[/td][td]Book[/td][td]

105.8​

[/td][/tr]

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

1345​

[/td][td=bgcolor:#DDEBF7]Andrew Smith[/td][td=bgcolor:#DDEBF7]TV[/td][td=bgcolor:#DDEBF7]L2,345.00[/td][td][/td][td=bgcolor:#E2EFDA]

1687​

[/td][td=bgcolor:#E2EFDA]Jane Doe[/td][td=bgcolor:#E2EFDA]Chair[/td][td=bgcolor:#E2EFDA]

200.1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1687​

[/td][td]Jane Doe[/td][td]Book[/td][td]L105.80[/td][td][/td][td]

1687​

[/td][td]Jane Doe[/td][td]TV[/td][td]

2345​

[/td][/tr]

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

1687​

[/td][td=bgcolor:#DDEBF7]Jane Doe[/td][td=bgcolor:#DDEBF7]Chair[/td][td=bgcolor:#DDEBF7]L200.10[/td][td][/td][td=bgcolor:#E2EFDA]

1687​

[/td][td=bgcolor:#E2EFDA]Jane Doe[/td][td=bgcolor:#E2EFDA]Table[/td][td=bgcolor:#E2EFDA]

200.1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1687​

[/td][td]Jane Doe[/td][td]TV[/td][td]L2,345.00[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

1687​

[/td][td=bgcolor:#DDEBF7]Jane Doe[/td][td=bgcolor:#DDEBF7]Table[/td][td=bgcolor:#DDEBF7]L200.10[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTOMER ID", Int64.Type}, {"CUSTOMER NAME", type text}, {"ITEM", type text}, {"VALUE", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CUSTOMER ID", "CUSTOMER NAME", "ITEM"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "VALUE", each List.Distinct(Table.Column([Count],"VALUE"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"VALUE", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Values",{{"VALUE", type number}})
in
    #"Changed Type1"[/SIZE]