CODEWITHSUNDEEP
exceltranspose
Raj
Raj

Reputation: 1071

How to transpose a column in excel relative to another?

Is there a way to do transpose a column with respect to another in excel. What i am trying to do is similar to what Proc Transpose in SAS or The reshape package in R does.

A   1
A   2
B   1
B   2
B   4

I want it to transformed into this format in excel.

A   1   2   
B   1   2   4

Upvotes: 0

Views: 127

Answers (3)

Forward Ed
Forward Ed

Reputation: 9894

One possible solution for you is as follows. Appologies if it is a bit convoluted, I just woke up and the fog in my head has not cleared.

In D2 place this:

=IFERROR(INDEX($A$1:$A$5,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$1:$A$5),0,0),0)),"")

It will generate a unique list of items from column A

Then in column E2 use the following:

=IF(COUNTIF($A$1:$A$5,$D2)>=COLUMN(A:A),INDEX($B$1:$B$5,MATCH($D2,$A$1:$A$5,0)+COLUMN(A:A)-1),"")

It will pull the items in column B that correspond to column A. Copy it to the right as far as you need to go.

Both those formulas can be copied down as far as you need to go.

BONUS

Place the following in E1. It is an array formula so instead of pressing enter when you are done with the formula, you press CTRL+SHFT+ENTER. This is also why these formulas are also referred to as CSE formulas sometimes.

=MAX(COUNTIF($A$1:$A$5,D2:D3))&" Columns are required"
or
=MAX(COUNTIF(A1:A5,A1:A5))&" Columns are required"
'Thanks to Scott Craner for the second formula here

You will know you have entered it correctly when {} appear around the formula in the formula bar. You cannot add these {} manually.

CAVEAT

The data needs to be grouped.

Proof of concept:

enter image description here

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96771

With data in columns A and B, try this short macro:

Sub ReOrganizer()
    Dim i As Long, j As Long, Na As Long, K As Long
    Dim s As String, rc As Long, Nd As Long

    rc = Rows.Count
    Range("A:A").Copy Range("D:D")
    Range("D:D").RemoveDuplicates Columns:=1, Header:=xlNo
    Na = Cells(rc, "A").End(xlUp).Row
    Nd = Cells(rc, "D").End(xlUp).Row

    For i = 1 To Nd
        s = Cells(i, "D").Value
        K = 5
        For j = 1 To Na
            If s = Cells(j, "A").Value Then
                Cells(i, K).Value = Cells(j, "B").Value
                K = K + 1
            End If
        Next j
    Next i
End Sub

enter image description here

NOTE:

  • the data in column A is not required to be sorted.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152605

This formula will do what you want, I put "A" in D1 and "B" in D2. Put this in E1 and copy over and down:

=IFERROR(INDEX($B:$B,IF(COLUMN(A:A)>COUNTIF($A:$A,$D1),1500000,MATCH($D1,$A:$A,0)+COLUMN(A:A)-1)),"")

Make sure to copy over and down sufficient to cover all data.

enter image description here

Upvotes: 3

Related Questions