Line up values in rows without manually shifting column down

[Emma Eve]

Hello.

I would like to be able to shift cells down so that the values in two columns align. Currently, I am looking at each row and manually insert - shift cells down to line up the values in each column. I have thousands of rows, so I can easily spend half a day or more doing this. Is there a formula or something that will accomplish this? I'll do my best to provide an example below.

Thank you for your time and consideration!

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Data 1 - Original
[/TD]
[TD]Data 2 - Original[/TD]
[TD][/TD]
[TD]Data 1 - Adjusted[/TD]
[TD]Data 2 - Adjusted[/TD]
[/TR]
[TR]
[TD]M4262[/TD]
[TD]M4262[/TD]
[TD][/TD]
[TD]M4262[/TD]
[TD]M4262[/TD]
[/TR]
[TR]
[TD]M4264[/TD]
[TD]M4263[/TD]
[TD][/TD]
[TD]inserted automatically
[/TD]
[TD]M4263[/TD]
[/TR]
[TR]
[TD]M4265[/TD]
[TD]M4266[/TD]
[TD][/TD]
[TD]M4264[/TD]
[TD]inserted automatically[/TD]
[/TR]
[TR]
[TD]M4267[/TD]
[TD]M4268[/TD]
[TD][/TD]
[TD]M4265[/TD]
[TD]inserted automatically[/TD]
[/TR]
[TR]
[TD]M4268[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]inserted automatically[/TD]
[TD]M4266[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]M4267[/TD]
[TD]inserted automatically[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]M4268[/TD]
[TD]M4268[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Dobo_Bobo]

Does it have to be two seperate lists?

If not, then the two lists can be merged into one list. You can do this by:

1) Selecting all of the Data 2 reference numbers > pasting them directly below M4268 in the Data 1 list.
2) Select all of the reference numbers > go to 'Data' tab on the ribbon at the top of Exel > click 'Remove Duplicates' > Press OK > Press OK once you have read the confirmation message.
3) Select the whole list again and sort by smallest to largest in the 'Sort' section of the Data tab on the ribbon.

 

[MickG]

I'm not sure this will be acceptable as it on relates to column "A & B", But Maybe !!!
The code will replace the existing data with the Adjusted Data.

[COLOR="Navy"]Sub[/COLOR] MG24Jan23
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object, Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] sRay [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
                [COLOR="Navy"]If[/COLOR] Not .Contains(Dn.Value) [COLOR="Navy"]Then[/COLOR] .Add Dn.Value
                [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                    Dic.Add (Dn.Value), Dn
                [COLOR="Navy"]Else[/COLOR]
                    [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = Union(Dic(Dn.Value), Dn)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR]
        .Sort
 [COLOR="Navy"]Dim[/COLOR] c
sRay = .toarray
ReDim Ray(1 To .Count + 1, 1 To 2)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(sRay)
    c = c + 1
    [COLOR="Navy"]If[/COLOR] Dic(sRay(n)).Count = 2 [COLOR="Navy"]Then[/COLOR]
        Ray(c, 1) = sRay(n): Ray(c, 2) = sRay(n)
    [COLOR="Navy"]Else[/COLOR]
        Ray(c, Dic(sRay(n)).Column) = sRay(n)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Range("A2").Resize(c, 2).Value = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[VBA Geek]

Hello, with data in column A and B, both sorted as in your example, try to run the below code
Excel 2013/2016

	A	B
Data 1 - Original	Data 2 - Original
M4262	M4262
M4264	M4263
M4265	M4266
M4267	M4268
M4268

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

</tbody>

Sheet1​

Sub EmmaM()
Dim i As Long, iAdd As Long, lRow As Long
Application.ScreenUpdating = False
lRow = Range("B" & Rows.Count).End(xlUp).Row
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    iAdd = WorksheetFunction.CountIfs(Range("B2:B" & lRow), "<" & Range("A" & i).Value, Range("B2:B" & lRow), ">" & Range("A" & i - 1).Value)
    If iAdd Then Range("A" & i).Resize(iAdd).Rows.Insert xlDown
Next i

lRow = Range("A" & Rows.Count).End(xlUp).Row
For i = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
    iAdd = WorksheetFunction.CountIfs(Range("A2:A" & lRow), "<" & Range("B" & i).Value, Range("A2:A" & lRow), ">" & Range("B" & i - 1).Value)
    If iAdd Then Range("B" & i).Resize(iAdd).Rows.Insert xlDown
Next i
End Sub

 

[Emma Eve]

Does it have to be two seperate lists?

If not, then the two lists can be merged into one list. You can do this by:

1) Selecting all of the Data 2 reference numbers > pasting them directly below M4268 in the Data 1 list.
2) Select all of the reference numbers > go to 'Data' tab on the ribbon at the top of Exel > click 'Remove Duplicates' > Press OK > Press OK once you have read the confirmation message.
3) Select the whole list again and sort by smallest to largest in the 'Sort' section of the Data tab on the ribbon.

Hello. Thank you for the reply. Yes, I do need to retain both columns. After I line up the rows, I use INDEX/MATCH to pair other information to the values in both columns and then I create an analysis.

 

[Emma Eve]

Wow - thank you so much! It works great! I use =A2=B2 just to make sure a duplicate isn't lurking among the data: duplicates throw off the coding.