Merging several rows of data into one row.

**Lozhaze** · 07-04-2012, 05:29 AM

Hi, i need some advice please, i have a page of data that looks like this-

(each date, name, and number is in it's own cell)

18-Jun-12 Smith, John 5 70
18-Jun-12 Smith, John 1 90
18-Jun-12 Smith, John 1 126

and i have to get it all into one row to look like this to import into another program

18-Jun-12 Smith, John 5 70 1 90 1 126

Sometimes the data is in more than 3 rows, maybe up to 7 and there is up to 200 names on the list.
I am currently doing this manually one row at a time as a formula or macro is well beyond my knowledge, maybe not even possible.

Sorry to be a bit of a leech on the forum

Many thanks for any input/advice.

**arlu1201** · 07-04-2012, 07:03 AM

Do you have a sample file that you can upload?

**Lozhaze** · 07-04-2012, 08:18 AM

Sample file1.xls

Here is a sample of my data, thanks for looking

**nightcrawler-jay** · 07-04-2012, 09:01 AM

Hi,

Try this =CONCATENATE(A1," ",B1," ",C1," ",D1) you will just need to check your date formate as it pulls through as a number.

**Lozhaze** · 07-04-2012, 09:06 AM

Hi Jay, thanks for your input but that won't do the job i need.

**arlu1201** · 07-04-2012, 09:26 AM

What if the date is different but the name is the same?

**Lozhaze** · 07-04-2012, 09:32 AM

I should have said that the date is not really important, i could delete the date column before i start, sorry.

The figures are hours/days and pay rates respectively.

**arlu1201** · 07-04-2012, 09:44 AM

Try this code

Please Login or Register  to view this content.

If you have headings in row1, then change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

**Lozhaze** · 07-04-2012, 10:27 AM

That appears to work absolutely perfectly, fantastic knowledge and skills.

Thank you very much.

**arlu1201** · 07-04-2012, 10:33 AM

Thanks. Am glad it worked.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save