CODEWITHSUNDEEP
excelgoogle-sheets
mdnba50
mdnba50

Reputation: 379

Merge rows with same value in column A

I have the columns, movie-name which is column A and then month name from column B to P.

e.g.

movie-name | Jan | Feb | March | April
movie1     | 0.1 |     |       |
movie2     |     | 0.4 |       |
movie2     |     |     |  0.9  | 
movie1     |     | 0.8 |       |
movie2     |     |     |       | 0.2

I'd like to merge column a with same movie name so the desired result is:

movie-name | Jan | Feb | March | April
movie1     | 0.1 | 0.8 |       |
movie2     |     | 0.4 |  0.9  | 0.2

How do i do this with google sheets or excel formula?

Upvotes: 0

Views: 3962

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18707

=QUERY(filter({A:A,IFERROR(B:E*1,B:E)}, A:A<>""), "select Col1, sum(Col2), sum(Col3), sum(Col4), sum(Col5) group by Col1")

  • A:A -- column with names

  • B:E -- columns with months

  • sum(Col2), sum(Col3), sum(Col4), sum(Col5)... add more columns for more months

enter image description here

Upvotes: 2

Daniel Gale
Daniel Gale

Reputation: 663

In Excel,

Select the data and headings.
Choose the Data Tab and click sort.
Sort by column (movie-name) and make sure the 'My Data contains headers' checkbox is selected.
Next while the data is still highlighted and the Data tab is still open,
  Choose the SubTotal button, movie-name should be selected in the 'At each change' option,
  Select each column you want subtotaled. (Jan-Dec), Click okay.

You will be able to select between seeing all data, sub-totals that you are asking for and a grand-total.

Subtotal summary

Upvotes: 1

Related Questions