Log In
Register
Home
Active Member
September 20, 2019
Offline
Hello to all,
I need some help.
I have table looking this way
A B C D E
Material name Machine 1 Machine 2 Machine 3 Machine 4
1 Material 1 prio1 prio 2
2 Material 2 prio1 prio 2
3 Material 3 prio1 prio 1
4 Material 4 prio1 prio2 prio3
5 Material 5 prio1 prio2 prio3 prio4
What I want to do, pull back in another table machine name sort by priorities
Prio1 Prio2 Prio3 Prio 4
Material 1 Machine 1 Machine3
Material 2 Machine 2 Machine 4
Material 3 Machine 2 Machine 4
Material 4 Machine 1 Machine 3 Machine 4
Material 5 Machine 1 Machine 2 Machine 3 Machine 4
Can somebody tell do i need code for this or some formula/function.
Bset regards
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
Hi Ivica,
It's much more easier in Power Query than vba, just Unpivot Prio columns then Pivot the new column created.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Material"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Value]), "Value", "Attribute")
in
#"Pivoted Column"
Active Member
September 20, 2019
Offline
Hi Catalin,
i do not understand where code should be inserted
One more thing i have table with 1000 rows with materials and 25 machines with assigned priorities.
So I was thinking to have cells where where I can insert material name and in other cells machine priorities
thanks for feedback
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
Hi Ivica,
It's a power query solution, it's in the sample file attached to my previous message All you have to do is to fill the table with your materials and priorities, then press refresh in the results table (right click-Refresh)
The file you attached is in a completely different structure than the one you provided initially so it brings confusion, not clarity.
You will have to try again and explain what you have and describe your process.
1 Guest(s)
