compare results in excel

Hello all,

I have a report(attached). It has two sheets. two sheets has one similar row i.e NTlogin. How can I use that row in two sheets and compare and get the results in 3rd sheet.
the third sheet will have the combination of data. Also the first sheet have multiple values.



Thanks all
test.xlsx
A SAsked:
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.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you are using Excel 2010 or later, you may use Power Query to get the data in desired format.

To do that, import Inventory and Users data into Power Query Editor and load them as Connection Only and then Merge these two queries on Common Field NTLogin to get the data onto a new sheet.

In the attached, you can see the output on Sheet1.
test.xlsx
0
A SAuthor Commented:
thanks,

what query should i use?
where ti use the query?

thanks
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What Excel version you are using?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

A SAuthor Commented:
2010
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You will have to download the Power Query from the following link and install it on your system.

https://www.microsoft.com/en-in/download/details.aspx?id=39379

Let me know once it is downloaded and installed, I will guide you through the steps required to get the desired output.
0
A SAuthor Commented:
I have downloaded it and installed it

Thanks
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Great!
Can you see the Power Query Tab on the ribbon?
0
A SAuthor Commented:
yes I do
0
A SAuthor Commented:
I clicked the powerquery tab on ribbon,then i clicked from file>and chose my excel sheet.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay. Follow what I say.

Step1.

1) On Inventory Sheet, click on any cell in the table.
2) Power Query Tab --> Click on From Table/Range. This will open the Power Query Editor along with the data.
3) Make sure the first row is the header row. If not, there will be a Table icon just above the row numbers, click the drop down and choose Use First Row as Header.
4) Now on Home Tab, expand the first button Close & Load and choose Close & Load To.
5) In the popped window, choose Only Create Connection and click OK to finish.

Step2.
Repeat the step1 with Sheet Users.

Step3.
1) On Power Query Tab, you will find option which says Merge or Merge Queries ( I am using Office 365 so not sure, you will have to find it). Click it.

2) In the opened window, from the first drop down, choose Table1 and click on column header NTLogin.

3) From the second drop down, choose the Table2 and clik on column heaer NTLogin.

4) From the Join Kind drop down, choose Inner (only matching rows) and click on OK.

5) The Power Query Editor will be opened again with the resultant data.

6) Right click on the header Computer (which you don't need) and choose Remove.

7) You will see a header called Table2 with two arrows, click it and uncheck the checkbox for Use original column name as prefix. and click OK.

8) You can rearrange the columns by grabbing the column header and moving them to their desired position.

9) Once done, click on Close & Load.

10) The resultant data will be exported to a new sheet.

Let me know if you have any doubt following the above steps.
0

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:
Remember, before clicking on Close & Load, you can rearrange the columns order and remove unwanted columns as well.
If you forget something, you can always edit the query which you will see on the right side of the sheet where data is exported. Hover your mouse over the query and choose Edit. The Power Query Editor will be opened again.
There you will find all the steps listed on right side along with a gear icon next to them. You can click the gear icon and there you can correct if you did something wrong.
It seems confusing but is actually easy once you get it properly.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Once all is done correctly, if you add more data on Inventory or Users Sheets, all you need is, go to the sheet where data was exported, right click in the table and choose Refresh and the Table will be updated automatically.
0
A SAuthor Commented:
Thanks subodh,
I am totally lost.
I followed the steps and I got error
Dta no value N/A
0
A SAuthor Commented:
DataFormat.Error: Invalid cell value '#N/A'.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please watch this demo video and make sure you did it exactly the same way.
MergeData.mp4
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As I told you, I am using Office 365 so the Tab will look different to you but you will find all the options with Power Query on 2010 also.
In Office 365, the Power Query is built in on Data Tab under Get & Transform Data.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Also, please do it first with the Sample file you uploaded in your post#1 so you would know that it works.
0
A SAuthor Commented:
Thanks I dont have merge option under data, it is uder power query.
When I select inventory and click on column>from table range>your video is selcting whole table.but in my case its opening data in query editor
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Which step you are referring to as per the steps I posted?
0
A SAuthor Commented:
the one selecting inventory sheet and select from range
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You mean Step1?
0
A SAuthor Commented:
step 2
0
A SAuthor Commented:
i clicked on a column on inventory sheet, in step 2 I have to click from  Click on From Table/Range. This will open the Power Query Editor along with the data. here its not like the video
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
When select a cell inside the data and click on From Table/Range, you get the following prompt.

Don't forget to check the checkbox for My table has headers as shown in the following image.

Create-Table-2018-04-29-01.51.08.jpg
0
A SAuthor Commented:
thanks alot for being patient and helping me out
0
A SAuthor Commented:
thats the thing I am not getting box for inventory sheet, but I am getting for users.
For inventory sheet, its opening quesry editor with data
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
When you click on From Table/Range, it will open Power Query Editor immediately if the data is already formatted as an Excel Table.

But that doesn't matter. All you need is to check, when the data is loaded into the Power Query, it looks fine with all the headers and the data below. Isn't this the case?
0
A SAuthor Commented:
Is there any way you can help me on team viewer, if you dont mind
0
A SAuthor Commented:
yes its look fine but not geeting the result we need
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, PM me your Team viewer ID and Password.
0
A SAuthor Commented:
Thanks, I messaged the Id and password
0
A SAuthor Commented:
thanks
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome A S!
0
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.