2

I would like to combine 2 columns into 1. One after the other.

I have this:

       A  B
Row 1: 1  10
Row 2: 2  11
Row 3: 3  12
Row 4: 4  13

And would like to obtain this:

       A
Row 1: 1
Row 2: 2
Row 3: 3
Row 4: 4
Row 5: 10
Row 6: 11
Row 7: 12
Row 8: 13

The number of rows in the 2 columns is different every time (number of rows in A and B is the same).

I tried many functions and I'm close but still not there. Would appreciate any help. I prefer formulas. I'm still a newbie in VBA but any help would be fine.

Thanks!

Community
  • 1
  • 1
Kuba Guzik
  • 23
  • 2
  • Can you show us what you've tried? There's a few different ways we could go about this, but writing code _for_ you isn't what this site is for. We'd be glad to help and write code _with_ you. Where are you stuck? – Tyeler Sep 22 '16 at 12:38
  • 1
    This post is (almost) an exact duplicate of a question posted merely one day ago [Excel VBA: find the union of two tables](http://stackoverflow.com/questions/39617785/excel-vba-find-the-union-of-two-tables). The only difference is that you want a `Union All` and not a `Union`. So, you can use the solution posted there and merely remove the line `.Range("C1", .Range("C1").End(xlDown)).RemoveDuplicates Array(1)` (which eliminates possible duplicates). That's it. – Ralph Sep 22 '16 at 13:12
  • @Ralph the two posts do have their similarities, but he does say that he prefers working with `Formulas` which surprisingly I couldn't find a duplicate of. I really thought this question would have been asked already but I can't find anything... – Tyeler Sep 22 '16 at 13:16

2 Answers2

2

Here is a formula that will do it.

Put it in C1 and copy down.

=IF(A1<>"",A1,INDEX(B:B,ROW()-COUNTA(A:A)))

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Awesome! I think it works. I'll check it precisely later and let you know! – Kuba Guzik Sep 22 '16 at 13:42
  • @KubaGuzik if it works for you let me know by clicking the check mark by the answer to mark as correct. – Scott Craner Sep 22 '16 at 13:43
  • Ok. will do...! – Kuba Guzik Sep 22 '16 at 13:45
  • I checked the formula and it is working perfectly fine. The only thing is it works only when done in 1 sheet. If I try to refer to another sheet, the formula does not work properly (not all data is referenced). If you have an idea why it happens I'll be very thankful. Nevertheless I managed to solve my problem so many thanks! – Kuba Guzik Sep 23 '16 at 08:26
  • Please post the actual formula you are trying. I am not clairvoyant. – Scott Craner Sep 23 '16 at 13:07
-1

Try this.

Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Range("A1").Select
sn152
  • 276
  • 1
  • 8
  • 2
    You might want to check [how to avoid using .select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). We don't know what kind of workbook is being used, how many sheets there are, where he'd want to call the sub from, etc. Using `.Select` is best to be avoided as much as possible. – Tyeler Sep 22 '16 at 12:50
  • I just answered his question. He needs to be more specific on his question about how many sheets are there. And about using .Select, yes I agree it is better to be avoided. But I just recorded a macro as his question was simple and not more specific – sn152 Sep 22 '16 at 12:58
  • You have to keep in mind that answers to questions on Stack Overflow are read by many people after the OP is done with it. If you feel he's provided us with a poor question, then [the site's guidance](http://stackoverflow.com/help/how-to-answer) tells us that it's better left unanswered. Providing a low quality answer to a low quality question doesn't help anyone and is likely to get downvotes. – Tyeler Sep 22 '16 at 13:13