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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Assuming the blank in B4 of your input data should be 7860, this will reconfigure the data starting in D1 to the format you want.
Code: 
Sub sakis_s()
'assumes first data entry is in cell A1
' Reformatted data begin in cell D1
Dim R As Range, Vin As Variant, Vunique As Variant, Vout As Variant, i As Long, j As Long
Dim Cols As Long
Set R = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To UBound(Vin, 1))
Application.ScreenUpdating = False
Range("D1").CurrentRegion.ClearContents
Rows(1).Insert
[A1] = "HDR"
With R.Columns(1).Offset(-1, 0).Resize(R.Rows.Count + 1)
    .AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
End With
Rows(1).Delete
Vunique = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row).Value
For i = 1 To UBound(Vunique, 1)
    For j = 1 To UBound(Vin, 1)
        If Vin(j, 1) = Vunique(i, 1) Then
            ct = ct + 1
            If ct > Cols Then Cols = ct
            Vout(i, ct) = Vin(j, 2)
        End If
    Next j
    ct = 0
Next i
Range(Cells(1, "E"), Cells(UBound(Vout, 1), Cols + Columns("E").Column)).Value = Vout
Application.ScreenUpdating = True
End Sub
 
Upvote 0
JoeMo said:
Assuming the blank in B4 of your input data should be 7860, this will reconfigure the data starting in D1 to the format you want.
Code: 
Sub sakis_s()
'assumes first data entry is in cell A1
' Reformatted data begin in cell D1
Dim R As Range, Vin As Variant, Vunique As Variant, Vout As Variant, i As Long, j As Long
Dim Cols As Long
Set R = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To UBound(Vin, 1))
Application.ScreenUpdating = False
Range("D1").CurrentRegion.ClearContents
Rows(1).Insert
[A1] = "HDR"
With R.Columns(1).Offset(-1, 0).Resize(R.Rows.Count + 1)
    .AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
End With
Rows(1).Delete
Vunique = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row).Value
For i = 1 To UBound(Vunique, 1)
    For j = 1 To UBound(Vin, 1)
        If Vin(j, 1) = Vunique(i, 1) Then
            ct = ct + 1
            If ct > Cols Then Cols = ct
            Vout(i, ct) = Vin(j, 2)
        End If
    Next j
    ct = 0
Next i
Range(Cells(1, "E"), Cells(UBound(Vout, 1), Cols + Columns("E").Column)).Value = Vout
Application.ScreenUpdating = True
End Sub
Click to expand...

Wow Joe! That was really amazing! I can't thank you enough! I really appreciate your time, it works perfectly! :rofl:
 
Upvote 0
Another way that you could consider
Code: 
Sub Rearrange()
  Dim d As Object
  Dim a As Variant
  Dim i 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
  Range("D1:E1").Resize(d.Count).Value = Application.Transpose(Array(d.Keys, d.Items))
  Range("E1").Resize(d.Count).TextToColumns DataType:=xlDelimited, Semicolon:=True, FieldInfo:=Array(Array(1, 9))
End Sub
 
Upvote 0
Solution
Just re-reading this thread and noticed that you originally asked for a formula to do this.
If you are interested, that can be done & for this I have assumed a heading row with data starting in row 2.
D2 is copied down as far as you might ever need.
E2 is copied across as far as you might ever need and down as far as the column D formula.

Excel Workbook
ABCDEFGH
1
2204089954Z4204089954Z4
3203784031852037840318544557860
420378404455203832831239999
520378407860203819014B3
620383283123
720383289999
8203819014B3
Summary
 
Upvote 0
Peter_SSs said:
Just re-reading this thread and noticed that you originally asked for a formula to do this.
If you are interested, that can be done & for this I have assumed a heading row with data starting in row 2.
D2 is copied down as far as you might ever need.
E2 is copied across as far as you might ever need and down as far as the column D formula.

Summary


ABCDEFGH






