Automatic table

%3CLINGO-SUB%20id%3D%22lingo-sub-2210396%22%20slang%3D%22en-US%22%3EAutomatic%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2210396%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I%20need%20help%20with%20creating%20a%20table.%20In%20sheet%201%2C%20I%20have%20my%20data.%20I%20want%20to%20transfer%20the%20names%20under%20Column%20A%20to%20the%20table%20on%20Sheet%202%2C%20and%20are%20categorized%20according%20to%20the%20identified%20labels.%20I'm%20trying%20the%20pivot%2C%20however%2C%20it%20only%20calculates%20and%20not%20copy%20the%20texts.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20be%20attaching%20the%20file%20for%20reference.%20Hoping%20that%20you%20can%20help%20me.%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lavtubo12_1-1615793204935.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263694i78E21AD46990ED0B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lavtubo12_1-1615793204935.png%22%20alt%3D%22lavtubo12_1-1615793204935.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESheet%202%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lavtubo12_2-1615793249709.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263695iBC33E155EB8CE48B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lavtubo12_2-1615793249709.png%22%20alt%3D%22lavtubo12_2-1615793249709.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPreferred%20output%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lavtubo12_4-1615793319776.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263697iFDB6CC2CD1118FB9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lavtubo12_4-1615793319776.png%22%20alt%3D%22lavtubo12_4-1615793319776.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2210396%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%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-2210446%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2210446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F997344%22%20target%3D%22_blank%22%3E%40lavtubo12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20which%20version%20of%20Excel%20you%20are.%20For%20such%20transformation%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20434px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263700iAE5CD0861B7E4AC9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20J2%20it%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTRANSPOSE(UNIQUE(F2%3AF7))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20J3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(%24A%242%3A%24A%247%2C%24F%242%3A%24F%247%3DJ%242%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20to%20the%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi! I need help with creating a table. In sheet 1, I have my data. I want to transfer the names under Column A to the table on Sheet 2, and are categorized according to the identified labels. I'm trying the pivot, however, it only calculates and not copy the texts. 

 

I'll be attaching the file for reference. Hoping that you can help me. Thank you!

 

Sheet 1

lavtubo12_1-1615793204935.png

Sheet 2

lavtubo12_2-1615793249709.png

 

Preferred output:

lavtubo12_4-1615793319776.png

 

1 Reply

@lavtubo12 

Depends on which version of Excel you are. For such transformation

image.png

in J2 it could be

=TRANSPOSE(UNIQUE(F2:F7))

in J3

=FILTER($A$2:$A$7,$F$2:$F$7=J$2,"")

and drag it to the right.