how to arrange the data

[s_balasubramanian]

how to get/arrange the data as below.
Original Data I have	Need like below
Team	Code	Cname	Team	Act	LL	KK	UU
C	54	KK	A
A	11	Act	B
A	12	LL	C
A	13	KK	D
B	21	Act
B	12	LL
B	23	KK	in Excel 2016
C	31	Act
D	41	Act
A	14	UU
C	66	UU
C	43	LL

[td]

11​

[/td]​

[td]

12​

[/td]​

[td]

13​

[/td]​

[td]

14​

[/td]​

[td]

21​

[/td]​

[td]

12​

[/td]​

[td]

23​

[/td]​

[td]

31​

[/td]​

[td]

43​

[/td]​

[td]

54​

[/td]​

[td]

66​

[/td]​

[td]

41​

[/td]​

 

[AlanSidman]

Pivot your data using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Cname]), "Cname", "Code")
in
    #"Pivoted Column"

 

[vletm]

Pivot-table

 

[s_balasubramanian]

Pivot-table

Thanks
if i have alfanumeric data, how can i arrange it. new file attached

 

[vletm]

or something like this?
or need exact information.

 

[AlanSidman]

if i have alfanumeric data, how can i arrange it. new file attached

Will work the same if you use the power Query Mcode that I showed in post #2. Makes no difference if the values are alpha, alpha numberic or numeric.

 

[jindon]

vba, if you like.

Sub test()
    Dim s$(1), i&, r As Range
    Set r = Sheets("sheet1").[j3]
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0 xml;HDR=Yes';"
    s(0) = "Transform First(Code) Select Team From `Sheet1$F3:H` Group By Team Pivot Cname;"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        For i = 0 To .Fields.Count - 1
            r(, i + 1) = .Fields(i).Name
        Next
        r(2).CopyFromRecordset .DataSource
        .Close
    End With
End Sub

 

[s_balasubramanian]

vba, if you like.

Sub test()
    Dim s$(1), i&, r As Range
    Set r = Sheets("sheet1").[j3]
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0 xml;HDR=Yes';"
    s(0) = "Transform First(Code) Select Team From `Sheet1$F3:H` Group By Team Pivot Cname;"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        For i = 0 To .Fields.Count - 1
            r(, i + 1) = .Fields(i).Name
        Next
        r(2).CopyFromRecordset .DataSource
        .Close
    End With
End Sub

Thanks

 

[s_balasubramanian]

Pivot your data using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Cname]), "Cname", "Code")
in
    #"Pivoted Column"

thanks
i have to learn to use power query and will use it

 

[s_balasubramanian]

sir

i have attached the exact data need for this request
please provide any macros in details to get the data like in sheet31 and i'm new to this
to understand the macros, please suggest any you tube video link if possible

 

[vletm]

One sample

Usage:
# Have data in rawdata-sheet
# Select Output-sheet

To Understand Macros:
# Try to read row by row - what is going on?

Bonus:
# Select any Code to see ... those.

 

[AlanSidman]

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

 

[s_balasubramanian]

One sample

Usage:
# Have data in rawdata-sheet
# Select Output-sheet

To Understand Macros:
# Try to read row by row - what is going on?

Bonus:
# Select any Code to see ... those.

thanks
will study and understand
but very impressive after clicking the output sheet (x y reference)

 

[s_balasubramanian]

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

thanks for your support