Solved

Combine random lengths of rows from one spreadsheet and apply to several different rows to another spreadsheet

Posted on 2014-12-10
3
52 Views
Last Modified: 2014-12-18
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
0
Comment
Question by:mvill12
  • 2
3 Comments
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 40492759
mvill12,

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
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 40493002
mvill12,

I believe I missed a column on the original attached file.   Please use this version #2 attachment.

Thanks,
Jerry
Q-28578502-WELL-Data-v2.xlsm
0
 

Author Closing Comment

by:mvill12
ID: 40507297
Again Jerry helped me out with this requested within record time. Thanks again Jerry!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now