Solved

MS Excel combine 4 rows into one row without using macro

Posted on 2010-09-16
8
372 Views
Last Modified: 2012-05-10
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!
0
Comment
Question by:scarlett_lee
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33690286
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 65 total points
ID: 33690297
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33690343
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
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 60 total points
ID: 33690375
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 50

Expert Comment

by:teylyn
ID: 33690407
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
 
LVL 50

Expert Comment

by:teylyn
ID: 33690443
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 Comment

by:scarlett_lee
ID: 33690517
Hehe...
0
 

Author Closing Comment

by:scarlett_lee
ID: 33820103
The question was answered
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now