How to combine every alternate row into one row combined - Excel 2

[777ad777]

Hi,
If someone can suggest a formula I would be very grateful!
I have an excel sheet I am trying to edit with over 500 rows of entries. I couldn't get an CSV file so I have copied it from a Word document so it split up the information into 2 rows.
I need to combine every second row into the row of the one above but keep them in seperate columns.

This is an example of what I am working with;

I need it to look like this;

Please any suggested are appreciated.
Thank you!

 

[SkipVought]

Hi and welcome to Tek-Tips.

"I couldn't get an CSV file so I have copied it from a Word document so it split up the information into 2 rows."

Please upload your Word document. This may indeed be the key to solving your issue in a simple way.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You could try do build data on new sheet, using OFFSET to $A$1 of existing sheet, with 2*ROW()+adjustment as row shift and 0-3 as column shift.

combo

 

[777ad777]

In F3 you put =OFFSET(A$3,(ROW()-ROW(A$3))*2,).
In H3 put =OFFSET(A$4,(ROW()-ROW(A$3))*2,0). [note the change of the first 3 to 4]
These formulas should give you the first entry in a single row.
Then copy the formulas down for as many rows as you need.
When happy with the results you can copy the results of the formulas and Paste Special > Values. Then delete columns A to E

Elsewhere the above formula worked like a charm with a few adjustments.

I appreciate your reply to my query.

Thank you!

 

[SkipVought]

@777ad777, glad combo's suggestion provided a solution. You might want to hit the [blue]Great post![/blue] link since the little pink stars indicate a successful tip and give other members who browse the forum a flag to look for.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Gavona]

An non-formula alternative in case someone finds this post in the future might be to:
1. copy columns A to C:F
2. Select C1:F1, Insert, Shift Cells Down
3. Filter Column F within your data area to show only blank cells and delete these rows
If you have lots of rows then you may be better sorting the data so all the blank rows are together and then delete them




Gavin

 

[beadedbytes]

Hi, Skip,

Appreciate your help - always. A sample file is attached. Reach out if you have additional questions for me.

 

[SkipVought]

Faq68-5287
First you must NORMALIZE your data.

The result will be 3 tables with 3 columns. The data in the 3 columns can be appended into ONE TABLE with 3 columns.

From there you have the entire plethora of Excel features to analyze and report.

Let me know how its working for you.

If this ia an ongoing issue of nermalizing new data periodically, you may need some VBA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Beadedbytes, did you post in the wrong thread?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]