Solved

Merge Data based on Index value and add column

Posted on 2011-09-02
7
254 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:shogun5
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Worked like a charm! Thanks!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

763 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

11 Experts available now in Live!

Get 1:1 Help Now