automate Excel data structure/view

Question

I have a simple list -

column A - "Project code" - "ABC", "XYZ

column B - "Job #"

how can I automate the organization of this data like the following?

ABC	XYZ
01	11
02	12

Answer 1

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Project code"}, {{"Count", each _[#"Job #"]}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[Count],#"Grouped Rows"[Project code])
in
    Custom1

Answer 2

Hi,

In cell D1, enter this formula

=LET(p,A2:A7,j,B2:B7,h,TOROW(UNIQUE(A2:A7)),VSTACK(h,IFNA(DROP(REDUCE("",h,LAMBDA(s,c,HSTACK(s,FILTER(j,p=c)))),,1),"")))

Hope this helps.

Answer 3

Hi @miguelalger,

Thank you for your prompt update.

If you want to make it more flexible, you can use the Power Query option. You can try using Power Query with these steps:

1- Create Power Query table

• Select all your data table > right click and choose Get Data from Table/Range > Check the option My table has headers > OK. This will open the Power Query editor.

• First step in Power Query editor, going to Add Column > Index Column > from 1

• Then select the Project code column and go to Transform > Pivot Column > set values column to Job# and advanced options as Don't aggregate.

• In the next interface, right click the Index column and click Remove. Then go to Home tab > Advanced editor > Delete all the M code in there and paste this instead:

let
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project code", type text}, {"Job #", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[#"Project code"]), "Project code", "Job #"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    ProjectCols = Table.ColumnNames(#"Removed Columns"),
    ListsNoNulls = List.Transform(ProjectCols, (c) => List.RemoveNulls(Table.Column(#"Removed Columns", c))),
    #"Compacted Columns" = Table.FromColumns(ListsNoNulls, ProjectCols)
 
in
    #"Compacted Columns"

• After pressing Done, all null cells in your table will be removed like this. The last step is selecting Close & Load to output the table

• With Power Query, it will update the table with every change in your original cells (add new rows, change data number, etc.) but you will need to go to Query > click Refresh to update, not always update on any changes automatically.

To understand more about using PowerQuery to pivot tables, you can refer to Pivot columns (Power Query) | Microsoft Support.

I hope this information is helpful. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you.

I look forward to your response.

Answer 4

Hi @miguelalger,

Thank you for posting your question in the Microsoft Q&A forum.

Please notify me if I've misunderstood anything about your request. Based on your description, you have the original data like this: Project code (ABC, XYZ) in column A and Job# (01, 02, 11, 12) in column B. Then you want to transform like this:

The easiest and quickest way is using TRANSPOSE and FILTER formula. You can try following these steps to see if this helps:

• First step is to create the column headers by using this formula: =TRANSPOSE(UNIQUE(A2:A5))

• After that, to fill the column with the job# code, you need to use this formula in cell E4: =FILTER($B$2:$B$5,$A$2:$A$5=E3). Then copy and paste the formula into cell F4.

Note: the data range in my formulas is based on my current test sheet. You will need to change the data range based on your working sheet to make sure the formulas work properly.

I hope the information provided proves useful. Please proceed with the outlined steps and let me know whether they resolve the issue. If not, I’ll be glad to continue working with you to find a solution.    

Thank you for your patience and understanding throughout this process. Should you have any questions or need further assistance, feel free to reach out at any time.    

I look forward to hearing from you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".          

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread