matching up columns

[ngg]

This one is kind of complicated.

I have 2 columns with numbers in them.(they are much bigger than my
examples). All numbers in column B exist in column A but not all numbers in
column A exist in column B. What I need to do is match up B with A. So I need
to find the number 2 in column A and then move the 2 down in column B so it
aligns with the 2 in column A. Is there a quick and easy way to do this? I
have listed my desired result below.

A B

1 2
2 4
3 5
4 8
5
6
7
8


Desired result

A B

1
2 2
3
4 4
5 5
6
7
8 8



ngg

 

[Dave Peterson]

Add headers to row 1 if they you don't have them. (Delete them if you don't
want them later.)

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

'change the mycols to the number of columns that
'are associated with column B

myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

========
Remember: No duplicates in each of the columns, right????

 

[Dave Peterson]

I see you have a solution at your other post.

This one is kind of complicated.

I have 2 columns with numbers in them.(they are much bigger than my
examples). All numbers in column B exist in column A but not all numbers in
column A exist in column B. What I need to do is match up B with A. So I need
to find the number 2 in column A and then move the 2 down in column B so it
aligns with the 2 in column A. Is there a quick and easy way to do this? I
have listed my desired result below.

A B

1 2
2 4
3 5
4 8
5
6
7
8

Desired result

A B

1
2 2
3
4 4
5 5
6
7
8 8

ngg

 

[ngg]

Let me relist my table. I did not have it correct. What I need is where the
number in B is in A, I need that number in B and its corresponding date in C
to line up with the like number in A.




Will your solution still work for this? If it does, what exactly do I do
with it? I have never worked with anything this complex. Thanks!!!

A B C

19271 19481 19410702
192713 213111 19400424
19481 213382 19410211
19591 213381 19410211
200173 214581 19430905
210581 221313 19421228
213111 23183 19451223
21331 23674 19210914
213382 23872 19441213


What I want:

A B C

19271
192713
19481 19481 19410702
19591
200173
210581
213111 213111 19400424
21331
213382

 

[ngg]

Dave,

Here is what worked for me. Thank you for your help.

In D1, I used:

=IF(ISNA(VLOOKUP($A1,$B:$C, 1, 0)), "", VLOOKUP($A1,$B:$C, 1, 0))

In E1, I used:

=IF(ISNA(VLOOKUP($A1,$B:$C, 2, 0)), "", VLOOKUP($A1,$B:$C, 2, 0))


ngg