Append Value from 3 worksheet to the Right of a New Worksheet

[airforceone]

Need help again mate.... hopefully my last hurdle to finishing my project (hope someone notice)

can anyone nudge me to the correct path
anyway what I'm trying to achieve is append records from 3 sheets into a new one (see sample table).
Copy product sheet to a new sheet then test for Buyer with 2 conditions (Control Number and Product Description) if condition is met then append buyer record to the last column corresponding control number and product description same with seller

UPLOAD.xlsx
	A	B	C
1	CONTROL NUMBER	DATE	PROD DESC
2	A0001	2017.02.16	Apple - Green
3	A0001	2017.02.16	Apple - Red
4	A0001	2017.02.16	Apple - Black
5	A0002	2017.10.13	Mango
6	A0003	2019.10.30	Buko
7	A0004	2014.02.05	Blueberries
8	A0005	2014.02.05	Avocado
9	A0006	2013.06.19	Papaya
10	A0007	2011.04.01	Lychee
11	A0008	2011.05.30	Pineapple
12	A0009	2022.04.28	Durian
13	A0010	2022.05.24	Orange - Small
14	A0010	2022.05.24	Orange - Large
15	A0010	2022.05.24	Orange - Medium
PRODUCT

UPLOAD.xlsx
	A	B	C	D
1	CONTROL NUMBER	DATE COMMITTED	PROD DESC	SELLER LAST NAME
2	A0001	2017.02.16	Apple - Green	NOVEMBER
3	A0002	2017.10.13	Mango	LIMA
4	A0003	2019.10.30	Buko	PAPA
5	A0004	2014.02.05	Blueberries	ROMEO
6	A0005	2014.02.05	Avocado	CHARLIE
7	A0006	2013.06.19	Papaya	TWITTER
8	A0007	2011.04.01	Lychee	GAMA
9	A0008	2011.05.30	Pineapple	AMAZON
10	A0009	2022.04.28	Durian	DOE
11	A0010	2022.05.24	Orange - Small	PETER
12	A0010	2022.05.24	Orange - Large	COM
13	A0010	2022.05.24	Orange - Medium	CAMI
SELLER

UPLOAD.xlsx
	A	B	C	D
1	CONTROL NUMBER	DATE COMMITTED	PROD DESC	BUYER LAST NAME
2	A0001	2017.02.16	Apple - Green	EBAY
3	A0001	2017.02.16	Apple - Red	ZETA
4	A0001	2017.02.16	Apple - Black	MIKE
5	A0002	2017.10.13	Mango	ECHO
6	A0003	2019.10.30	Buko	HERNANDEZ
7	A0004	2014.02.05	Blueberries	ROMEO
8	A0005	2014.02.05	Avocado	NOVEMBER
9	A0006	2013.06.19	Papaya	ROMEO
10	A0007	2011.04.01	Lychee	EBAY
11	A0008	2011.05.30	Pineapple	BRABO
12	A0009	2022.04.28	Durian	LIMA
13	A0010	2022.05.24	Orange - Small	PAPA
14	A0010	2022.05.24	Orange - Large	CHARLIE
15	A0010	2022.05.24	Orange - Medium	Pero
BUYER

UPLOAD.xlsx
	A	B	C	D	E
1	CONTROL NUMBER	DATE	PROD DESC	SELLER LAST NAME	BUYER LAST NAME
2	A0001	2017.02.16	Apple - Green	NOVEMBER	EBAY
3	A0001	2017.02.16	Apple - Red	NOVEMBER	ZETA
4	A0001	2017.02.16	Apple - Black	NOVEMBER	MIKE
5	A0002	2017.10.13	Mango	LIMA	ECHO
6	A0003	2019.10.30	Buko	PAPA	HERNANDEZ
7	A0004	2014.02.05	Blueberries	ROMEO	ROMEO
8	A0005	2014.02.05	Avocado	CHARLIE	NOVEMBER
9	A0006	2013.06.19	Papaya	TWITTER	ROMEO
10	A0007	2011.04.01	Lychee	GAMA	EBAY
11	A0008	2011.05.30	Pineapple	AMAZON	BRABO
12	A0009	2022.04.28	Durian	DOE	LIMA
13	A0010	2022.05.24	Orange - Small	PETER	PAPA
14	A0010	2022.05.24	Orange - Large	COM	CHARLIE
15	A0010	2022.05.24	Orange - Medium	CAMI	Pero
OUTPUT

 

[kevin9999]

Thanks for that. OK, I ran the code on your shared file, and it ran perfectly. Link to the file (with code added) below:
2023.10.09 - Mr.Excel Code checked.xlsm

 

[airforceone]

Thanks for that. OK, I ran the code on your shared file, and it ran perfectly. Link to the file (with code added) below:
2023.10.09 - Mr.Excel Code checked.xlsm

sorry mate code does not work I don't know what went wrong is it my file or what, but sharing my SS now...

 

[kevin9999]

Thanks for letting me know. Looks like you've got a solution anyway, so this will remain a mystery

 

[airforceone]

Thanks for letting me know. Looks like you've got a solution anyway, so this will remain a mystery

looks like it mate, but thanks anyway

 

[bebo021999]

your code works great! but hurts my brain so painfully
is there a way to scale the code a bit? i still have a few columns/fields that should be included in the output....

Nice to heat it works.
My VBA code will iterate through each row in the PRODUCT sheet, creating a dictionary with a key formed by concatenating the values of the three columns A|B|C, for example:

Key: A0001|2017.10.13|Mango

and

Item: "|" (a "|" with left (for SELLER) and right (for BUYER) is blank initial)

Then, it will iterate through each row in the SELLER/BUYER sheet, find the corresponding key in the dictionary, and then put the SELLER on the left and the BUYER on the right. For example:

SELLER sheet: row 2, (key: A0001|2017.10.13|Mango) will take the item and add it to the left: "LIMA|"
BUYER sheet, row 4, (key: A0001|2017.10.13|Mango), will take the item and add it to the right, becoming: "LIMA|ECHO"

So, the dictionary will include:
...
...
... Key: A0001|2017.10.13|Mango, and Item: "LIMA|ECHO"
...

Then, iterate through each key, split it into corresponding cells to get the final result.

Hope it clear for you.

 

[Alex Blakenburg]

sorry mate code does not work I don't know what went wrong is it my file or what, but sharing my SS now...View attachment 99969

Try changing Formula2R1C1 to just FormulaR1C1

 

[airforceone]

Try changing Formula2R1C1 to just FormulaR1C1

tried it mate, though it seems to work but the result is somewhat cryptic SS included...

 

[airforceone]

Nice to heat it works.
My VBA code will iterate through each row in the PRODUCT sheet, creating a dictionary with a key formed by concatenating the values of the three columns A|B|C, for example:

Key: A0001|2017.10.13|Mango

and

Item: "|" (a "|" with left (for SELLER) and right (for BUYER) is blank initial)

Then, it will iterate through each row in the SELLER/BUYER sheet, find the corresponding key in the dictionary, and then put the SELLER on the left and the BUYER on the right. For example:

SELLER sheet: row 2, (key: A0001|2017.10.13|Mango) will take the item and add it to the left: "LIMA|"
BUYER sheet, row 4, (key: A0001|2017.10.13|Mango), will take the item and add it to the right, becoming: "LIMA|ECHO"

So, the dictionary will include:
...
...
... Key: A0001|2017.10.13|Mango, and Item: "LIMA|ECHO"
...

Then, iterate through each key, split it into corresponding cells to get the final result.

Hope it clear for you.

thanks mate, will try to study it later cheers...