1

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.

|improve this question
  • 1
    In this answer is a solution with VBA. You can simplify it and change it to your needs, and have an easy solution for your problem. – IQV Sep 18 '17 at 6:20
  • 1
    I've looked at that solution before, but from what I can tell it's pretty specific to only having up to 2 rows with 2 columns of data that need to be merged into 1 row. With 72 columns of data and varying numbers of rows to combine, I'm not sure how to modify that solution to make it work for my table. Any thoughts? Thanks! – AYoung Sep 26 '17 at 18:17
  • 1
    You have to expand the logic from 2 * 2 to n * 72: The outer loop goes through col A. It remembers the species and the number of the (first) row of this species. As long as the next row is the same species the inner loop goes along the row and copies (or moves) an entry to the first row of this species. – IQV Sep 27 '17 at 5:54
  • 1
    So then it's almost like I'd need a totally blank row with that species name to start? Because in the first row for each species, the first column with data is different - it's not like they all have data in column 2, then the next row has data in column 3, etc. If I don't want the loop to copy a blank data cell over a cell above it that does have data, it seems like for my data, the loop would have to start with a totally blank row (save for the species name in column 1). Or am I just not understanding VBA solution in the other post? – AYoung Sep 27 '17 at 19:28
  • 1
    You can do this, but it's not necessary. You can keep the first row of every species and add only the one cell of the following rows in the range "B:BT" which have an entry. Metaphorically speaking you move all cells with content in the first row of this species. – IQV Sep 28 '17 at 5:59
1

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.