VBA: one to many relationship - 2 Columns of Data from different Sheets

[Stranger8421]

Hello All,

Hope you are well. I have three sheets of data that look like the below.
1. CustomerData

CustomerId	ProductId
1​	112​
2​	113​
3​	115​
4​	117​

2. ProductData

ProductId	ItemsId
112​	22​
112​	23​
113​	24​
113​	25​
113​	26​
113​	27​
115​	28​
115​	29​
115​	30​
117​	34​

3. ItemsData

ItemsId	Item name	Item cost
22​	potatoes	120​
23​	tomatoes	135​
24​	onions	169​
25​	carrots	150​
26​	bell peppers	270​
27​	broccoli	290​
28​	cucumbers	10​
29​	salad mix	20​
30​	Jack-Fruit	45​
34​	Water	10​

So Each sheets have relations between them Like ProductId column from CustomerData sheet is matching with ProductId column from ProductData sheet then likewise Item details also.
I'd like the final table to look like this.

CustomerId	ProductId	Iteam id	Iteam name	Iteam Price
1​	112​	22​	potatoes	120​
1​	112​	23​	tomatoes	135​
2​	113​	24​	onions	169​
2​	113​	25​	carrots	150​
2​	113​	26​	bell peppers	270​
2​	113​	27​	broccoli	290​
3​	115​	28​	cucumbers	10​
3​	115​	29​	salad mix	20​
3​	115​	30​	Jack-Fruit	45​
4​	117​	34​	water	10​

I believe this should be fairly easy to do, but I don't think I'm phrasing it to google right as it isn't returning as result. Could you Please help me if you know of one that is already out there.

Thanks and Regards,
Joe

 

[bobsan42]

it is easily done with PowerQuery (or GetData) if your office version supports it - Office 365 does. No VBA is needed in this case.
Format your data as TableObjects with Headers.
Goto Data tab > Combine Queries > Merge ...
Select the tables to Merge, Select the Join Field (matching fields) and the Join Kind.
Expand or Aggregate the second table in the PowerQuery Editor.
Save and Load ...

Plenty can be found on the net about working with PoqwerQuery.

 

[Stranger8421]

it is easily done with PowerQuery (or GetData) if your office version supports it - Office 365 does. No VBA is needed in this case.
Format your data as TableObjects with Headers.
Goto Data tab > Combine Queries > Merge ...
Select the tables to Merge, Select the Join Field (matching fields) and the Join Kind.
Expand or Aggregate the second table in the PowerQuery Editor.
Save and Load ...

Plenty can be found on the net about working with PoqwerQuery.

it is easily done with PowerQuery (or GetData) if your office version supports it - Office 365 does. No VBA is needed in this case.
Format your data as TableObjects with Headers.
Goto Data tab > Combine Queries > Merge ...
Select the tables to Merge, Select the Join Field (matching fields) and the Join Kind.
Expand or Aggregate the second table in the PowerQuery Editor.
Save and Load ...

Plenty can be found on the net about working with PoqwerQuery.

Hi Bob,

Thanks for replying to my question. Actually, I need to automate this task In one Excel I need to import another excel sheet and then do this task.
Actually, I wrote an import VBA script. Now I need to write VBA /anything which needs to do the automatic task to get final results by matching values from each table. Is it Possible?

Thanks and Regards,
Joe