[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Merge Data based on Index value and add column

Posted on 2011-09-02
7
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

649 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