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
.
.

Open in new window



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!
scarlett_leeAsked:
Who is Participating?
 
Rory ArchibaldCommented:
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
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
scarlett_leeAuthor Commented:
Hehe...
0
 
scarlett_leeAuthor Commented:
The question was answered
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.