Solved

Merge Data based on Index value and add column

Posted on 2011-09-02
7
268 Views
Last Modified: 2012-05-12
Hello Experts!

I have data on two sheets. Both sheets have records with a key (indexed) value. I need to merge each row from MASTER FROM DATABASE sheet and MASTER9-1POS-Balances into sheet 3 to keep all values from the MASTER FROM DATABASE sheet and value in column C from the MASTER9-1POS-Balances sheet. I have over 2000 rows of data but the sample file (attached) only includes a few rows so you all have an idea what the file looks like. Please note that there are more records in the MASTER FROM DATABASE sheet so the rows not in the MASTER9-1POS-Balances sheet will create a blank or null value in the MASTER FROM DATABASE sheet.

Hope this makes sense.

Thanks! Need this pretty quick so offering maximum points.
Combined-Master-two-sheets---sam.xlsx
0
Comment
Question by:shogun5
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:BusyMama
ID: 36473053
Is it imperative that it go on Sheet3?

Is it OK to rearrange columns and sort on MASTER9-1POS-Balances?

If the answers to those questions are NO and YES respectively then ....

Move the index column on MASTER9-1POS-Balances to column A and sort it ascending.

Use a VLOOKUP function in column L of Master from Database to return the matching column C value.
=VLOOKUP(C12,'MASTER9-1POS-Balances'!$A$1:$F$4,4,FALSE)
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 36473072
also, are there any numbers in column c that exist on the master9 list that do not exist on the master list? If no, then the above solution is perfect. If there are, then you have to create a merged index first. Here is one method:

http://www.get-digital-help.com/2009/06/29/merge-two-columns-into-one-list-in-excel/
0
 

Author Comment

by:shogun5
ID: 36473121
BusyMama:

Thank you. I did what you suggested (see attached file) but get an error on the vlookup function. I placed the function in column L1 of the Master from Database.

Ideas?

thanks
Combined-Master-two-sheets---sam.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:shogun5
ID: 36473162
mankowitz:,

No. No numbers in column C that match. The idea is to find the matching index id number of Master from Database sheet and place the value from the respective row (indexed) from master9 to to the Master from Database sheet. Doing this manually would take forever but basically, I would create a new column in Master from Database sheet, find the matching index ID number from both sheets and place the column C value from the Master9 sheet and placed it into the new column and respective row of the Master from Database sheet.

I can try the vLookup but each workbook will have a different row count so would like to use a macro that uses row count and code that loops through search for a matching index id and inserts the value from Column to the appropriate cell on Master from Database.

I just don't know VBA well enought yet.
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36473790
In row 1, you are referring to C12 as the item to find ... change that to C1 and then copy the formula down the column.

The way the formula is currently set up, it will return #N/A if there is not a match on the second sheet.

Change to this if you would prefer a blank cell instead of #N/A:
=IFERROR(VLOOKUP(C1,'MASTER9-1POS-Balances'!$A$1:$F$4,4,FALSE),"")
0
 
LVL 7

Accepted Solution

by:
BusyMama earned 500 total points
ID: 36473805
Also, to address the different lengths of each worksheet change to:


=IFERROR(VLOOKUP(C1,'MASTER9-1POS-Balances'!$A:$F,4,FALSE),"")

That is referencing the entire columns A through F, instead of a set number of rows like I initially provided.
0
 

Author Closing Comment

by:shogun5
ID: 36474054
Worked like a charm! Thanks!
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

Suggested Solutions

Title # Comments Views Activity
ms office troubleshooting for users 8 36
sql server query from excel 3 57
Copy value from a certain cell 5 25
Get the last column in excel range 6 0
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

861 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

24 Experts available now in Live!

Get 1:1 Help Now