Need help with conditional transpose

[kalique]

Hi all,

I have a table like this:

Smt1 Smt 2
a 1
a 2
a 4
b 5
b 6
c 8
c 7
d 9
d 10
e 11
e 24
e 25

(not unique) (unique key

Now I want to have a function that transpose the Smt2 column when the first column repeat. For example:

Smt 1 Smt2
a 1 2 4
b 5 6
c 8 7

...

Plz help me. Thank you very much!

 

[P Sitaram]

See my post here:

http://www.mrexcel.com/board2/viewtopic.php?t=253408

 

[kalique]

Thank you very much.

I have another question on this:

I did like above to transpose from col -> row to concatenate all the column together by symbol, for example " + " then perform eliminating. Now is there a quick way to do the other way. For example:

a1 b1 + b2 + b3
a2 b1 + b3 + b4

to

a1 b1
a1 b2
a1 b3
...

Thanks,
Kalique

 

[P Sitaram]

Assuming tht the concatanated values are in col B:

Sub CopyRowsToCols()
    Dim i As Integer
    Dim lngRow As Long
    Dim rngCurr As Range
    Dim strSpl
    lngRow = [B65536].End(xlUp).Row
    
    Do While lngRow > 0
        Set rngCurr = Range("b" & lngRow)
        If InStr(1, rngCurr, "+") > 0 Then
            strSpl = Split(rngCurr, "+")
            rngCurr.Offset(1, 0).Resize(UBound(strSpl), 1).Rows.EntireRow.Insert
            For i = UBound(strSpl) To 0 Step -1
                rngCurr.Offset(i, 0) = Trim(strSpl(i))
                rngCurr.Offset(i, -1) = rngCurr.Offset(0, -1)
            Next i
        End If
        lngRow = lngRow - 1
    Loop
    
End Sub

Change the lngRow > 0 to the starting row number.

 

[Yogi Anand]

Hi kalique:

VBA is th way to go on this.

However, in reference to your first question, following is a formula based solution ...

Book1
	A	B	C	D	E	F	G
1	Field1	Field2		uniqueValuesFromColumnA
2	a	1	intermediaryColumn	Field1
3	a	2	1	a	1	2	4
4	a	4	4	b	5	6
5	b	5	6	c	8	7
6	b	6	8	d	9	10
7	c	8	10	e
8	c	7
9	d	9
10	d	10
11	e	11
12	e	24
13	e	25
Sheet1 (2)

cells D3:D7 show unique values from column A

formula in cell C3 is ... =MATCH(D3,$A$2:$A$13,0)
this is then copied down

formula in cell E3 is ... =IF(COLUMNS($A:A)>$C4-$C3,"",INDEX($B$2:$B$13,COLUMNS($A:A)+$C3-1))
this is then copied across and down.