mvill12
asked on
Combine random lengths of rows from one spreadsheet and apply to several different rows to another spreadsheet
I have a two large spreadsheet that needs to be combined from one line to many lines.
On spreadsheet A, one line of data applies to one ID match. However the ID match has several subsets of data that also needs to apply on Spreadsheet B. Below is an example.
Spreadsheet A:
*Lease # Tract # Well Xref
L00029807025 1 315735001
L00029807025 1 315733000
L00029807025 1 315733002
L00029807027 1 315734001
L00029807027 1 315734002
L00029807028 1 315735000
L00029807028 1 315735001
Spreadsheet B
*Lease # Tract #
L00029807025 1
L00029807025 2
L00029807025 1
L00029807025 2
L00029807026 1
L00029807026 2
L00029807027 1
L00029807027 2
L00029807028 1
L00029807028 2
L00029807029 1
L00029807029 2
L00029807029 3
How the outcome should look like:
*Lease # Tract # WELL XREF
L00029807025 1 315735001
L00029807025 2 315735001
L00029807025 1 315733000
L00029807025 2 315733000
L00029807025 1 315733002
L00029807025 2 315733002
L00029807027 1 315734001
L00029807027 2 315734001
L00029807027 1 315734002
L00029807027 2 315734002
L00029807028 1 315735000
L00029807028 2 315735000
L00029807028 1 315735001
L00029807028 2 315735001
Points to remember:
*Not every value on spreadsheet B will be used
*The most important thing is that the lease and tract combo is hit on every line with the data. For example if lease Z has 3 tracts and is a match on spreadsheet A, all three tracts will have the same data line on the new spreadsheet.
I have the file example attached.
Thank you!
WELL-xref-example.xlsm
On spreadsheet A, one line of data applies to one ID match. However the ID match has several subsets of data that also needs to apply on Spreadsheet B. Below is an example.
Spreadsheet A:
*Lease # Tract # Well Xref
L00029807025 1 315735001
L00029807025 1 315733000
L00029807025 1 315733002
L00029807027 1 315734001
L00029807027 1 315734002
L00029807028 1 315735000
L00029807028 1 315735001
Spreadsheet B
*Lease # Tract #
L00029807025 1
L00029807025 2
L00029807025 1
L00029807025 2
L00029807026 1
L00029807026 2
L00029807027 1
L00029807027 2
L00029807028 1
L00029807028 2
L00029807029 1
L00029807029 2
L00029807029 3
How the outcome should look like:
*Lease # Tract # WELL XREF
L00029807025 1 315735001
L00029807025 2 315735001
L00029807025 1 315733000
L00029807025 2 315733000
L00029807025 1 315733002
L00029807025 2 315733002
L00029807027 1 315734001
L00029807027 2 315734001
L00029807027 1 315734002
L00029807027 2 315734002
L00029807028 1 315735000
L00029807028 2 315735000
L00029807028 1 315735001
L00029807028 2 315735001
Points to remember:
*Not every value on spreadsheet B will be used
*The most important thing is that the lease and tract combo is hit on every line with the data. For example if lease Z has 3 tracts and is a match on spreadsheet A, all three tracts will have the same data line on the new spreadsheet.
I have the file example attached.
Thank you!
WELL-xref-example.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again Jerry helped me out with this requested within record time. Thanks again Jerry!
The two tables are combined on the Final Results worksheet. Please review it carefully and verify this is the results you are anticipating. You can add or delete records from the two data tabs and recreate the results worksheet at any time by pressing the Blue update button. The VBA and SQL code are written specifically for this set of data. If you change or delete the column names then the system will likely stop working.
This workbook uses ADOX to create a temporary Access database, then loads the two data worksheets into Access tables. A SQL query is called to combine and create the results table in the Access database. Once that is complete the VBA updates the Querytable on the Excel FinalResults worksheet which reads the results table from Access and then finishes by deleting the temporary Access db.
Thanks,
Jerry
Q-28578502-WELL-Data.xlsm