Combine two rows of data into one row

**JoeyGirl** · 09-30-2010, 09:35 PM

Hi Everyone,

My colleague has a data extract that has information belonging to one customer over two rows. What we would like to do is bring each set together so that for each customer there is only one row. I have googled and searched the forum and the closest thing to this I can find is splitting one row into two using the "OFFSET" function.

This report is recieved weekly and will end up with around 5000 entries so a solution with as little manual input as possible would be much appreciated!

Let me know if you require a sample

Thanks

**JBeaucaire** · 09-30-2010, 09:47 PM

This can be done rather easily with a macro. Is VBA OK?

If so, provide a workbook showing two BEFORE and AFTER sheets on a small set of the data. Don't dumb it down too much, or a macro written based on your example sheets won't work.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**JoeyGirl** · 09-30-2010, 09:56 PM

More Info: There is no unique identifier in both rows

**JoeyGirl** · 09-30-2010, 10:36 PM

I have attached a sample of the steps we go through starting from what we get in a text file and finishing with the ideal end result. It's perfectly fine if the solution can only match the "move to one line" tab, but the "end result" is preferred if that can be automated too (it's just concatinating the related fields to tidy it up). The data will always be the same size and shape.

Thanks for your help!

**JBeaucaire** · 10-01-2010, 10:55 AM

It's interesting that you don't want to put the time back together properly. On the End Result sheet the content in cell C1 is 6AM but there is a PM in D1. Wouldn't that be best if C1 and D1 were merged properly back into a single cell with the correct time in it?

Anyway, I wrote a custom function for this task so you can use it the same way. Here's the function:

Please Login or Register  to view this content.

On the attached sheet, I show how I'm using the function to directly parse the words out into the cells. Most only parse 1 word, but the function parses up to 4 words in a single cell.

Then I wrote a simple macro to enter those formulas for you to parse the data, flatten the values, then remove the original column A. Run this on a copy of your data.

Please Login or Register  to view this content.

**JoeyGirl** · 10-03-2010, 09:25 PM

Hi Again,

Thanks for the code

I get a runtime error "Method 'Range' of object '_Global' failed, and the debugger points to:

Range("B1:B" & LR / 2).FormulaR1C1 = "=WORD(INDEX(C1,ROW()*2-1), 1)"

Any idea how to fix this?

**Charlie_Howell** · 10-03-2010, 10:42 PM

If macros aren't an option try this

**JBeaucaire** · 10-04-2010, 03:20 PM

Originally Posted by JoeyGirl

I get a runtime error "Method 'Range' of object '_Global' failed, and the debugger points to:

Please Login or Register  to view this content.

Any idea how to fix this?

When you DEBUG at that moment and hover your cursor over LR, what is the value?

If you need to edit the LR code at that top to use a different column, do so.

**JBeaucaire** · 10-11-2010, 05:54 PM

As a result of helping another OP with this same kind of need and the prompting of DonKeyOte, I've improved the WORD() function to be cleaner and unlimited in number of words it can pull out.

Review the file and code in post #5 anew or the updates.