This problem is in an Excel .xls file.
Simplest Use Case:
Column A has one row. Column B has 5 rows. The 5 rows in Column B need to be merged into one row, delimited by newlines.
I have a huge .xls document where there are a ton of IDs in column A. There are on average anywhere from 3 to 10 rows that belong to each column A row.
How to know which Column B rows belong to which Column A? By the positioning of the cells. One Column A row may have 5 Column B rows to the right of it.
I don't have any VBA experience. I have looked around for macros and functions but haven't had any luck finding anything that matches this problem.
Edit: I am now trying to figure out how to get the script to ignore rows that have a one-to-one mapping between column A and column B.
Edit again - 06-20-2012:
Now that I can attach images, here is a screenshot of an image for what I'm trying to get.
The rows for Brian and Mark should be ignored, while Scott and Tim get their values copied over.
Edit:
Unmerging column A, using the code that Andy supplied, and then using this VB script afterwards does the trick:
Sub mergeA()
For i = 2 To Cells(65535, 1).End(xlUp).Row
If IsEmpty(Cells(i, 1)) Then Range(Cells(i - 1, 1), Cells(i, 1)).Merge
Next
End Sub
That VB script puts the cells in column A back together
I didn't make the script, it came from this web page:
http://www.vbforums.com/showthread.php?t=601304
A1
and Column B has 3 rows for that ID, then inA4
we will find the next ID until we get to a blank row)? Also, do you plan to learn VBA to accomplish your task? Can you show any attempts you've made so far (perhaps you can focus on one part of the problem like how to merge a dynamic number of rows)? – Zairja