IT

ALL IT Technology Information

Excel join data

 Introduction to the combine sheets problem

- New file to consolidate the data - Updating "power query" steps - Cleaning up the combined data - Loading data to Excel - Testing with new data - More Power Query

1st project (how to join multiple data sheet in merge in one file )

combine sheet all month and show data in one excel file 

  open a new file and go to 

        Data > Get Data > From File > From Workbook 

        select you file and insert  


  and Select any one sheet to open data file


 and show right hand side APPLIED STEPS 

Remove all APPLIED STEPS  

 

and show in more table data and select you need data table and remove other data 

select columns press ctrl to select and right click and remove other columns

then click data expend data button to show your all columns , you need columns select and ok


next you filter data you need data only show to filter data null value click out 


next your first header value row is create headers 

select first header then click (use first row as headers ) your row is create header 

       and your data filter because your like 5 table join to 5 times show your header to you filter data and remove check out header value ok

 

then your data join columns to (name or last name )to select 2 columns and right click merge columns 

next space in name or last name then ok                          

                 next space in name or last name then ok  

                New column name you type (NAME )



 last close & load  your data click to show your data :)


==========

2ed project 

1st file = product name ,data , how many box in stock

2ed file = product name , per box price

to your work count what is amount product

=====

type to use first but in sum error your 2 file always open then your data calculation 

=see your data in 2 different excel file to use merge data and calculation 

amount fine to use x lookup formula
XLOOKUP FORMULAS RULE = 
=XLOOKUP(FILTER$ 1 VALUE ,[WITCH FILTER$ NAME TO  ],[TO VALUE FINE SAME NAME]
TO USE AUTO FINED NAME TO FILTER NAME AND THIS VALUE 

=XLOOKUP(D5,[2ed sheet name columns select],[2ed sheet precise columns select ])*F5)

D5 = product name 

F5 = Boxes value 




BUT YOUR 2ed    FILE IS CLOSE TO ERROR BECAUSE THIS TYPE IS SUM ERROR TO TRY 2ed TYPE TO CREATE THIS DATA  (YOUR 2ed FILE OPEN TO WORK THIS FORMULAS) 

===

2ed TYPE WORK :)

FIRS TO GO DATA >GET DATA >FROM DATA > FROM WORKBOOK

NEXT YOU NEED DATA TABLE TO SELECT TABLE AND INSERT DATA

TO SHOW 2 TAB IN YOUR 2ed FILE DATA TABLE 

AND YOU CREATE COLUMNS AMOUNT 

AND TYPE FORMULAS 

=F5*XLOOKUP(D5,prices[product],prices[price per box])

                                         

 

To change your amount file to auto update this file only refresh data table like this 
 
power by chandoo




2 comments:

  1. Great and supporting culture for employees. Management is really super active and think about their employees, so as of now none.

    Amazing salary and perks for all the employees, there are great work ethics. You will not be disappointed with your decision of working for this company. There is no partiality at all

    ReplyDelete
  2. I think its good to do.I want a most trustable job because i need some amount.I am very happy to do this job .It is useful to students and poor people .
    The people are so much helpful here, you never feel like you are a junior or senior. Everyone is there for one another and the management is transparent too.PROS - The hierarchy is flat.CONS - It should increase work locations in India.

    ReplyDelete