VBA code for copying and combining data from 2 sheets (joining 2 tables into a new one)

[mulmar74]

Hello,

I am looking for some help with my little challenge.

I am trying to combine data from 2 different sheets in an automated way, but I don't know VBA and macro well enough to get this to work without help.

I have 2 sheets and I want all the combinations from the to sheets to be copied to sheet3:

To make it easier to explain this if give an example of my situation (my actual tables are much bigger, but for explaining it this should be sufficient)
SHEET1
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]item[/TD]
[TD]price[/TD]
[TD]start[/TD]
[TD]end[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10[/TD]
[TD]1-1-17[/TD]
[TD]31-12-17[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]15[/TD]
[TD]1-1-17[/TD]
[TD]1-1-18[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]20[/TD]
[TD]1-3-17[/TD]
[TD]3-3-18[/TD]
[/TR]
</tbody>[/TABLE]

SHEET2
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]contact[/TD]
[TD]country[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]NameA[/TD]
[TD]NL[/TD]
[TD]12345678[/TD]
[/TR]
[TR]
[TD]NameB[/TD]
[TD]BE[/TD]
[TD]87654321[/TD]
[/TR]
[TR]
[TD]NameC[/TD]
[TD]FR[/TD]
[TD]45678123[/TD]
[/TR]
</tbody>[/TABLE]

SHEET3 (result)
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]item[/TD]
[TD]price[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]contact[/TD]
[TD]country[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10[/TD]
[TD]1-1-17[/TD]
[TD] 31-12-17[/TD]
[TD]NameA[/TD]
[TD]NL[/TD]
[TD]12345678[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]15[/TD]
[TD]1-1-17[/TD]
[TD]1-1-18[/TD]
[TD]NameA[/TD]
[TD]NL[/TD]
[TD]12345678[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]20[/TD]
[TD]1-3-17[/TD]
[TD]3-3-18[/TD]
[TD]NameA[/TD]
[TD]NL[/TD]
[TD]12345678[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10[/TD]
[TD]1-1-17[/TD]
[TD]31-12-17[/TD]
[TD]NameB[/TD]
[TD]BE[/TD]
[TD]87654321[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]15[/TD]
[TD]1-1-17[/TD]
[TD]1-1-18[/TD]
[TD]NameB[/TD]
[TD]BE[/TD]
[TD]87654321[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]20[/TD]
[TD]1-3-17[/TD]
[TD]3-3-18[/TD]
[TD]NameB[/TD]
[TD]BE[/TD]
[TD]87654321[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10[/TD]
[TD]1-1-17[/TD]
[TD]31-12-17[/TD]
[TD]NameC[/TD]
[TD]FR[/TD]
[TD]45678123[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]15[/TD]
[TD]1-1-17[/TD]
[TD]1-1-18[/TD]
[TD]NameC[/TD]
[TD]FR[/TD]
[TD]45678123[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]20[/TD]
[TD]1-3-17[/TD]
[TD]3-3-18[/TD]
[TD]NameC[/TD]
[TD]FR[/TD]
[TD]45678123[/TD]
[/TR]
</tbody>[/TABLE]

If there is a simple way to realize this without a macro? if not can somebody help me out with a macro for this?

Any help is very much appreciated.

Thanks,

Martin

 

[Fazza]

Welcome, Martin

this past thread is similar https://www.mrexcel.com/forum/excel...ble-combinations-two-lists-without-macro.html

a query can join the tables, without formulas & without macros

regards, Fazza