# MS Excel combine 4 rows into one row without using macro

I need to combine 4 rows into one row. Like below.
``````1
2
3
4
1
2
3
4
.
.

combine to

1234
1234
1234
1234
.
.
``````

I know how to write a macro to do this. But my fuzzy end user doesn't like to use macro.
So is there some other way to do this, like using a few button clicks, or text import wizard, or formulas?

Thanks!
###### Who is Participating?

Commented:
Assuming your data starts in A1, in B1 enter:
=INDEX(A:A,(ROW()-1)*4+1)&INDEX(A:A,(ROW()-1)*4+2)&INDEX(A:A,(ROW()-1)*4+3)&INDEX(A:A,(ROW()-1)*4+4)
and copy down as far as necessary.
0

in a word ... no!

Even if for example trying to use filters, cut, paste , row insertion/delete and concatenation it would be a lot of work and highly specialised in terms of there is no generic functions to help with doing it.

Chris
0

Oh phooeey - I was gobsmacked that Rory posted so went to see how it could work - I really really need to read the question .. I thought it was 11 22 33 44 to gather the like rows.

Thankfully there are others who are awake!

Chris
0

Microsoft MVP ExcelCommented:
here is my version. Took me a bit longer than rorya to work it out, but it also works on my data sample

=INDEX(A:A,ROW()*4-3)&INDEX(A:A,ROW()*4-2)&INDEX(A:A,ROW()*4-1)&INDEX(A:A,ROW()*4)

cheers, teylyn
0

Microsoft MVP ExcelCommented:
Chris, I thought at this time of day the UK should be wide awake. At least, when I feel like keeling over, down under, you are in the middle of your productive phase, no? (Unless you're moonlighting at EE of course).

cheers, teylyn
0

Microsoft MVP ExcelCommented:
I like the concept of "fuzzy users". Reads like you can just wipe them away, like a piece of irritating fluff. They're not really there. You can only see them if you concentrate hard. If you don't concentrate, you'll look straight through them.

:-))

Sorry, scarlett lee, but this was too tempting to pass by.

I hope with the tips you get here at EE, you'll be able to satisfy your fussy users and get lots of warm, fuzzy feelings back from them.

cheers, teylyn
0

Author Commented:
Hehe...
0

Author Commented: