How to transpose Data

[webmax]

Hi
Attaching the excel sheet contains student data.
I am give two things one Raw Data & Output need given in the sheet (what actual output i need)
any body can help

Regards
Shahul

 

[bosco_yip]

Try...............................

1] In I3, copied down :

=IFERROR(INDEX(A$3:A$11,MATCH(0,INDEX(COUNTIF(I$2:I2,A$3:A$11),0),0)),"")

2] In J3, copied down :

=IF($I3="","",VLOOKUP($I3,$A$3:$C$11,2,0))

3] In K3, copied down :

=IFERROR(INDEX($C$1:$C$11,AGGREGATE(15,6,ROW($C$3:$C$11)/($A$3:$A$11=$I3)/($B$3:$B$11=$J3),COUNTIF($K$2:K$2,K$2))),"")

4] In L3, copied down :

=IFERROR(INDEX($D$1:$D$11,AGGREGATE(15,6,ROW($C$3:$C$11)/($A$3:$A$11=$I3)/($B$3:$B$11=$J3),COUNTIF($L$2:L$2,L$2))),"")

5] Then, select formula range K3:L5, copied across right until column T

Regards
Bosco

 

[webmax]

thanks if any macro is there

 

[shrivallabha]

thanks if any macro is there

Something like below should help you.

Public Sub TransposeData()
Dim lngOutRow As Long: lngOutRow = 6 '\\ Change to suit
Dim lngOutCol As Long: lngOutCol = 9 '\\ Change to suit
Dim i As Long
Dim objDict As Object: Set objDict = CreateObject("Scripting.Dictionary")
objDict.CompareMode = vbTextCompare

'\\ Load in Dictionary
For i = 3 To Range("A" & Rows.Count).End(xlUp).Row
    If objDict.Exists(Range("A" & i).Value & "|" & Range("B" & i).Value) Then
        objDict.Item(Range("A" & i).Value & "|" & Range("B" & i).Value) = _
        objDict.Item(Range("A" & i).Value & "|" & Range("B" & i).Value) & "|" & Range("C" & i).Value & "|" & Range("D" & i).Value
    Else
        objDict.Add Range("A" & i).Value & "|" & Range("B" & i).Value, Range("C" & i).Value & "|" & Range("D" & i).Value
    End If
Next i

'\\ Get Dictionary output in cells
Dim k, s
For Each k In objDict.Keys
    s = Split(k & "|" & objDict.Item(k), "|")
    Cells(lngOutRow, lngOutCol).Resize(1, UBound(s) + 1) = s
    lngOutRow = lngOutRow + 1
Next k

'\\ Release object
Set objDict = Nothing
End Sub

 

[webmax]

Hi
Thanks for the macro .
If i want to add extra column where to change the code
I added two columns but the data is not filling properly attached the file for the reference.
And also if i want to copy the file in the next sheet what is the macro code

Regards
shahul

 

[shrivallabha]

Hi
Thanks for the macro .
If i want to add extra column where to change the code
I added two columns but the data is not filling properly attached the file for the reference.
And also if i want to copy the file in the next sheet what is the macro code

Regards
shahul

You have changed requirement which you should've stated in the first post itself. Is this your final layout?

 

[webmax]

yes

 

[webmax]

Hi
Ataching the updated file can u help in macro for the output data

 

[Peter Bartholomew]

I used this as an exercise in 'modern dynamic array' calculation.
My first observation would be that the mechanics would be far simpler if the subject and marks awarded were concatenated into single cells for the purpose of output display.



The cells highlighted in lurid magenta are the cells containing formulas.
The Student IDs and Names were listed vertically using 'unique' but the results were transposed before filtering.

Note1: 'Unique.ID' is the cell C16 containing 5001 so 'Unique.ID#' is the spilt range that is currently C16:C17. The final trick is to pick one value from the array at a time using the @ operator because FILTER required a 1D array.

Note2: Just for the record, the workbook also contains the alternating pattern, as requested. That uses the formula
= IF(alternate, INDEX(Subjects.Sat, counter), INDEX(Marks.Attained, counter))
for output.

p.s. I am not sure how many (other than @AliGW) can see this !

 

[AliGW]

Why wouldn't anyone else be able to see this?

 

[Peter Bartholomew]

Hi Ali
Perhaps we should retire to the 'lounge' and find out what proportion of Forum members and Ninjas are on Excel 365 insider (or at least have access to it) and what they think of it.

For me it is such a relief having Excel work the way it should always have worked, rather than having to hide my formulae away within defined names so that they are not crippled by the dreaded implicit intersection. Now I need no longer commit everything I write with CSE just to be on the safe side!

Just clicked! I was talking about the xlsx; the picture is pretty strident. Sun specs may be in order.

 

[shrivallabha]

Hi
Ataching the updated file can u help in macro for the output data

Based on your inputs I have revised code. It is final offering from my side. You will have to do further amendments if requirements change again!

I have added code to copy the output to other sheet. Read the code and edit it to suit. Currently, it will give you output on second sheet.

Public Sub TransposeData()
Dim wksOutSht As Worksheet: Set wksOutSht = ThisWorkbook.Sheets(2) '\\ Change to suit
Dim lngOutRow As Long: lngOutRow = 2 '\\ Change to suit
Dim lngOutCol As Long: lngOutCol = 1 '\\ Change to suit
Dim i As Long
Dim objDict As Object: Set objDict = CreateObject("Scripting.Dictionary")
objDict.CompareMode = vbTextCompare

'\\Empty destination sheet
wksOutSht.UsedRange.ClearContents

'\\ Load in Dictionary
For i = 3 To Range("A" & Rows.Count).End(xlUp).Row
    If objDict.Exists(Range("A" & i).Value & "|" & Range("B" & i).Value & "|" & Range("C" & i).Value) Then
        objDict.Item(Range("A" & i).Value & "|" & Range("B" & i).Value & "|" & Range("C" & i).Value) = _
        objDict.Item(Range("A" & i).Value & "|" & Range("B" & i).Value & "|" & Range("C" & i).Value) & "|" _
        & Range("D" & i).Value & "|" & Range("E" & i).Value & "|" & Range("F" & i).Value & "|" & Range("G" & i).Value _
         & "|" & Range("H" & i).Value & "|" & Range("I" & i).Value
    Else
        objDict.Add Range("A" & i).Value & "|" & Range("B" & i).Value & "|" & Range("C" & i).Value, _
        Range("D" & i).Value & "|" & Range("E" & i).Value & "|" & Range("F" & i).Value & "|" & Range("G" & i).Value _
        & "|" & Range("H" & i).Value & "|" & Range("I" & i).Value
    End If
Next i

'\\ Get Dictionary output in cells
Dim k, s
For Each k In objDict.Keys
    s = Split(k & "|" & objDict.Item(k), "|")
    wksOutSht.Cells(lngOutRow, lngOutCol).Resize(1, UBound(s) + 1) = s
    lngOutRow = lngOutRow + 1
Next k

'\\ Release object
Set objDict = Nothing
End Sub

 

[Brandi Bell]

What if I was wanting to do the reverse? I have a data set that is formatted as the Output Data for this question, but I am wanting to transpose it into the format of the Raw Data.
I have attached my sample data. I am at a loss as to how to make this work.
Thanks for any help!
Brandi