54Z4
54Z4








14B3














14B3






<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:49px;"><col style="width:32px;"><col style="width:68px;"><col style="width:49px;"><col style="width:47px;"><col style="width:47px;"><col style="width:25px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]2040899[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]2037840[/TD]
[TD="align: right"]3185[/TD]

[TD="align: right"]2037840[/TD]
[TD="align: right"]3185[/TD]
[TD="align: right"]4455[/TD]
[TD="align: right"]7860[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]2037840[/TD]
[TD="align: right"]4455[/TD]

[TD="align: right"]2038328[/TD]
[TD="align: right"]3123[/TD]
[TD="align: right"]9999[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]2037840[/TD]
[TD="align: right"]7860[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]2038328[/TD]
[TD="align: right"]3123[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]2038328[/TD]
[TD="align: right"]9999[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]2038190[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
D2=IFERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$8)+(A$2:A$8=""),0),0)),"")
E2=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($B$2:$B$8)-ROW($B$2)+1)/($A$2:$A$8=$D2),COLUMNS($E2:E2))),"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

That's an amazing solution as well! Thanks you so much for this function Peter! Script seems to work easier in my situation!

By the way, is there any way to also make the exact opposite using a script?
 
Upvote 0
sakis_s said:
Script seems to work easier in my situation!
Click to expand...
OK, so you have a couple of those to choose from. :)


sakis_s said:
By the way, is there any way to also make the exact opposite using a script?
Click to expand...
Sure, if you started with the results above in columnd D, E, F, ... and columns A:C blank then try

Code: 
Sub Make_List()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("D1").CurrentRegion.Value
  ReDim b(1 To UBound(a) * UBound(a, 2), 1 To 2)
  For i = 1 To UBound(a)
    For j = 2 To UBound(a, 2)
      If Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, j)
      End If
    Next j
  Next i
  Range("A1").Resize(k, 2).Value = b
End Sub
 
Upvote 0
Peter_SSs said:
OK, so you have a couple of those to choose from. :)


Sure, if you started with the results above in columnd D, E, F, ... and columns A:C blank then try

Code: 
Sub Make_List()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("D1").CurrentRegion.Value
  ReDim b(1 To UBound(a) * UBound(a, 2), 1 To 2)
  For i = 1 To UBound(a)
    For j = 2 To UBound(a, 2)
      If Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, j)
      End If
    Next j
  Next i
  Range("A1").Resize(k, 2).Value = b
End Sub
Click to expand...

That works perfectly too!
Thank you so much Peter!

Just noticed one thing:
Some cells are formatted as text and have leading zeros:
e.g. "0700", "0010" etc.

Macro when pastes to new cells, changes the format and i'm loosing the leading zeros. Do you know why is this happening?

<colgroup><col width="420"></colgroup><tbody>
[TD="class: xl66, width: 420"][/TD]

</tbody>

<colgroup><col width="420"></colgroup><tbody>
[TD="class: xl64, width: 420"][/TD]

</tbody>
 
Upvote 0
Try making this change near the end of the macro
Code: 
<del>Range("A1").Resize(k, 2).Value = b</del>
With Range("A1").Resize(k, 2)
  .NumberFormat = "@"
  .Value = b
End With
 
Upvote 0
Peter_SSs said:
Another way that you could consider
Code: 
Sub Rearrange()
  Dim d As Object
  Dim a As Variant
  Dim i 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
  Range("D1:E1").Resize(d.Count).Value = Application.Transpose(Array(d.Keys, d.Items))
  Range("E1").Resize(d.Count).TextToColumns DataType:=xlDelimited, Semicolon:=True, FieldInfo:=Array(Array(1, 9))
End Sub
Click to expand...

Yes! Now copies values as text! Perfect! Can you help me with your previous script too? I really appreciate your help Peter! :)
 
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
181
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,884
Messages
6,175,173
Members
452,615
Latest member
bogeys2birdies

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