A SQL query but in Excel and be done in VBA code

[ShuStar]

Hi Gurus,

I am trying to Join 2 tables together from 2 different sheets on Excel (but they are in the same workbook) - any idea how I can do this via VBA code?

I can do this on a basic SQL query which would look something like:
Select Columns A:F in Raw Data 2 sheet & Columns A:D in Matrix sheet
From Raw Data 2 sheet
Inner Join Matrix sheet
On Raw Data 2 ID (which is Column A) = Matrix ID (Column A)

For both sheets - A2 is column headers, and row 3 downwards are the data values for each column

How could I do this on Excel VBA where the data would be joined into a new tab called Consolidated Data? Any idea?

 

[ShuStar]

Hi all,

update:

Ultimately, via vba code, aiming to merge data from two tabs together, where:
Consolidated Tab - this is the tab where I would like to merge Matrix and Raw data 2 tab together
Raw data 2 tab - the ID is only here once (unique/distinct)
Matrix tab - the same ID can appear more than once

I don't think an index match would work here for me (unless anyone knows how maybe); it's probably not even the right sql query i mentioned above (Union is probably more appropriate rather than a inner join..) I am struggling to put this issue into words lol

An example sheet can be found in this link - it may help visualise this issue I am facing - Mapping Filter Doc

Many thanks to anyone who can support.

 

[ShuStar]

Ranges I am working with:

Ultimately, via vba code, aiming to merge data from two tabs together, where:
Consolidated Tab - this is the tab where I would like to merge Matrix and Raw data 2 tab together - plot data from A3 onwards/downwards
Raw data 2 tab - the ID is only here once (unique/distinct) - Range = A3:Fx (x being the last row of table set)
Matrix tab - the same ID can appear more than once - Range = A3:Dx (x being the last row of table set)

 

[ShuStar]

Hi all,

**Admins can delete posts #1,2,3 to declutter this thread as I have tried to re-summarise the ask as below***

I am trying to attach an image to help explain better what i am trying to achieve but it is too large. An example sheet can be found here - Mapping Filter Doc

Effectively, I'm trying to merge 2 worksheets in the same workbook on what would otherwise be a union function in SQL or MS Access (unless I am losing the plot)

I have looked at vlookup, index match, some Union vba code, Data Consolidation, Pivot Table (via Alt+D+P method) but none seem to be able to resolve.

Could do with trying to work out the VBA code to apply the below logic:
For each ID in Raw Data 2 Tab, match it to the IDs in Matrix Tab and plot the Range Columns & data into a new Tab called Consolidated (the columns to pull over have been noted below).

Ranges I am working with:
Raw data 2 tab - the ID for each row is here appears only once (ie ID is unique/distinct). Table Range = A3:Fx (x being the last row of table set)
Matrix tab - the same ID can appear more than once. Table Range = A3:Dx (x being the last row of table set). Note: if it helps knowing this in any way, in Matrix Tab: ID and Names columns combined is what would make the dataset unique/distinct here
Consolidated Tab - this is the tab where I would like to merge Matrix and Raw data 2 tab together - plot data from A3 onwards/downwards
*Bonus Point - If there is no match between Raw Data ID and Matrix ID - in the new Consolidated tab place comments in the Names column as "No Match"

Would be ever so grateful to anyone who can help me crack this.

 

[ShuStar]

Anyone?

 

[ShuStar]

Anyone at all?

 

[ShuStar]