Home |
Search |
Today's Posts |
#1
|
|||
|
|||
It's a Match!
I have 2 columns (A and B) and each has 3 or 4 digit numbers all mixed up. Basically I need to be able to match them up (if there is a match) and I am going to delete them if they are both in A and B but I need to know which ones are ONLY in A and also which ones are ONLY in B which is what should be left...right??? Thanks, Jonathan -- goodpasture ------------------------------------------------------------------------ goodpasture's Profile: http://www.excelforum.com/member.php...o&userid=23898 View this thread: http://www.excelforum.com/showthread...hreadid=399044 |
#2
|
|||
|
|||
goodpasture Wrote: I have 2 columns (A and B) and each has 3 or 4 digit numbers all mixed up. Basically I need to be able to match them up (if there is a match) and I am going to delete them if they are both in A and B but I need to know which ones are ONLY in A and also which ones are ONLY in B which is what should be left...right??? Thanks, Jonathan Hi Jonathan Try this in column C, =IF(COUNTIF($A$1:$A$10,B1)=0,"No Match","Match"), change the range A1:A10 to suit your data, it will identify all matches so if column A contains 1234 twice and 1234 is also in column B both will show as a match -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=399044 |
#3
|
|||
|
|||
But isn't there a function in Excel called Match? I can't find much on it but I seen it listed in a function listing. Does anyone know anything about this? -- goodpasture ------------------------------------------------------------------------ goodpasture's Profile: http://www.excelforum.com/member.php...o&userid=23898 View this thread: http://www.excelforum.com/showthread...hreadid=399044 |
#4
|
|||
|
|||
I guess nevermind on that last post about the match. The IFCOUNT actualyl seems to work but something further I think I'm going to need is that I need to be able to sort them so the matches are together. I should have a group where A and B are together (matched) and then a group where there is only stuff in A and then only stuff in B. thinking about it, is there maybe a way to just find out what's matching and get rid of it and then what should be left is stuff only in A and tehn stuff only in B. -- goodpasture ------------------------------------------------------------------------ goodpasture's Profile: http://www.excelforum.com/member.php...o&userid=23898 View this thread: http://www.excelforum.com/showthread...hreadid=399044 |
#5
|
|||
|
|||
goodpasture Wrote: I guess nevermind on that last post about the match. The IFCOUNT actualyl seems to work but something further I think I'm going to need is that I need to be able to sort them so the matches are together. I should have a group where A and B are together (matched) and then a group where there is only stuff in A and then only stuff in B. thinking about it, is there maybe a way to just find out what's matching and get rid of it and then what should be left is stuff only in A and tehn stuff only in B. Hi goodpasture Try this macro it sorts the data to align the matches in the 2 columns, it assumes that Row 1 is a Header row so leaves that row alone, your data should therefore start in row 2, it also assumes data is in columns A and B 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 -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=399044 |
#6
|
|||
|
|||
Thanks Paul... Now how would I add column D,E, and F to follow along with B with this code? Thanks again, Jonathan -- goodpasture ------------------------------------------------------------------------ goodpasture's Profile: http://www.excelforum.com/member.php...o&userid=23898 View this thread: http://www.excelforum.com/showthread...hreadid=399044 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |