Excel: Combine multiple rows but keep column data

microsoft excelmicrosoft-excel-2011vbavlookup

I have an Excel spreadsheet (Excel 2011 for Mac) with 1175 rows containing species names (431 names total) and 72 columns (A:BT) of months from October 2011 to August 2017. Each row only has one cell of information (besides the species name), but some species have multiple rows, and the number of rows per species varies.

Screenshot of a partial view of the table

I would like to combine rows with the same species name into one row, but keep the column data in the correct respective column.

Screenshot of what I'd like the table to look like, based on the partial view in the first screenshot

Because every row only has one cell filled in, a way to merge columns of data with the same species name (keeping the one value that might be in the column) or collapse all the rows with the same species name into one row is basically what I'm looking for. I tried creating a vlookup function to create a new table based on the species name, but could only ever get it to pull in the first column with data for that species or the last column with data.

Any help is greatly appreciated! I'm hoping I don't have to go through and do this all by hand.

Best Answer

Logic for writing VBA code is there but it becomes too complicated for me. So I am humbly suggesting a manual method instead which uses Concatenate Function of Excel:

  1. In Cell B2 write Formula : =Concatenate(B3,B4,B5,B6,B7,B8) ( If you dont want to write Formula in detail ; You can hold Control Key Pressed and Select the Cells to Concatenate )

  2. Copy Formula of B2 and Paste it in the range (C2....Bt2)

  3. You will get the desired result in Row 2.
  4. Now Select Row 2 Copy it and Paste Special the same in Row 2 as VALUES
  5. Delete all the Rows Row 3 to Row 8 now.
  6. Repeat the same process Step 1 to Step 5 One by One.

For any doubt, you may please revert back.

With Regards.

Related Question