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