Combine Multiple Rows in to one

tomexcel1

New Member
Joined
Feb 22, 2018
Messages
47
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.

CUSTOMER IDCUSTOMER NAMEITEMVALUE
1298Joe BloggsBook£105.80
1298Joe BloggsBook£105.80
1298Joe BloggsChair£200.10
1345Andrew SmithTV£2,345.00
1345Andrew SmithTV£2,345.00
1687Jane DoeBook£105.80
1687Jane DoeChair£200.10
1687Jane DoeTV£2,345.00
1687Jane DoeTable£200.10

<tbody>
</tbody>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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
 
Upvote 0
maybe try PowerQuery

CUSTOMER IDCUSTOMER NAMEITEMVALUECUSTOMER IDCUSTOMER NAMEITEMVALUE
1298​
Joe BloggsBookL105.80
1298​
Joe BloggsBook
105.8​
1298​
Joe BloggsBookL105.80
1298​
Joe BloggsChair
200.1​
1298​
Joe BloggsChairL200.10
1345​
Andrew SmithTV
2345​
1345​
Andrew SmithTVL2,345.00
1687​
Jane DoeBook
105.8​
1345​
Andrew SmithTVL2,345.00
1687​
Jane DoeChair
200.1​
1687​
Jane DoeBookL105.80
1687​
Jane DoeTV
2345​
1687​
Jane DoeChairL200.10
1687​
Jane DoeTable
200.1​
1687​
Jane DoeTVL2,345.00
1687​
Jane DoeTableL200.10
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top