Solved

Merge Data based on Index value and add column

Posted on 2011-09-02
7
275 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 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