Automatically pull data from excel table to another based on criteria

[Aladdin_Ajaj]

I'm working on excel table that has currency exchange data for multiple currencies and multiple filters to specify the the operations, I would like to keep the excel table as it is for daily use of data entries and create new excel table that automatically pull data with new arrangement.

The problem is in the main table all currencies shares same 3 columns for value but differentiate in rows data, I want to divide those 3 columns based on currency to 6 columns without affecting rows data (vertically values based on horizontally data).

• Main Excel Table

Main Table

• Automation table I want to create 

New table I want to create

As you can see in the main table I have name then currency then order (buy/sell) then type of order those steps distinguish the operations, then it comes to the main part I want to solve which are the 3 columns of values I want to automatically pull whether the operation is (buy/sell) with the data associated with (from the left and right) to new excel table.

I have been searching for a solution and most accurate one is using power query but I still don't have decent knowledge on how to use the M code or other tools it provide.

Hope I can get help from excel community.

[#2 · Accepted solution]

@Aladdin_Ajaj 

I would use Power Query as well. In the attached file you can enter data in the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse. Then select refresh.

The layout in the screenshot is for illustration. The green table can be draged to the right and then thousands of rows can be added to the blue table.

[#3]

This is just how I want! Thank you

The blue table is main dynamic table is that right?

and the green table I want in separate sheet, can you please tell me how I can do it?

[#4]

@Aladdin_Ajaj 

You are welcome. Yes the blue table is the main dynamic table and the green table is the result table. In the attached file the green table is on another sheet.

[#5]

Thank you

Can you tell me the steps to follow up with the way of creating such relation between the two tables?

[#6]

@Aladdin_Ajaj 

In the worksheet with the green table you can follow the steps shown in the screenshot to start the Power Query Editor.

In the Power Query Editor you can click on any of the applied steps and view the changes of the data.

Some of the applied steps have a gear icon on the right. An example is highlighted in the below screenshot. You can click on the gear icon to view the applied rule.

[#7]

Thank you for your precious details.

If I want to add columns for the blue table would it harm the syntax of green table?

Because I still need to add date, name, place, and notice columns that already associated with my original data

[#8]

@Aladdin_Ajaj 

You can add columns to the blue dynamic table as well. The syntax of the green table won't be harmed. Only the order of the columns could be different in the green table. This can be corrected with simple changes in the Editor.

In the attached file i added columns date, name, place and notice on the right side of the table. Within the Power Query Editor i've arranged the columns accordingly. After each refresh the order of the columns in both tables is the same.

[#9]

Thank you so much

I applied same steps you've done in the excel file and I managed to create what I wished for.

I appreciate your time and effort.

Thank you