CODEWITHSUNDEEP
vbaexcel
Mowgli
Mowgli

Reputation: 3512

Compare Columns B and C then shift data

I need to compare Column B and C. They consist of real numbers only, sorted in ascending order.

I already have the code for what I want it to do, but I need help changing the range in the code.

So currently code compares Column B and C.

Look at this image for more clear explanation.

I need help changing code so I can add a column to the left of A and to the right of D also, and add those to shift range.

Basically, compare E and F and shift B:E and F:I.

Option Explicit
Sub AlignList()
' hiker95, 08/02/2012
' http://www.mrexcel.com/forum/showthread.php?651139-Compare-Column-B-amp-C-and-Shift
Dim r As Long, lr As Long, d As Range
Application.ScreenUpdating = False
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
Set d = Range("A1:B" & lr)
r = 1
Do While d.Cells(r, 2) <> ""
  If d.Cells(r, 2).Offset(, 1) <> "" Then
    If d.Cells(r, 2) < d.Cells(r, 2).Offset(, 1) Then
      d.Cells(r, 2).Offset(, 1).Resize(, 2).Insert -4121
    ElseIf d.Cells(r, 2) > d.Cells(r, 2).Offset(, 1) Then
      d.Cells(r, 1).Resize(, 2).Insert -4121
      lr = lr + 1
      Set d = Range("A1:B" & lr)
    End If
  End If
  r = r + 1
Loop
Application.ScreenUpdating = 1
End Sub

Upvotes: 1

Views: 2699

Answers (1)

Stepan1010
Stepan1010

Reputation: 3136

You don't need a macro to do what you are trying to do (unless you are trying to do it automatically for some bizarre reason.

You can just copy and paste columns D and C so that they are below the other data(see picture) and then add a 5th column that is the max of columns B and C(this will grab the number):

Answer Before

And then you just need to sort on the max column:

Answer After

EDIT:

Ok so this is pretty basic excel so I am just going to post the screenshots with some small directions. Basically what you are trying to do(I don't know if you realize this) is combine two tables(granted it is in a really bizarre way). So if you follow my original instructions except use vlookup a little creatively you can absolutely accomplish what your are trying to do. Here are the screenshots:

So using vlookup creatively: Original

You can match up the data that needs to be matched up: Vlookup1

And Here is the other vlookup column: Vlookup2

Then once your data is matched up - you can convert it all to values with copy and paste: CopyPasteValues

Then you add your max column: MaxColumn

Then you just sort and you should get what you want: WhatYouWant

You can then delete any extra data that you don't need any more: DeleteAnythingYouDoNotWant

So hopefully that helps you out. The only reason you would want to use VBA is if you want to automate this process - but if this is a one time thing then that would be overly time consuming. Excel is meant to manipulate data - match up tables - things like that - use it for that purpose.

Upvotes: 5

Related Questions