Code for split table to 2 tables with Keys

Hello guys,
does anyone have a code that could split one worksheet (or table) into 2 worksheets (or tables) and "connect" them with Primary - Foreign Keys. Like in database tables.
Basicly copy the Contry column into a new sheet, remove duplicates, add a unique number column called CountryID and then create a new worksheet where we don't have Country column but the CountryID

For example, if I have:

Please Login or Register  to view this content.

Is there a code to create this 2 worksheets (or tables) like this

Countrys worksheet:

Please Login or Register  to view this content.

New Orders sheet with CountryID but without Country:

Please Login or Register  to view this content.

Hi dageci,

If you do an Advanced Filter on your first table and click the "Unique" box it will give you your second table, without repeated country names.

Then for your third table, if you use an Index(Match()) type of formula you can get the countryID values in the third column. I don't think VBA is needed to solve your problem..

Thanks MarvinP for your answer.
But I was thinking to add a button that would run a macro so that when a new data comes in, anyone (me, my wife or someone else) clicks on this button and the data gets transformed.
I there a solution with macro?

It can be done with PowerQuery. If you are interested, post a sample workbook and I'll show you how.

In the attached I did the following:

1. Loaded the data into PowerQuery (Get & Transform on the Data ribbon).
2. Created an index for the country codes and loaded the result of the query to Sheet 1 (this could be loaded to a separate tab if you wished).
3. Duplicated the first query and deleted all the steps, then merged with the original query, and loaded this to Sheet 1.

Make sure you enable content in the attached, then try adding another order to the source data table, then click on Refresh All (Data ribbon).

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L
1	OrderID	OrderDate	Country			Country	Index			OrderID	OrderDate	CountryID
2	10248	04.07.2015	France			France	1			10248	04/07/2015	1
3	10249	05.07.2015	Germany			Germany	2			10251	08/07/2015	1
4	10250	08.07.2015	Brazil			Brazil	3			10249	05/07/2015	2
5	10251	08.07.2015	France			Belgium	4			10250	08/07/2015	3
6	10252	09.07.2015	Belgium			Switzerland	5			10253	10/07/2015	3
7	10253	10.07.2015	Brazil			Bali	6			10252	09/07/2015	4
8	10254	11.07.2015	Switzerland							10254	11/07/2015	5
9	10255	12.07.2015	Switzerland							10255	12/07/2015	5
10	10256	26.07.2019	Bali							10256	26/07/2019	6

Sheet: Sheet1

Hey AliGW,
here is a simple workbook with 3 worksheets.

1. worksheet "OrdersWithCountryNames" is a light version of what the raw data looks, only a few important columns (Country)

2. worksheet "CountryWithID" that I now create by copy pasting the countries from the 1. worksheet, remove duplicates, add a new column (CountryID) and populate it with 1....n numbers

3. worksheet "OrdersWithCountryID" that I now create by copy paste the 1.worksheet, add a new column (CountryID) and populate it with vlookup, then copy paste only the values so that I can remove the Country column from this worksheet.

Hope that I did explain my current steps on how I how do it, but would like to automate this process. There are more columns like this one that I transform into a new workbook (table). But the steps should be the same.
Thanks for any help!

Did you see the solution I offered above?

I was typing when you put the post.
Thakns AliGW this looks like what I was searching for.
Thanks once again!

No worries. You can easily record a macro for the refresh and assign it to a button.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.