mvill12
asked on
Applying values from Spreadsheet A to different repeating lines of data on Spreadsheet B with a common ID
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!
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!
ASKER
Phillip - how does it work it there is no match to spreadsheet B from spreadsheet A. I think I am a bit confused. Thanks!
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
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
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
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
ASKER
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
recording-example-1.xlsx
ASKER
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
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
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
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
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
recording-example-JP.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jerry was even nice enough to create a spreadsheet with the output!! Highly recommend Jerry for any of your questions.
ASKER
Hi Phillip - the numbers were still a bit off but thank you for helping me out.
1. What was incorrect with my solution?
2. You still haven't answered my question: