Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching up columns
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching up columns
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???? ngg wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching up columns
I see you have a solution at your other post.
ngg wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching up columns
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 Peterson" wrote: 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???? ngg wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching up columns
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 "Dave Peterson" wrote: I see you have a solution at your other post. ngg wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Columns | Setting up and Configuration of Excel | |||
matching columns | Excel Worksheet Functions | |||
Matching 2 columns | Excel Worksheet Functions | |||
matching on columns | Excel Discussion (Misc queries) | |||
MATCHING COLUMNS | Excel Discussion (Misc queries) |