[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Merge Data based on Index value and add column

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
shogun5
Asked:
shogun5
  • 3
  • 3
1 Solution
 
BusyMamaCommented:
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
 
mankowitzCommented:
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
 
shogun5Author Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
shogun5Author Commented:
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
 
BusyMamaCommented:
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
 
BusyMamaCommented:
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
 
shogun5Author Commented:
Worked like a charm! Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now