Help with duplicate values move horizontally

sakis_s

sakis_s

New Member
Joined
Sep 22, 2019
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
Hi!
I'm looking for a formula to do the following:

I have:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]2040899
[/TD]

</tbody>
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]54Z4
[/TD]

</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="width: 68"]2037840
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]3185
[/TD]

</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]2037840[/TD]

</tbody>
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="width: 68"]4455
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]2037840[/TD]

</tbody>
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="width: 68"]7860
[/TD]
[/TR]
</tbody>[/TABLE]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]2038328
[/TD]

</tbody>
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]3123
[/TD]

</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2038328
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]9999
[/TD]

</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]

<colgroup><col width="68"></colgroup><tbody>
[TD="width: 68"]2038190
[/TD]

</tbody>
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="width: 68"]14B3
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And i need the following result:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2040899
[/TD]
[TD]54Z4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2037840
[/TD]
[TD]3185
[/TD]
[TD]4455
[/TD]
[TD]7860
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2038328
[/TD]
[TD]3123
[/TD]
[TD]9999
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2038190
[/TD]
[TD]14B3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Removing duplicates from column A and entering Column B values from removed duplicates horizontally next to the first value.

Anyone has any idea how to do that?

Your help is much appreciated.

Thank you very much for your time.
 
Sort by date Sort by votes
Peter_SSs said:
Try making this change near the end of the macro
Rich (BB code): 
<del>Range("A1").Resize(k, 2).Value = b</del>
With Range("A1").Resize(k, 2)
  .NumberFormat = "@"
  .Value = b
End With
Click to expand...

I tried this one but it doesn't work with this script :lookaway:

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
  [COLOR=#ff0000]With Range("D2:E2").Resize(d.Count)
  .NumberFormat = "@"
  .Value = Application.Transpose(Array(d.Keys, d.Items))
End With[/COLOR]
  Range("E2").Resize(d.Count).TextToColumns DataType:=xlDelimited, Semicolon:=True, FieldInfo:=Array(Array(1, 9))
End Sub
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
sakis_s said:
I tried this one but it doesn't work with this script :lookaway:
Click to expand...
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
 
Upvote 0
Peter_SSs said:
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
Click to expand...

Good morning Peter!
I tried it but this bring all results in one cell separated with ";"
11.jpg


I could use "text to columns" now but it's a time-consuming process. Any ideas to fix this? Thank you!
 
Upvote 0
Also forgot to mention that i'm receiving the following error when i run it:

Capture2.jpg
 
Upvote 0
sakis_s said:
I tried it but this bring all results in one cell separated with ";"
Click to expand...
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?
 
Upvote 0
Peter_SSs said:
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?
Click to expand...

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. :eeek:
Thank you so much Peter you really helped a lot! All best! :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

lost_in_the_sauce
  • Solved
How to get value with most recent date in an array
Replies
6
Views
543
Fluff
Fluff
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
182
Madam_Xavier
M
ExcelNewbie2020
  • Solved
Extract unique values and sum duplicates
Replies
3
Views
240
alansidman
A
R
  • Solved
Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match
2
Replies
10
Views
636
RapchikM
R
P
  • Question Question
INDEX-MATCH with Duplicate Values
Replies
4
Views
459
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top