I've a list as shown in A
and B
columns, and I want to merge as shown in D
:
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.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI've a list as shown in A
and B
columns, and I want to merge as shown in D
:
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.
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:
$A$1:$B$5
- range, containing two columns needed.QUOTIENT(ROW()+1,2)
- integer division of a current row number by 2. That gives row number in the range from (1).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:
=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
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
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.
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.
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.
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 )
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.
Finally, you might want to be able to toggle row headers on and off. If so just set RangeIncHeaders
to Yes
.
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:
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?).
column D
is A1,B1,A2,B2,A3,B3,A4,B4
– claws
Jan 21 '13 at 6:23
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
.
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.
=unique(transpose({transpose(unique(A:A)),transpose(B:B))})) This should serve the purpose !