Solved

Applying values from Spreadsheet A to different repeating lines of data on Spreadsheet B with a common ID

Posted on 2014-12-09
11
65 Views
Last Modified: 2014-12-10
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 #    Record Date      Record Ref.      
L00029807025      1      7/29/2013      1308275              
L00029807025      1      10/31/2013      1311967        
L00029807025      1      7/29/2013      1308276            
L00029807027      1      11/01/2013       1308001            
L00029807027      1      10/31/2013      1311967            
L00029807028      1      2/3/2014         1308000            
L00029807028      1      10/31/2013      1311967            


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 #   Record Date     Record Ref.      
L00029807025      1            7/29/2013             1308275      
L00029807025      2           7/29/2013             1308275      
L00029807025      1          10/31/2013             1311967          
L00029807025      2           10/31/2013             1311967          
L00029807025      1           7/29/2013           1308276              
L00029807025      2           7/29/2013           1308276              
L00029807027      1          11/01/2013           1308001              
L00029807027      2          11/01/2013           1308001              
L00029807027      1           10/31/2013          1311967      
L00029807027      2          10/31/2013          1311967      
L00029807028      1           2/3/2014         1308000              
L00029807028      2          2/3/2014         1308000              
L00029807028      1          10/31/2013          1311967              
L00029807028      2         10/31/2013          1311967              

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 files if anyone needs the exact data.

Thank you!
0
Comment
Question by:mvill12
  • 5
  • 3
  • 3
11 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488993
Continuation of http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28576378.html , where I posted the attached solution.

1. What was incorrect with my solution?
2. You still haven't answered my question:
Why do you have 3 lines labelled L00029807025 in A and 4 in B, but only 6 as the result?
0
 

Author Comment

by:mvill12
ID: 40489013
Phillip - how does it work it there is no match to spreadsheet B from spreadsheet A. I think I am a bit confused. Thanks!
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40489053
MVill12, in the attached:

1. If it is in A but not B, the I've added a column in A saying "No Match" (column H).
2. If it is in B but not A, then columns F-I in spreadsheet C will be blank.

I hope you will be less confused.
EE141209.xlsx
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 40489113
mvill12,

If you would include the source files that would be easier to work with.   Also, in worksheet B, is it necessary to have duplicate entries such as the four below.   Not knowing the source and meaning of the data perhaps they need to be there for a reason but if the duplicates could be eliminated then a simple SQL statement will combine the two files and produce the results you are looking for.

Lease #                Tract #  
L00029807025      1    
L00029807025      2    
L00029807025      1
L00029807025      2
0
 

Author Comment

by:mvill12
ID: 40489221
Phillip - here's the exact file. I tried running your example but it giving me errors. Please use the lease number to look up the tract value.
recording-example-1.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:mvill12
ID: 40489223
Jerry:

 Attached are the files with an example of how the final output should look like. Please use the lease number to look up the tract value.  Thanks!
recording-example-1.xlsx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40489339
You should have provided this exact file yesterday. I do have enough to do without having to rework my example again just because you have decided to provide more data.

It only gives you errors because you have changed the structure of the data by adding new columns, not because my answer was incorrect.

The answer for your data is attached. FINIS.
recording-example-1.xlsx
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 40489342
Please look over the Final Results worksheet in the attached workbook and let me know if this is the results you are expecting.
recording-example-JP.xlsx
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 40489422
Another version that will allow you to add / delete data from both tables and reproduce the output.   As noted, this is specific to the data you provided.   Adding / Deleting / Renaming columns of data may render it inoperable.

The VBA is written to work with the data provided.  

Thanks,
Jerrry
EE-Q-28577063-Combine-Tables.xlsm
0
 

Author Closing Comment

by:mvill12
ID: 40492615
Jerry was even nice enough to create a spreadsheet with the output!! Highly recommend Jerry for any of your questions.
0
 

Author Comment

by:mvill12
ID: 40492660
Hi Phillip - the numbers were still a bit off but thank you for helping me out.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

930 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

10 Experts available now in Live!

Get 1:1 Help Now