Transpose data in excel

[khaledshahin]

Hi,

I need your help to change the format of Table#1 to Table#2

Table#1 (raw data)

Project Name	Staff Name
Project 1	John
Project 1	Jess
Project 1	Jack
Project 2	Ann
Project 2	Jess
Project 2	John
Project 2	Pete

Table#2 (Required format)

Project Name	Staff Name 1	Staff Name 2	Staff Name 3	Staff Name 4
Project 1	John	Jess	Jack
Project 2	Ann	Jess	John	Pete

Thank you in advance
KS

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Project Name	Staff Name		Project Name	Staff Name 1	Staff Name 2	Staff Name 3	Staff Name 4
2	Project 1	John		Project 1	John	Jess	Jack
3	Project 1	Jess		Project 2	Ann	Jess	John	Pete
4	Project 1	Jack
5	Project 2	Ann
6	Project 2	Jess
7	Project 2	John
8	Project 2	Pete
9
Data

Cell Formulas
Range	Formula
D1:H3	D1	=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),REDUCE(HSTACK(A1,B1&" "&SEQUENCE(,m)),u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:B100,A2:A100=y)),,m,""))))))
Dynamic array formulas.

 

[khaledshahin]

Thank you for your reply. It didn't work and says "function isn't valid". Any idea how to fix that?

 

[Fluff]

Are you using 365 as per your profile?

 

[alansidman]

An alternative with Power Query aka Get and Transform Data and found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Project Name"}, {{"Count", each _, type table [Project Name=text, Staff Name=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Project Name", "Staff Name", "Index"}, {"Project Name", "Staff Name", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Custom", {{"Index", each "Staff Name " & Text.From(_, "en-US"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Staff Name")
in
    #"Pivoted Column"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[jolivanes]

Assumes Columns 3 and higher are free to use. Result will be like Fluff's picture

Sub Maybe_So()
Dim c As Range, ac As Range, i As Long
Set ac = ActiveCell
Application.ScreenUpdating = False
    Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Cells(1, 4), Unique:=True
        For Each c In Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
            With Columns(1)
                .Range(.Find(c.Value, , , 1, , 1), .Find(c.Value, , , 1, , 2)).Offset(, 1).Copy
                c.Offset(, 1).PasteSpecial Transpose:=True
            End With
        Next c
    ac.Select
For i = 5 To Cells(1, 4).CurrentRegion.Columns.Count + 3
Cells(1, i).Value = "Staff Name " & i - 4
Next i
ac.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub