Transpose Data

[Brianfree]

Hi i have some data in excel which look like the following..

a,1
b,1
c,1
d,1
x,2
y,2

I need to format it like...


1,a,b,c,d
2,x,y

Please can anyone advise?

Many thanks,

Brian

 

[SkipVought]

Hi,

Check out Edit > Paste Special -- TRANSPOSE

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Brianfree]

Hi, i have tried this but it doesn't create a new row for each number..

little bit stuck!

thanks anyway for replying..

 

[SkipVought]

but it doesn't create a new row for [highlight blue][white]each[/white][/highlight] number..

But THAT is not what you specified!!!
[tt]
[highlight]a,1
b,1
c,1
d,1[/highlight]
[highlight green]x,2
y,2[/highlight]

I need to format it like...


[highlight]1,a,b,c,d[/highlight]
[highlight green]2,x,y[/highlight]
[/tt]
THINK about it. You MIGHT need to do this in more than one step.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

You might want to use a user-defined function like:

Function RangeConcatenate(rngIn As Range, _
        Optional strDelimiter As String = ", ")
[green]    ' Usage
    '  A1 = Red
    '  A2 = White
    '  A3 = Blue
    ' RangeConcatenate(A1:A3) = Red, White, Blue
    ' RangeConcatenate(A1:A3," - ") = Red - White - Blue
[/green]    
    Dim rngTemp As Range              [green]'each cell[/green]
    Dim strTemp As String
    Application.Volatile              [green]'autoupdates[/green]
    For Each rngTemp In rngIn         [green]'loop through each cell[/green]
        If Len(rngTemp.Text) > 0 Then [green]'ignore blank cells[/green]
            strTemp = strTemp & rngTemp & strDelimiter
        End If
    Next
    If Len(strDelimiter) > 0 Then     [green]'remove the final delimiter[/green]
        strTemp = Left(strTemp, Len(strTemp) - Len(strDelimiter))
    End If
    RangeConcatenate = strTemp
End Function

Duane
Hook'D on Access
MS Access MVP

 

[lionelhill]

If you want a method using only formulae and commands, here is an option that works in Excel2007 or better (I don't think <= Excel 2003 has a text-to-columns tool, but you can get the same effect by saving as a .txt file and reopening).

I assume your numbers are in column B and letters are in column A. If they are the other way round, merely swap A and B in the formulae below. If they are currently in one column in the format 1,a, use the text-to-columns tool on the Data tab to split them.

I also assume your data are sorted by number so that all entries that need to be appear on one row are currently on adjacent rows. If the data are mixed up randomly, sort them.

Make cell C1 = A1
Put the following in C2:
=IF(B2=B1, CONCATENATE(C1,",",A2), A2)
Copy this down to the end of the data. This column concatenates letters grouped by number.
Put the following in D1, and copy down to the end of the data:
=IF(B1<>B2, B1&","&C1, "")
This selects only one row for each group of one number, and adds the number.

Take column D and copy it. Paste it somewhere as values, and sort it. This will get rid of all the gaps, and put the rows together.
You now have your output in the format 1,a,b,c as you requested, but not in separate columns.
To put it in separate columns, use the text to columns tool.