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

Stranger8421

New Member
Joined
Jun 30, 2022
Messages
23
Office Version
  1. 365
Hello All,

Hope you are well. I have three sheets of data that look like the below.
1. CustomerData
CustomerIdProductId
1​
112​
2​
113​
3​
115​
4​
117​

2. ProductData


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

3. ItemsData
ItemsIdItem nameItem 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.
CustomerIdProductIdIteam idIteam nameIteam 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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top