Excel - How to merge rows of similar data?

December 2016




Issue


I am trying to merge various rows of data with a common heading in Column A. For example I have the following data:

A 1 0 0 0 1 
B 1 1 0 0 0 
C 1 0 0 0 0 
A 0 1 0 0 0 
B 0 0 1 1 1 
C 0 1 1 1 1 


I would like:

A 1 1 0 0 1 
B 1 1 1 1 1 
C 1 1 1 1 1 


I don't want to add them together, as they are dates, but i would like to end up with no duplicate rows and all data relevant to the column a heading on the same row,

Solution


You can try this
  • Copy the unique value to a new sheet( use advance filter)
  • The you can use this formula


=MAX(SUMPRODUCT((Sheet1!$A$1:$A$6=$A1) * (Sheet1!B$1:B$6)))

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - How to merge rows of similar data? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.