Excel - Combine Data from Different Sheets into One Sheet

I have got data into 3 different sheets within one excel file. I want to combine the data from all 3 sheets into one final combined sheet.

The Formula should do a comparison based on the Symbol Column.

ex. If Symbol given in Cell A4 is present within [ Sheet A Column B ]
then pick up the values for [ Price % and Open Interest % ] from [ Column C and D respectively]
And put those values within the [ Column B and C ] in the current sheet.

If a particular symbol is NOT PRESENT within Sheet A, then just put a value of [ ZERO 0 ] in the current sheet, in front of that symbol.

In the exact same manner, just repeat the process for Sheet B and Sheet C as well.

I have attached a snapshot that shows the excel sheet and I have also attached the original excel sheet that has got all the data inside it.

Please suggest some way to do it.

Thanks a lot

Combine-Data-from-Different-Sheets-i.png
Combine-Data-from-Different-Sheets-.xlsx
happy 1001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try use vlookup function, like:

in B4 put formula:
=IFERROR( VLOOKUP(A4,'Sheet A'!B:D,2,FALSE), 0)

Open in new window

in C4 put formula:
=IFERROR( VLOOKUP(A4,'Sheet A'!B:D,3,FALSE), 0)

Open in new window


do similar formula for the rest of the cells..
Combine-Data-from-Different-Sheets_.xlsx
Murphy1992Commented:
This can be completed with a basic VLOOKUP.  The trick is to ensure you have symbol data column alphabetically sorted and there is only one entry for each symbol in each separate worksheet.

I've added a if error statement at the start of the VLOOKUP formula so a 0 can be applied if there is no matching data as you required.

The VLOOKUP function looks tricky but really isn't if you break it down to what it actually does.  
First define the cell you want to match to,
Second, highlight the data you want it to lookup all, ensure you have the criteria for Excel to match & the data you want to return
Third, nominate which column the data you want returned for a match is in. e.g. column 1 is the name column 2 is the price% etc
Fourth, nominate True for close match, False for exact match

You now simply need to format your combined data worksheet
Combine-Data-from-Different-Sheets-.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this....

In B4
=IFERROR(INDEX('Sheet A'!C$2:C$151,MATCH($A4,'Sheet A'!$B$2:$B$151,0)),0)

Open in new window

and then copy across to column C and down as far as required.

In E4
=IFERROR(INDEX('Sheet B'!C$2:C$151,MATCH($A4,'Sheet B'!$B$2:$B$151,0)),0)

Open in new window

and then copy across to column F and down as far as required.

In H4
=IFERROR(INDEX('Sheet C'!C$2:C$151,MATCH($A4,'Sheet C'!$B$2:$B$151,0)),0)

Open in new window

and then copy across to column I and down as far as required.

For details refer to the attached workbook.
Combine-Data-from-Different-Sheets-.xlsx
happy 1001Author Commented:
@Murphy1992
Thank you so much for the correct formula as well as for the explanation. Although I have one doubt. You have mentioned -
The trick is to ensure you have symbol data column alphabetically sorted and there is only one entry for each symbol in each separate worksheet.
but I think that it is not 100 % necessary to have the symbol data column alphabetically sorted, because we are doing the comparison with the symbol name from the Combined Sheet, and as long as the required symbol is present anywhere in the sheet being looked into, it will deliver the correct result. It will not make a difference if the list is alphabetically sorted or not. Please correct me, if this observation is wrong.

@Ryan Chong
Thank you for your soltion Ryan. That was really quick.

@sktneer
Thanks a lot for your effort sktneer.

I guess there is some issue with that formula. Please look at the data from H166 to I177 in the Combined Data sheet that you have provided. These cells are showing zero, whereas they should be showing the proper data. I do not know why is it not showing correct data for these cells, when it is  showing correct data for others.
Thanks anyway.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
My apologies.

I didn't checked the ranges on other sheets.

If you are interested in using the suggested formulas, please make changes in the range reference like below for all three formulas...

So after changing the range reference the formula in H4 would become like this...

=IFERROR(INDEX('Sheet C'!C:C,MATCH($A4,'Sheet C'!$B:$B,0)),0)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.