ISNA question
 

Hi there..

I have two sets of data side by side in one worksheet. All the same
fields but one set of data has the more records than the other.

My aims is to display only the records that existing in each set.

I'm pretty crap when it comes to excel and so I'm not sure what kind
of formula I'm looking for here. vlookup? ISNA? MATCH?

I have done this =IF(ISNA(MATCH(M2,B:B, 0)),"NOT", " Found") which
tells me correctly that one is not in the other (and then I did it
again for the other side)

But what I actually just want to see is two sets of data with the same
number of rows.


Can anyone help me out here?

ISNA question
 

Are there duplicates in each column?

If no, then there are a couple of options that I like to do.

#1. Create a new worksheet and combine both lists into on giant column. Say
column A of sheet2.

Then use data|Filter|advanced filter to eliminate the duplicates and put the
result in column B

Debra Dalgleish explains how to do this last step:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then I delete column A.

Then I use a couple of formulas in column B and C:

=isnumber(match(a2,sheet1!a:a,0))
(in B2)
=isnumber(match(a2,sheet1!b:b,0))
(in c2)

And drag down as far as required.

Then I apply data|filter|autofilter on those 3 columns.

Filtering to show True in column B and then True in column C shows me the items
in both lists. I can filter to show False/True or True/false and see what items
are missing from the opposite list.


#2. Add a header to row 1 and try this macro that I've saved this from a few
previous posts:

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: Both these techniques are useful if there are no duplicates in each
of the columns.

Honkey Lips wrote:

Hi there..

I have two sets of data side by side in one worksheet. All the same
fields but one set of data has the more records than the other.

My aims is to display only the records that existing in each set.

I'm pretty crap when it comes to excel and so I'm not sure what kind
of formula I'm looking for here. vlookup? ISNA? MATCH?

I have done this =IF(ISNA(MATCH(M2,B:B, 0)),"NOT", " Found") which
tells me correctly that one is not in the other (and then I did it
again for the other side)

But what I actually just want to see is two sets of data with the same
number of rows.

Can anyone help me out here?

--

Dave Peterson

ISNA question
 

On Jun 22, 8:41 pm, Dave Peterson wrote:
Are there duplicates in each column?

If no, then there are a couple of options that I like to do.

#1. Create a new worksheet and combine both lists into on giant column. Say
column A of sheet2.

Then use data|Filter|advanced filter to eliminate the duplicates and put the
result in column B

Debra Dalgleish explains how to do this last step:http://www.contextures.com/xladvfilter01.html#FilterUR

Then I delete column A.

Then I use a couple of formulas in column B and C:

=isnumber(match(a2,sheet1!a:a,0))
(in B2)
=isnumber(match(a2,sheet1!b:b,0))
(in c2)

And drag down as far as required.

Then I apply data|filter|autofilter on those 3 columns.

Filtering to show True in column B and then True in column C shows me the items
in both lists. I can filter to show False/True or True/false and see what items
are missing from the opposite list.

#2. Add a header to row 1 and try this macro that I've saved this from a few
previous posts:

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: Both these techniques are useful if there are no duplicates in each
of the columns.





HonkeyLips wrote:

Hi there..

I have two sets of data side by side in one worksheet. All the same
fields but one set of data has the more records than the other.

My aims is to display only the records that existing in each set.

I'm pretty crap when it comes to excel and so I'm not sure what kind
of formula I'm looking for here. vlookup? ISNA? MATCH?

I have done this =IF(ISNA(MATCH(M2,B:B, 0)),"NOT", " Found") which
tells me correctly that one is not in the other (and then I did it
again for the other side)

But what I actually just want to see is two sets of data with the same
number of rows.

Can anyone help me out here?

--

Dave Peterson- Hide quoted text -

- Show quoted text -





Dave...thanks so much for your effort...

that's brilliant..


Willl give it a go now.