combine every 2 rows (a record) into one row in Excel 2

[hu5]

Hi,

I copied a spreadsheet from a website, then pasted it in the Excel worksheet, it's a 10 rows table, but due to the key entry by the author, Excel made it 20 rows.
Is there a way to combine every 2 rows (a record) into one row in Excel? Thanks.

Hu

 

[KenWright]

Bit more detail as to what it looks like now would help - What needs combining etc. Do you just want two 10 column records to become one 20 column record?

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[kpetursson]

do the cells in the two rows in the same column have values?
You could write a macro to combine the values from the second row with the values in the first row.

Sub CombineCells()
Dim Range1 As Range
Dim Range2 As Range
Dim i As Integer
i = 1
Set Range1 = Range(ActiveCell.Address)
Set Range2 = Range1.Offset(1, 0)
For i = 1 To 10
Range1.Value = Range1.Value & Range2.Value
Set Range1 = Range1.Offset(0, 1)
Set Range2 = Range2.Offset(0, 1)
Next i
Range2.EntireRow.Delete
End Sub


Note that the entire second row will be deleted

Hope this helps.



Kevin Petursson

 

[KenWright]

Better yet, give us the website and tell us which table and we can see what we are dealing with.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[hu5]

Thank you all. It's a secure website requires login. But the example as follows:

The format in Excel after copy from the website looks like the following, one records split in two rows, all columns have values in them.


A B C
1 first record first record first record
2 first record first record first record
3 second record second record second record
4 second record second record second record


I am looking for the format below, one row for each record, how to combine them? Is there any simpler way beside writing macro?

A B C
1 first record first record first record
2 second record second record second record
3
4

 

[KenWright]

OK, just to be absolutely clear here:-

1) Is your data numeric or textual? - If it's numeric then the cells can be added, but if textual then they need to be concatenated.

If the data is textual then do you need any kind of separator. (It makes it a lot harder for us when you don't give any representative data to work with).

2) Have you tried Data / Get External Data, or rightclicking on the website and choosing Export to Microsoft Excel?

Should be no need for a macro.

Regards
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Assuming your data in A1:J20:-

To Sum data, then in L1 put the following and copy to L1:U10

=SUM(OFFSET(A$1,(ROW()-1)*2,0,2))

To conctenate data, then in L1 put the following and copy to L1:U10

=OFFSET(A$1,(ROW()-1)*2,0)&" "&OFFSET(A$1,((ROW()-1)*2)+1,0)


When done just copy L1:U10, paste special as values and then delete A1:J20.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

If it's numeric then just throw a pivottable over it

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

In which case you'd need some kind of identifier that married up the rows, eg in a new column

=CEILING(ROW(),2)/2

Regards
Ken........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[kpetursson]

assuming the data does not need to be added (numerically), just combined ie...
a b c
1 i fo a
2 ts r t
3 a it tw
4 t s o

using the Macro I gave above would produce this
a b c
1 its for at
2 at its two

If you need a seperator between the data in the second row, and the first row of the original, the macro can easily be changed.

If you want the data to appear on two line but in the same cell, that can be accomplished with the macro as well.

The macro would eliminate all manual changes of adding formuls, moving that data, deleting the rows...

Try it. You have nothing to lose (assuming you saved the spreadsheet befor you try anything!!!)

Have a good day.



Kevin Petursson

 

[hu5]

for Kevin,
Yes, your macro works, thanks very much. As I'm new with macro and would like to have more information. Although Each web page contains only 10 records, my files is as big as 15,000 records. I wish to cut 30,000 rows in half.
This macro only works when select a row, and two rows combine into one at a time. Does the macro can be set up doing the entire file all at once?


for Ken,
The real example listed below: I tried Data / Get External Data already.

A B C
1 MIL-STD-1325-83 09/27/1974 RAILCAR LOADING...
2 Not. of Validation (3/01/1989)
3
4

 

[BenRowe]

hu5, ken wright's formula does what, i think, you need: =OFFSET(A$1,(ROW()-1)*2,0)&" "&OFFSET(A$1,((ROW()-1)*2)+1,0)
copy it into D1 and then fill across and down

 

[hu5]

Ben,

The formula =OFFSET(A$1,(ROW()-1)*2,0)&" "&OFFSET(A$1,((ROW()-1)*2)+1,0)
works beautifully, but when I delete the original range A1:H20, I got #REF! error value in every cell range of A1:H20, and the new paste data are all gone. How do I fix this problem? Thanks.

Hu

 

[KenWright]

Ahhhh - now you didn't read the very last part of my note now did you

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[kpetursson]

hu5

Try this.

You can change the variables 'NumberOfColumns' and 'NumberOfRows' depending on your needs. It will then process as mony columns as you'd like in as many rows as you'd like.

Hope this helps.

Sub CombineCells()
'CombineCells Macro
'Written by Kevin Petursson
' Petursson Programming
'Date: February 18, 2005
Dim Range1 As Range
Dim Range2 As Range
Dim i As Integer
Dim x As Integer
Dim NumberOfColumns As Integer
Dim NumberOfRows As Integer
i = 1
x = 1
NumberOfColumns = 10
NumberOfRows = 10
Set Range1 = Range(ActiveCell.Address)
Set Range2 = Range1.Offset(1, 0)
For x = 1 To NumberOfRows
For i = 1 To NumberOfColumns
Range1.Value = Range1.Value & Range2.Value
Set Range1 = Range1.Offset(0, 1)
Set Range2 = Range2.Offset(0, 1)
Next i
Range2.EntireRow.Delete
Set Range1 = Range1.Offset(1, -NumberOfColumns)
Set Range2 = Range1.Offset(1, 0)
Next x
End Sub



Kevin Petursson

 

[KenWright]

Just a slight tweak on Kevin's code to save you having to manually set row/columns, albeit it does assume there is no other data in the sheet.

Also, the Integer Type is generally better declared as Long nowadays:-

http://msdn.microsoft.com/library/d...-us/modcore/html/decontheintegerdatatypes.asp

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[hu5]

Kevin

Thank you for the great code, it works with 10 rows even 20 rows, but when I change NumberOfRows = 20262, it ran a long time but didn't work at all.
I tried with 10 rows again, it's not working either. I closed file & reopened, still not working. macro is enabled. Do you know what could be the problem? Thanks.

Hu

 

[kpetursson]

About the olny thing you might try is changing the variable declarations from "... As Interger" to "... As Long". I would even go so far as to do a complete system shutdown before tring to run it again. I have found many times that simply rebooting will fix the problem 75% of the time.

I would expect it to run a long time at 20262.

Try adding this code...
Application.ScreenUpdating = False
Below this line...
Set Range2 = Range1.Offset(1, 0)

and this code...
Application.ScreenUpdating = True
Below this line...
Next x

This will stop the screen in excel from updating while the macro is running. I would still expect the macro to take a while to excute.

Kevin Petursson

 

[KenWright]

Dohhh - only just realised I never actually posted the tweak I alluded to in one of my earlier posts.

Anyway, what you can do is use the statusbar to tell you if the thing is still working or not, and if you add the following line

Application.statusbar = x

after the line in the macro that says

For x = 1 To NumberOfRows

and then at the very end of your code use

Application.statusbar = False

You will see a count on the bottom left of your screen in the status bar as it runs through the rows.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[hu5]

Thank you, Kevin and Ken, you've been very helpful and kind. The macro works now, must select the rows to be concatenated, and it will do the trick.

Hu