Combine multiple columns into one, not overwrite

[peson79]

Hi,
I've tried this forum once and it was a success so here we go again

I've have a work with customers and customer owners but they are in two sheet. Now I want to combine theese two sheets into a third sheet without overwriting each other.

So from Sheet 1
Column A: Customers
Column B: Customer owners

Sheet 2
Column A: Customers
Column B: Customer owners

Combine Customers from column A in Sheet 1 & 2 to Column A in Sheet 3 > Not overwriting each other.
Combine Customer owners from column B in Sheet 1 & 2 to Column B in Sheet 3 > Not overwriting each other.

Do I need VBA for this?, because thats beyond my knowledge

Im using Excel 2016

Thanks!

[samba_ravi]

If you have combine data from only 2 Sheets then can manage with formulas otherwise go with VBA

Please attach a sample excel file

[peson79]

Hi
Heres the attachement in stripped version.


BTW!
Of course with this little amount of data I can easily copy/paste and then sort. But, formula is so much more fun!

[sandy666]

is that what you want?

[peson79]

Hi Sandy,
yes, this is my first step. How did you do it, using the Consolidate button?

[sandy666]

Use Power (Query), Luke (Get&Transform)

[peson79]

ok,im not very familiar with that...

[sandy666]

ok, simply way

clik on first "table"
go to DATA - From table
in new window select Close and Load to - ... - as connection (it will be Table 1
do the same for second "table"

then go to DATA - Show Queries
right click on Table 1 - Append
in new window you'll see Table 1 selected then select Table 2
ok
as connection

then choose the place where you want new result table (sheet, cell)
in Queries windows right click on Append1 and select Load to, then select Table, and select cell, OK

edit:
remember, both source tables should have exactly the same headers

[peson79]

I was trying to follow this but now when I try to do your way I get this. Grrr....
Cant find any filter anywhere....

Expression.Error: We couldn't find an Excel table named 'Sheet1!_FilterDatabase'.
Details:
Sheet1!_FilterDatabase

[sandy666]

Filter???????
I didn't say anything about filter

EDIT:
Look at this one: PowerQuery - Append

[peson79]

Filter???????
I didn't say anything about filter

The file I uploaded was cleaned from corp data and there it works. But when I try the same in the original document I get this error, though I can’t find any filters.
I’ll look it up deeper when I’m back at my computer!

[sandy666]

So reflect structure of data from original file (with dummy data)
20-30 rows will be ok but with all columns

NB:
tables do not accept merged cells by default (structured table, pivot table or query table)
(Excel) Table - not range named table