Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Help with duplicate values move horizontally

  1. #11
    New Member sakis_s's Avatar
    Join Date
    Sep 2019
    Location
    Greece
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with duplicate values move horizontally

    Quote Originally Posted by Peter_SSs View Post
    Try making this change near the end of the macro
    Code:
    Range("A1").Resize(k, 2).Value = b
    With Range("A1").Resize(k, 2)
      .NumberFormat = "@"
      .Value = b
    End With
    I tried this one but it doesn't work with this script

    Code:
    Sub Rearrange()
      Dim d As Object
      Dim a As Variant
      Dim i As Long
      
      Set d = CreateObject("Scripting.Dictionary")
      a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
      For i = 1 To UBound(a)
          d(a(i, 1)) = d(a(i, 1)) & ";" & a(i, 2)
      Next i
      With Range("D2:E2").Resize(d.Count)
      .NumberFormat = "@"
      .Value = Application.Transpose(Array(d.Keys, d.Items))
    End With
      Range("E2").Resize(d.Count).TextToColumns DataType:=xlDelimited, Semicolon:=True, FieldInfo:=Array(Array(1, 9))
    End Sub

  2. #12
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,416
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with duplicate values move horizontally

    Quote Originally Posted by sakis_s View Post
    I tried this one but it doesn't work with this script
    Try this one
    Code:
    Sub Rearrange_v2()
      Dim d As Object
      Dim a As Variant, vFieldInfo As Variant
      Dim i As Long, NumCols As Long
      
      Set d = CreateObject("Scripting.Dictionary")
      a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
      For i = 1 To UBound(a)
          d(a(i, 1)) = d(a(i, 1)) & ";" & a(i, 2)
      Next i
      With Range("D1:E1").Resize(d.Count)
        .Value = Application.Transpose(Array(d.Keys, d.Items))
        NumCols = Evaluate(Replace("aggregate(14,6,len(#)-len(substitute(#,"";"","""")),1)", "#", .Columns(2).Address)) + 1
        ReDim vFieldInfo(1 To NumCols)
        vFieldInfo(1) = Array(1, 9)
        For i = 2 To NumCols
          vFieldInfo(i) = Array(i, 2)
        Next i
        .Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True, FieldInfo:=vFieldInfo
      End With
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #13
    New Member sakis_s's Avatar
    Join Date
    Sep 2019
    Location
    Greece
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with duplicate values move horizontally

    Quote Originally Posted by Peter_SSs View Post
    Try this one
    Code:
    Sub Rearrange_v2()
      Dim d As Object
      Dim a As Variant, vFieldInfo As Variant
      Dim i As Long, NumCols As Long
      
      Set d = CreateObject("Scripting.Dictionary")
      a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
      For i = 1 To UBound(a)
          d(a(i, 1)) = d(a(i, 1)) & ";" & a(i, 2)
      Next i
      With Range("D1:E1").Resize(d.Count)
        .Value = Application.Transpose(Array(d.Keys, d.Items))
        NumCols = Evaluate(Replace("aggregate(14,6,len(#)-len(substitute(#,"";"","""")),1)", "#", .Columns(2).Address)) + 1
        ReDim vFieldInfo(1 To NumCols)
        vFieldInfo(1) = Array(1, 9)
        For i = 2 To NumCols
          vFieldInfo(i) = Array(i, 2)
        Next i
        .Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True, FieldInfo:=vFieldInfo
      End With
    End Sub
    Good morning Peter!
    I tried it but this bring all results in one cell separated with ";"


    I could use "text to columns" now but it's a time-consuming process. Any ideas to fix this? Thank you!

  4. #14
    New Member sakis_s's Avatar
    Join Date
    Sep 2019
    Location
    Greece
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with duplicate values move horizontally

    Also forgot to mention that i'm receiving the following error when i run it:


  5. #15
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,416
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with duplicate values move horizontally

    Quote Originally Posted by sakis_s View Post
    I tried it but this bring all results in one cell separated with ";"
    It is supposed to do that because the next thing my code does is Text To Columns, but clearly that is not happening due to the error that you have now also reported.

    As yet I have not been able to reproduce the problem.

    1. Have you changed the code in any way to adapt to your particular circumstances? If so, please post the code you are now using.

    2. What version of Excel and what operating system are you using?

    3. Are you able to upload a small dummy file (any sensitive data removed) that has this problem to a file-share site (eg DropBox) and provide a link to the file so we can have a look at it?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #16
    New Member sakis_s's Avatar
    Join Date
    Sep 2019
    Location
    Greece
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with duplicate values move horizontally

    Quote Originally Posted by Peter_SSs View Post
    It is supposed to do that because the next thing my code does is Text To Columns, but clearly that is not happening due to the error that you have now also reported.

    As yet I have not been able to reproduce the problem.

    1. Have you changed the code in any way to adapt to your particular circumstances? If so, please post the code you are now using.

    2. What version of Excel and what operating system are you using?

    3. Are you able to upload a small dummy file (any sensitive data removed) that has this problem to a file-share site (eg DropBox) and provide a link to the file so we can have a look at it?
    How silly i am! I'm using first row to keep notes and i forgot to change cells A1, D1 & E1 to A2, D2 & E2. Sometimes solution is the most obvious.
    Thank you so much Peter you really helped a lot! All best!

  7. #17
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,416
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with duplicate values move horizontally

    Quote Originally Posted by sakis_s View Post
    Thank you so much Peter you really helped a lot! All best!
    You're welcome. Glad you got it sorted. :;biggrin:
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •