Help with merging two data sets to create a "complete picture"

[perks84]

I have two data sets from an ERP system that i need to merge to create a complete picture of a transaction series. I have been battling for a couple of weeks so am reaching out for help.

Data set 1 Data Set 2
WorkOrder SalesOrder Movement SLoc Pounds Work Order Sales Order Movement SLoc Pounds
711 "blank" 261 9998 260 711 "blank" 261 9998 -260
711 782 101 9999 -207 blank" 782 601 9999 -207
712 782 601 9999 -101

Desired data set
WorkOrder, SalesOrder Movement SLoc Pounds
711 782 261 9998 260
711 782 101 9999 -207
711 782 261 9998 -260
711 782 101 9999 207
711 782 601 9999 -207
712 782 601 9999 -101

 

[alansidman]

It is easier to work with a sample workbook instead of what you have provided. Suggest you upload to this site a small sample workbook.

 

[perks84]

Sample excel workbook below

It is easier to work with a sample workbook instead of what you have provided. Suggest you upload to this site a small sample workbook.

The challenge is also to replace the "blank" cells with the correct deduced numbers

Data Set 1 / Query 1
WorkOrder	Sales Order	Movement	Sloc	Pounds
711	"blank"	261	9998	-260
711	782	101	9999	207
Data Set 1 / Query 1
WorkOrder	Sales Order	Movement	Sloc	Pounds
711	"blank"	261	9998	-260
711	782	101	9999	207
"blank"	782	601	9999	-207
712	782	601	9999	-100
Merged Data Set / Merged Query
WorkOrder	Sales Order	Movement	Sloc	Pounds
711	782	261	9998	-260
711	782	101	9999	207
711	782	261	9998	-260
711	782	101	9999	207
711	782	601	9999	-207
712	782	601	9999	-100

 

[Pecoflyer]

Please upload a sample workbook. Click Go advanced - Manage attachments" - NO PICS PLEASE

 

[Bob Phillips]

The challenge is also to replace the "blank" cells with the correct deduced numbers

That data is not very helpful as there are only two WorkOrder numbers and all of the resulting sales Order numbers are the same. What is the rule for 'deducing' those numbers?

 

[AliGW]

For the THIRD time: please upload a sample WORKBOOK. Thanks.