3 tables need to be sorted into one

[tenkre12]

Hello good people. I really need your help. I'm stuck with 3 tables each with over 200,000 rows. They need to be combined in one table. I honestly don't know how to explain it so i tried my best with attachment (please view 1.jpg)

(for some reason i can't upload image here, it says there is some mistake, so here it is http://postimg.org/image/dnbtmihqh/ )


To oversimplify it:

i have a code (text value not number) of a product and number of times it has been bought and sold per year

Lets say we have three tables

TABLE 1

Code----Product----2012 sold----2012 bought

br01-----name--------13 ----------- 25

TABLE 2

Code---Product---2013 sold----2013 bought

br01---- name ------2-------------1

TABLE 3

Code--Product---2014 sold---2014 bought

br01---name-------9------------2


What i need is this three tables combined into one:

Code---Product---2012 sold---2012 bought----2013 sold----2013 bought---2014 sold----2014 bought

br01----name-------13-----------25------------ 2 -------------1-----------9--------------2



the problem is not all products have been bought and sold in all three years. some were bought and sold only in one year, other in two years and some in all three years. Good thing is if a products was bought in 2012 it was also sold in 2012. In other words if a product was bought it was also sold in a same year. So for some products table can look like this

Code--Product--2012 sold--2012 bought--2013 sold--2013 bought--2014 sold--2014 bought

br04---name--------------------------------4----------11

Finally, those two products in one table:

Code---Product---2012 sold---2012 bought----2013 sold----2013 bought---2014 sold----2014 bought

br01----name-------13-----------25------------ 2 -------------1-----------9--------------2
br04----name-----------------------------------4-------------11

Please view picture i post in attachment it will be much clearer (i hope). I would greatly appriciate your help. The only other way to do this is the long long long road of filling those cells manually. and that equals suicide (kidding. mostly)

Thank you so much for even reading this madness

Best wishes

Thomas

[ben_hensel]

Welcome to the forum!

As a rule we prefer that people post example spreadsheets (an XLS(X) proper) instead of a screencap PNG or JPG. Well, that doesn't entirely apply to what you want.

First an important question:
Is the code value unique?

Is code-to-product a 1-1 relationship? That is, there is one-and-only-one "product" line for each individual "code" line.

If both of those are TRUE, then you can accomplish your need with a procedure like this:
Assuming that A1 = code header, B1 = product header, C1 = bought header, D1 = sold header, and the rows populate down from there.
Assuming that the tabs are named "Table 1", "Table 2", "Table 3".

1) create a new sheet for Table 4

2) creating composite code/product
2.1) copy the entire Code:Product ranges from Table 1 and paste them into the new sheet
2.2) copy/paste Code:Product for Table 2 onto the bottom
2.3) copy/paste Code:Product for Table 3 onto the bottom
2.4) Select the Code column; then use Data Ribbon => Data Tools panel => Remove Duplicates Button
That should create the A1:A30000 or however long with each unique code on its own row, with the appropriate "Product" description in B1:B30000 (or whatever) next to it, without any duplicates.

3) Populate the table with values using INDEX(MATCH)
Use lookup functions to populate the data from the original tables into the new Table 4.
3.1) C1 = "2012 bought"
C2 = IFERROR(INDEX('Table 1'!C:C, MATCH(A2, 'Table 1'A:A, 0)), 0)
3.2) D1 = "2012 bought"
C2 = IFERROR(INDEX('Table 1'!D:D, MATCH(A2, 'Table 1'A:A, 0)), 0)

3.3) E1 = "2013 bought"
E2 = IFERROR(INDEX('Table 2'!C:C, MATCH(A2, 'Table 2'A:A, 0)), 0)
3.4) F1 = "2013 bought"
F2 = IFERROR(INDEX('Table 2'!D:D, MATCH(A2, 'Table 2'A:A, 0)), 0)

3.1) G1 = "2014 bought"
G2 = IFERROR(INDEX('Table 3'!C:C, MATCH(A2, 'Table 3'A:A, 0)), 0)
3.2) H1 = "2014 bought"
H2 = IFERROR(INDEX('Table 3'!D:D, MATCH(A2, 'Table 3'A:A, 0)), 0)
Then double-click the bottom right corner of each cell C2:H2 (the icon will change to a cross when it's hovering over) to auto-fill all the way to the bottom of the table.

4) Flatten table
Select the whole "Table 4" and copy, "Paste Special: Values Only" (using right-click and selecting the dropdown or CTRL+ALT+V keyboard) to paste the lookup results over the top of the functions.



Does that fill your need?

[tenkre12]

Dear sir thank you for even reading this! Thank you so much!

First part - getting rid of duplicates worked FANTASTIC!! THANK YOU.

Im having problem with the formulas part. I'm using excel 2010. when i insert formula it always says it contains an error. What am i doing wrong?

Thank you so much for helping me

Thomas

[ben_hensel]

Well, looking at it, I left out a necessary syntax element:

...
3) Populate the table with values using INDEX(MATCH)
Use lookup functions to populate the data from the original tables into the new Table 4.
3.1) C1 = "2012 bought"
C2 = IFERROR(INDEX('Table 1'!C:C, MATCH(A2, 'Table 1'!A:A, 0)), 0)
3.2) D1 = "2012 bought"
C2 = IFERROR(INDEX('Table 1'!D:D, MATCH(A2, 'Table 1'!A:A, 0)), 0)
...

I left out the exclimation point on the range inside the MATCH functions.

That should remove the "The formula you typed contains an error" message box. Stick that exclimation point in and we should be good.

[tenkre12]

Dear Mr. Ben Hensel

What you did for me here I will never forget. I am not ashamed to admit that I cried a little after completing this task that I could never ever complete without your generous aid. You literally saved me from months of work. Let me say this again I beg you - YOU SAVED ME FROM MONTHS OF WORK, MONTHS OF CHORE, MONTHS OF PAIN, MONTHS OF MISERY. I work dead end job with dead end pay and my unpaid overtime would be endless nightmare of manual labor sorting those three tables into one. I know that I will never be able to return the favor. How could I help someone who posses such knowledge? Doing most basic things in excel is like hacking into pentagon for me. You don't know how does it move me to know that there is a person, with huge knowledge (and probably great job and well deserved pay i sincerely hope) who comes to a forum, and without asking anything in return, helps people like me, solving their endless tasks in couple of minutes. The fact that you just read the mess of my post is an honor for me sir.

Thank you from the bottom of my heart.

Nothing but best wishes

Sincerely

Tomislav "Thomas" Horvat

[ben_hensel]

Clicking the "* Add Reputation" button on the bottom left corner of the post that helped you is most of the thanks that any of us need.

And if your problem is solved, mark your thread [SOLVED] by:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved


If you have more excel questions... then by all means, come back to the forum.