Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've a list as shown in A and B columns, and I want to merge as shown in D:

Enter image description here

How can I do that?

Here, alphabets (a,b,c,d,e,f,g,h) are just used as place holders. What I really need in column D is A1,B1,A2,B2,A3,B3,A4,B4.

share|improve this question

5 Answers 5

A not-so-simple-to-maintain formula based solution is to use the following formula in D:

=INDEX($A$1:$B$5,QUOTIENT(ROW()+1,2),IF(MOD(ROW(),2)=0,2,1))

Let me add formatting and explain it in parts:

=INDEX(
   $A$1:$B$5,
   QUOTIENT(ROW()+1,2),
   IF(MOD(ROW(),2)=0,2,1)
   )

So, INDEX will return cell in a range by coordinates. Arguments are:

  1. $A$1:$B$5 - range, containing two columns needed.
  2. QUOTIENT(ROW()+1,2) - integer division of a current row number by 2. That gives row number in the range from (1).
  3. IF(MOD(ROW(),2)=0,2,1) - remainder of integer division from (2). That gives column number in the range from (1).

The solution is not really flexible, and slight improvements are needed to support:

  • More than two columns
  • Not-neighboring columns
  • Result in a specific range (for example, start from D5)
share|improve this answer
1  
Nice one, to make this really flexible you could change it to this: =INDEX( myrange, ROUNDUP((ROW()-StartRow+1)/NumOfCols,0)+IF(RangeIncHeaders="Yes",1,0), MOD(ROW()-StartRow,NumOfCols)+1 ) –  Andi Mohr Jan 21 '13 at 10:30
1  
If you set StartRow, NumOfCols and RangeIncHeaders as named range variables you can just copy and paste this formula down. –  Andi Mohr Jan 21 '13 at 10:32

Subtotal for each change in ColumnA, Filter and select the sub total rows (eg by looking for Count) blank these cells out, remove filter, move ColumnB down one row and then concatenate the two columns.

In more detail:

The requirement for interleaving would be easier to meet if every alternate row were blank. Subtotal already has the facility to add rows and can be used to achieve this. In the question, it appears as though each cell is different so a count at each change should create a subtotal for each entry. [If the data has adjacent repetition a ‘helper’ column can be used (say ColumnD here, since the helper column is only temporarily required). Say key 1 into D1 and drag fill handle down as far as required –row 4 in question – with Ctrl depressed.]

Subtotalling for each change (ColumnA if possible, otherwise ColumnD) inserts subtotals, for example:

SU539258 first example

With filtering the rows containing the subtotals can be selected (eg Text Filters, Contains, key "Count", OK) and blanked (the rows are required, but not their contents). Columns labels may be deleted at the same time if not required.

The middle small image shows that alternate blank rows have been achieved, whereupon the subtotals may be removed and a cell inserted at the top of ColumnB with shift down.

The original data is still intact (albeit now spaced out) and the ColumnD entries may be 'moved' from ColumnsA and B by concatenation, such as with the formula as in the right hand image (copied down as required). If the original data was text or values the formulae in ColumnD can then be replaced with Copy/Paste Special/Values.

There may well be better ways of achieving the result but that is difficult to tell without knowing what is in columns A and B (eg formulae [with or without relational references?], text or values?).

SU539258 second example

share|improve this answer
1  
Here, alphabets (a,b,c,d,e,f,g,h) are just used as place holders. What I really need in column D is A1,B1,A2,B2,A3,B3,A4,B4 –  claws Jan 21 '13 at 6:23
1  
You said a lot that I can't understand. –  claws Jan 21 '13 at 7:07

If you don't mind using a macro, here is an idea.

Sub MergeColumnsAlternating()
  Dim total, i, rowNum as Integer
  total = 4 '' whatever number of rows you need to merge.
  i = 1
  For rowNum = 1 to total
    Range("D" & i) = Range("A" & rowNum)
    i = i + 1
    Range("D" & i) = Range("B" & rowNum)
    i = i + 1
  Next rowNum
End Sub

I'm super rusty with my VBA (I barely remember it), I haven't used office in many years but wanted to contribute anyways.

share|improve this answer

A1 = a
B1 = b

What I understand is that in D1 you need a output as " ab " which is A1,B1. And then you need to have a formula as =A1&B1.

share|improve this answer

Assuming that you have columns as below in Excel,

   A1             B1
-------          --------
    1                 a
    2                 b
    3                 c

Then apply the formula in the C1 column, =INDEX($A:$B,CEILING(ROWS(C$1:C1)/2,1),2-MOD(ROWS(C$1:C1),2)). You will get your required result in c1.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.