I've a list as shown in
B columns, and I want to merge as shown in
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:
Let me add formatting and explain it in parts:
The solution is not really flexible, and slight improvements are needed to support:
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
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?).
If you don't mind using a macro, here is an idea.
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:
Now you can replace the original formula with our new one that uses variables:
Now let's insert a third column. Change the range referenced in the formula to
As an example, let's also move our output down so it starts on row 5 instead of row 1. Set
Finally, you might want to be able to toggle row headers on and off. If so just set
A1 = a
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
Assuming that you have columns as below in Excel,
Then apply the formula in the C1 column,