Moving multiple rows into one row (1 record has 4 rows)

Please help me, I'm trying to create a .cvs file from an exported excel report, but before I can import it to a database I'd like to have one row per record.

I've already omitted out the headers and footers blank spaces and unnecessary columns from the excel file and I needed to just combine multiple rows into one.

It currently looks like this:

ROW#(1), ID#(1), NAME(1)
ADDRESS(1), ZIP(1)
DOB(1), SOURCE(1)
ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2)
ADDRESS(2), ZIP(2)
DOB(2), SOURCE(2)
ITEM(2), CITY(2)
and so on..


I'd like to make it like

ROW#(1), ID#(1), NAME(1), ADDRESS(1), ZIP(1), DOB(1), SOURCE(1), ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2), ADDRESS(2), ZIP(2), DOB(2), SOURCE(2), ITEM(2), CITY(2)
and so on...

I can't figure out a macro or a way to do this automatically.

Thanks!

It can be done with an INDEX formula. Is your data all in one column, or do the commas indicate columns? Similarly, do your want the output in one column, or spread across 9 columns?

It's getting late here, but I'll take a look at it in the morning if no-one else has got back to you in the meantime.

Hope this helps.

Pete

Thanks Pete and Moo for the reply! I really appreciate it.

Yes Moo, that's the way it's formated however it has more data on each row, some cells are blank(no null constraint on the original report). I just posted a simplified one.
Il test out Moo's s solution tomorrow when I get back to work tomorrow.
I will re-make the file again, the original file does contain more columns an a lot of it has nulls and some of the cells have some information in it.

Pete, I want to retain all the columns from the rows that needs to be transferred and line it up and name the headers for import.

Thanks again guys!

Moo, unfortunately it's not formatted that way, I wanted to retain all cells because some of it doesn't have a not null constraint and can hold data.

Sample 4 row 1 record.xlsx

Here's a sample of the sheet.

Thanks

Your sample file is quite a bit different than your example in the opening post. Can you indicate what you want to end up with from that data.

Pete

Here you go Pete.

result 4 row 1 record.xlsx

Thanks!

I've applied the solution to Sheet2 of your sample file from post #5. You can put this formula in A1 of Sheet2:

=IF(INDEX(Sheet1!$A:$K,(ROWS($1:1)-1)*4+INT((COLUMNS($A:A)-1)/11)+1,1+MOD(COLUMNS($A:A)-1,11))="","",INDEX(Sheet1!$A:$K,(ROWS($1:1)-1)*4+INT((COLUMNS($A:A)-1)/11)+1,1+MOD(COLUMNS($A:A)-1,11)))

This can then be copied across to cell AR1 (4 * 11 fields per record potentially), and then that row of formulae copied down as far as you need to (I've copied to row 5).

I've also applied the same formats and column widths from your result file from post #7 - this applies mainly for dates.

If the data is in exactly the same format as your real file, you can use this as a template - just copy your real data into Sheet1, and then in Sheet2 you can copy the formulae down as far as necessary (i.e. to the row given by MAX(Sheet1!A:A) or beyond). You can fix the values of the formulae on Sheet2, and then you can delete Sheet1 and save the file with a different name, thus preserving the originals.

Hope this helps.

Pete

It works perfectly!
You are a wizard Pete!

I'l be using this as a template. There are different excel tables still with different column widths, I'l try to understand your code but it will take me a while.

Thank you very much!