Merge Rows in Excel using same columns and 2 different columns

Question

I have data like below.

I need to keep same column on basis of 1,2,3,5,9 and column 6,7,8 need to be combined or merged into one.

For the below example first 8 rows are duplicated but in actual there are 4 rows. Row 9 and 10 are seperate cases.

what can be the best possible way to do it. I tried to use vlookup, merge, concat to make a primary key and merge but somehow all my rows were not found.

column1	column2	column3	column4	column5	column6	column7	column8	column9	column10
Greater Country1	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz			state	1
See Data	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz			state	2
Greater Country1	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz			state	3
See Data	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz			state	4
Greater Country1	DataColumn	massWeight	ColumnNameSame	ItemColumn		Type AB
See Data	DataColumn	massWeight	ColumnNameSame	ItemColumn		Type AB
Greater Country1	DataColumn	massWeight	ColumnNameSame	ItemColumn		Type AB
See Data	DataColumn	massWeight	ColumnNameSame	ItemColumn		Type AB
Alpha	DataColumn	massWeight	DIFFERENT COLUMN ITEM	ItemColumn	xyz			state	11
AlphBetaa	DataColumn	massWeight	VERY DIFFERENT COLUMN ITEM	ItemColumn		Type AB	A	state	12

Expected outcome:

column1	column2	column3	column4	column5	column6	column7	column8	column9	column10
Greater Country1	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz	Type AB		state	1
See Data	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz	Type AB		state	2
Greater Country1	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz	Type AB		state	3
See Data	DataColumn	massWeight	ColumnNameSame	ItemColumn	xyz	Type AB		state	4
Alpha	DataColumn	massWeight	DIFFERENT COLUMN ITEM	ItemColumn	xyz			state	11
AlphBetaa	DataColumn	massWeight	VERY DIFFERENT COLUMN ITEM	ItemColumn		Type AB	A	state	12

Thanks in advance.

When i try the Power Query method, the last column is not coming correct.

If you are including Column9 in the grouping, then you have six (6) different rows, not four (4). Please explain. — Ron Rosenfeld
– Ron Rosenfeld, Commented May 11, 2023 at 17:00
Please provide sample data for the expected output based on your input. Thanks — David Leal
– David Leal, Commented May 12, 2023 at 2:23

Ron Rosenfeld · Accepted Answer · 2023-05-15 10:46:29Z

1

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

Select some cell in your Data Table
Data => Get&Transform => from Table/Range
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

Edited to show updated data sample and result

Given this data:

M Code

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Group by Cols 1-5 and combine columns 6-9 for each group
    #"Grouped Rows" = Table.Group(#"Changed Type", List.FirstN(Table.ColumnNames(#"Changed Type"),5), 
        List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),5), each {_, (t)=>
            List.Distinct(
                List.RemoveNulls(
                    Table.Column(t,_)
                                )
                            ), type list})),

//Expand All Lists
    #"Expanded Lists" = List.Accumulate(
        List.RemoveFirstN(Table.ColumnNames(#"Grouped Rows"),6),
        Table.ExpandListColumn(#"Grouped Rows",Table.ColumnNames(#"Grouped Rows"){5}),
        (state, current)=> Table.ExpandListColumn(state,current)),

//Set data types
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Lists",{{"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", Int64.Type}}),

//sort Column10 as shown in your example output
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Column10", Order.Ascending}})

in 
    #"Sorted Rows"

Output

edited May 15, 2023 at 10:46

answered May 11, 2023 at 17:23

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user12063090 Over a year ago

Dear Ron Rosenfeld,

user12063090 Over a year ago

updated output, your code M works but i missed out one column due to which it isn't working , i dont seem to mention list.Transofrm and Table.Group correcrtly, can you help with updated expected output how to change please

user12063090 Over a year ago

I attached the result from power Query

Ron Rosenfeld Over a year ago

@user12063090 Not sure what you are doing incorrectly. All I did was (using the first code contents I provided), was to add "Column 10" to the List.Transform within the Table.Group function and I see 11 and 12 in the last two rows of Column 10. So that part looks like List.Transform({"Column6","Column7","Column8","Column9","Column10"}, each {_, (t)=>

user12063090 Over a year ago

what do you see for the first four rows ? That’s incorrect for me. As you can see in screenshot it’s 1,3 in one row. Not separate rows

|

Collectives™ on Stack Overflow

Merge Rows in Excel using same columns and 2 different columns

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related