7

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.

7 Answers 7

8

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)
5
  • 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, 2013 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, 2013 at 10:32
  • @AndiMohr: can you give an example of this formula? i tried it, but had some errors. thanks
    – Marcel
    Mar 25, 2015 at 9:32
  • 1
    @AHC I'll post a new answer that explains how this would work.
    – Andi Mohr
    Mar 25, 2015 at 10:08
  • 1
    @AHC - here you go
    – Andi Mohr
    Mar 25, 2015 at 10:57
3

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.

0
3

Building on default locale's excellent answer, (and in response to AHC's request) you could add flexibility by defining some variables and adjusting the formula.

Let's start from default locale's result.

enter image description here

Unfortunately the formula used here will break if you have more than 2 columns, or if your output doesn't start on the same row as your range.

Let's define some variables to specify the row you want your output to start on, and the number of columns in your range.

enter image description here

The grey box shown above lists our variables. For cells G1, G2 & G3, name the range by clicking each cell in turn, then clicking into the box highlighted yellow. Type the relevant range name: StartRow, NumOfCols and RangeIncHeaders.

Now you can replace the original formula with our new one that uses variables:

=INDEX( $A$1:$B$4, ROUNDUP((ROW()-StartRow+1)/NumOfCols,0)+IF(RangeIncHeaders="Yes",1,0), MOD(ROW()-StartRow,NumOfCols)+1 )

enter image description here

Now let's insert a third column. Change the range referenced in the formula to $A$1:$C$4 to pick up the fact there are 3 columns. Set NumOfCols to 3 as well.

As an example, let's also move our output down so it starts on row 5 instead of row 1. Set StartRow to 5.

enter image description here

Finally, you might want to be able to toggle row headers on and off. If so just set RangeIncHeaders to Yes.

enter image description here

2

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

2
  • 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, 2013 at 6:23
  • 1
    You said a lot that I can't understand.
    – claws
    Jan 21, 2013 at 7:07
1

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.

1

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.

-1

=unique(transpose({transpose(unique(A:A)),transpose(B:B))})) This should serve the purpose !

1
  • 2
    How does it serve the purpose? What does it achieve that is better than other solutions? Please note that almost all of the other solutions provide explanation and detail in their answers, you should strive to do the same.
    – Mokubai
    Feb 3, 2018 at 10:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .