Code To Move From Vertical To Horizontal?

[Dazzawm]

I have sheet 1 as laid out below. Column A will have a list of numbers the same (then they change) with different numbers next to them. I need them to be put on sheet 2 with the numbers in column B next to them with a slash and a gap added, rather than in a list, like the result in sheet 2 and the same when the number in A changes and so on.... Thanks

Before Code

Excel 2010

	A	B
MS001
MS001
MS001
MS001
MS001	9609992380
MS001	377 906 309C
MS002
MS002
MS002
MS002

<tbody>
[TD="align: center"]8[/TD]

[TD="align: right"]46531222[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]60811067[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]60814507[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]500309838[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]60811534[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]16137039[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]5234313[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]33000153[/TD]

</tbody>

Sheet1​

After Code

Excel 2010

	A	B
MS001	46531222/ 60811067/ 60814507/ 500309838/ 9609992380/ 377 906 309C
MS002	60811534/ 16137039/ 5234313/ 33000153

<tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet2​

 

[adam087]

Hi Dazzawm

I've got a UDF I sue for this kind of scenario. It should help you out.

Option Explicit
Option Compare Text

Public Function GROUPON(group_key As Variant, table_array As Range, col_index_num As Long, Optional delim As String = " ")

    Application.Volatile

    Dim s               As String
    Dim cell            As Range
    Dim rangeToCheck    As Range
    
    With table_array
        Set rangeToCheck = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
    End With
    
    For Each cell In rangeToCheck
        If cell.Value = group_key Then s = s & cell.Offset(0, col_index_num - 1).Value & delim
    Next cell
    
    If Len(s) > 0 Then s = Left(s, Len(s) - Len(delim))
    GROUPON = s

End Function

Public Sub DescribeFunction_GROUPON()
    Dim FuncName As String
    Dim FuncDesc As String
    Dim Category As String
    Dim ArgDesc(1 To 5) As String
    
    FuncName = "GROUPON"
    FuncDesc = "Concatenates values from a table based on a provided key in the first column"
    Category = 7 'Text category
    ArgDesc(1) = "is a unique key upon which to group values"
    ArgDesc(2) = "is the Range in which the values are to be found where group_key will be found in the first column"
    ArgDesc(3) = "is the column number in table_array which contains the values to return (where the first column is 1)"
    ArgDesc(4) = "is the symbol to use as a delimiter between the values, where the default is a single space"
       
    Application.MacroOptions _
        Macro:=FuncName, _
        Description:=FuncDesc, _
        Category:=Category, _
        ArgumentDescriptions:=ArgDesc
        
End Sub

It's used as follows...

Excel 2010

	A	B
MS001	46531222/ 60811067/ 60814507/ 500309838/ 9609992380/ 377 906 309C
MS002	60811534/ 16137039/ 5234313/ 33000153

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]MS001[/TD]
[TD="align: center"]46531222[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]MS001[/TD]
[TD="align: center"]60811067[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]MS001[/TD]
[TD="align: center"]60814507[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]MS001[/TD]
[TD="align: center"]500309838[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]MS001[/TD]
[TD="align: center"]9609992380[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]MS001[/TD]
[TD="align: center"]377 906 309C[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]MS002[/TD]
[TD="align: center"]60811534[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]MS002[/TD]
[TD="align: center"]16137039[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]MS002[/TD]
[TD="align: center"]5234313[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]MS002[/TD]
[TD="align: center"]33000153[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]=GROUPON(A13, $A$1:$B$11, 2, "/ ")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B14[/TH]
[TD="align: left"]=GROUPON(A14, $A$1:$B$11, 2, "/ ")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hope that helps
/AJ

 

[Dazzawm]

How do I get it to work then Adam?

 

[adam087]

Copy the code in to a module in your workbook: ALT+F11 to access the editor then Insert --> Module. Save it as a Macro-Enabled workbook.

Now you can use the function =GROUPON in your worksheet as shown.

=GROUPON(group_key, table_array, col_index_num, [delim])

Where...

group_key is a unique key upon which to group values
table_array is the Range in which the values are to be found where group_key will be found in the first column
col_index_num is the column number in table_array which contains the values to return (where the first column is 1)
[delim] is an optional argument for symbol to use as a delimiter between the values, where the default is a single space

(It works syntactically very similiarly to VLOOKUP)

Hope that helps

/AJ

 

[Dazzawm]

Sorry I have no clue how to get it to work.

 

[adam087]

Once you've copied the code in to a module in your workbook, it basically installs a new Excel Function that you can use in a worksheet as you would any normal function, like VLOOKUP.

/AJ

 

[Dazzawm]

Thanks for your help but its going totally over my head. Either nothing happens or I get #VALUE. I need an idiot proof macro where I run it and it does it all for me!!!

 

[Dazzawm]

I have tried the copy paste special transpose but that put all the values in separate cells.

 

[MickG]

Try this:-
Results sheet2

[COLOR="Navy"]Sub[/COLOR] MG14Nov22
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A8"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(1).Value
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) & "/ " & Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Sheets("sheet2").Range("A2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

Thanks Mick, I run it and it comes up with 'Type Mismatch'?