Consolidate Data in excel

New Contributor

While consolidate data from two different worksheets, after adding one data range, second worksheet is not opening for selecting second data range. First sheet coming up! how can I move to second sheet?

4 Replies
I think we need a bit more information in order to be able to answer. How are you doing this consolidation in the first place, for example? Are the two data ranges in the same workbook, different sheets, or different workbooks altogether? That kind of stuff. And if you can post copies of the spreadsheets--so long as they contain no confidential info--that too would help us help you.


Thanks for your response...

No, two data ranges in two different worksheets.

In attached 3 sheets, I want to consolidate data available in Mechanical and Electrical sheets into Consolidate sheet, where data against each TAG under Mechanical and Electrical sheets will come together and move on...

If you need any more data or input pl. revert

Consolidate works if you have values as it needs to do some operations like sum, multiply etc

in this case i suggest to use append feature in power query



So I can see that you have five unique Tag_Asset_Numbers in each sheet, Electrical and Mechanical, AND that they are identical. But you also have myriad hidden columns and lots and lots of rows in each sheet. So clearly the Tag_Asset_Number column can not serve as the sole basis for consolidating the data.


chahine has suggested Power Query (in my Mac I don't have access to that capability), which would be a way to do your consolidation. You'd need to know and specify the fields for the "join"


Without knowing the structure of your data tables, I can't make further suggestions. You need to give us a lot better description of the kind of consolidation you're expecting. And explain why so many columns are hidden while you're at it. My basic reaction is that you'd benefit from doing a LOT of data clean  up before attempting consolidation, but I also realize that may be because I don't know your data. Perhaps you intend for those empty columns to be filled with data from the other sheet?