SOLVED

How to transpose table?

%3CLINGO-SUB%20id%3D%22lingo-sub-1775002%22%20slang%3D%22en-US%22%3EHow%20to%20transpose%20table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1775002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CEM%3EMy%20data%20is%20as%20below%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3Eandrea.fitzgeraldBHAVESH.PARMARCHLOE.SCOTTCHRIS.CARTLEDGECLARE.CAUSEY%3C%2FEM%3E%3C%2FP%3E%3CTABLE%20width%3D%22450px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22194px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E01%2F10%2F20%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E02%2F10%2F20%3C%2FTD%3E%3CTD%20width%3D%2276px%22%20height%3D%2230px%22%3E03%2F10%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22194px%22%20height%3D%2230px%22%3EName1%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E33%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E44%3C%2FTD%3E%3CTD%20width%3D%2276px%22%20height%3D%2230px%22%3E34%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22194px%22%20height%3D%2230px%22%3EName2%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2276px%22%20height%3D%2230px%22%3E12%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22194px%22%20height%3D%2230px%22%3EName3%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E37%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E28%3C%2FTD%3E%3CTD%20width%3D%2276px%22%20height%3D%2230px%22%3E24%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EAnd%20I%20want%20to%20convert%20it%20to%20the%20following%20layout%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22372px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22192px%22%20height%3D%2257px%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2257px%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2257px%22%3EOrders%20Created%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22192px%22%20height%3D%2230px%22%3EName1%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E01%2F10%2F20%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E33%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22192px%22%20height%3D%2230px%22%3EName2%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E01%2F10%2F20%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22192px%22%20height%3D%2230px%22%3EName3%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E01%2F10%2F20%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E37%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22192px%22%20height%3D%2230px%22%3EName1%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E02%2F10%2F20%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E44%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22192px%22%20height%3D%2230px%22%3EName2%3C%2FTD%3E%3CTD%20width%3D%2279px%22%20height%3D%2230px%22%3E02%2F10%2F20%3C%2FTD%3E%3CTD%20width%3D%22100px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20I%20go%20about%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3CBR%20%2F%3EMorgan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1775002%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1775153%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20transpose%20table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1775153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F789072%22%20target%3D%22_blank%22%3E%40morgancampbell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20the%20number%20of%20filled-in%20date%20columns%20fixed%3F%20Or%20does%20it%20vary%20by%20name%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1775176%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20transpose%20table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1775176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20number%20of%20columns%20are%20fixed%20for%20all%20names%2C%20regardless%20of%20data%2C%20so%20some%20of%20the%20cells%20are%200.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

My data is as below

 01/10/2002/10/2003/10/20
Name1334434
Name23012
Name3372824

 

 

And I want to convert it to the following layout:

 

NameDateOrders Created
Name101/10/2033
Name201/10/203
Name301/10/2037
Name102/10/2044
Name202/10/200

 

How would I go about this?

 

Thanks,
Morgan

3 Replies
Highlighted

@morgancampbell 

Is the number of filled-in date columns fixed? Or does it vary by name?

@Hans Vogelaar 

 

The number of columns are fixed for all names, regardless of data, so some of the cells are 0.

Highlighted
Best Response confirmed by morgancampbell (Occasional Contributor)
Solution

@morgancampbell 

That's easier with Power Query - query table with data or named range; select column with names; unpivot other columns and do some cosmetic as data types and renaming of columns; land result back.