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.
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,