#### Analysising Data from 2 worksheets

```I have 2 tables which contain data, some of which is the same, these ar
in the same spreadsheet. I know its possible to make excel match one ro
of data with another and if there isn't a match then it inserts a blan
on the side that doesn't match.

I'll give you an example

Data A                                                  Data B

Mr S - �200                                           Mr S - �200
Mr A - �150
Mrs P - �95                                           Mr P - �95

As there is no match for Mr A excel will leave a line free.

The problem is i dont know the formula for it. Any ideas

--
Message posted from http://www.ExcelForum.com

``` 0 8/11/2004 12:54:55 PM excel.misc  78881 articles. 5 followers. 3 Replies 527 Views Similar Articles

[PageSpeed] 59

```Write an if formula in a new column.

Example:

Column   A     B
Row
1        Doe, Jack
2        Doe, Jake
3        Doe, Jake
4        Doe, Jennifer
5        Smith, Jen

First you want to sort the column that has distictive=20
values (i.e., last name).  Then write the if formula:

=3Dif(a1=3Da2,"Duplicate"," ") - This formula says that if=20
the row below is the same (=3D to) then display the word=20
Duplicate and if not then display a space.

Hope this helped.

>-----Original Message-----
>I have 2 tables which contain data, some of which is the=20
same, these are
>in the same spreadsheet. I know its possible to make=20
excel match one row
>of data with another and if there isn't a match then it=20
inserts a blank
>on the side that doesn't match.=20
>
>I'll give you an example
>
>Data A                                                 =20
Data B
>
>Mr S - =A3200                                           Mr=20
S - =A3200 =20
>Mr A - =A3150
>Mrs P - =A395                                           Mr=20
P - =A395
>
>As there is no match for Mr A excel will leave a line=20
free.=20
>
>The problem is i dont know the formula for it. Any ideas?
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
``` 0 8/11/2004 1:42:49 PM
```and you wont find a formula for it. Formulas return a=20
value to the cell in which it resides. you are asking a=20
formula to perform an action i.e. insert a blank cell (and=20
shift down). formulas don't perform actions. this can be=20
done with a macro. I copied your example and pasted it=20
into a black workbook. name1 at A3 name2 at c3. i then=20
wrote the code below.  both list would have to be sorted=20
the same. i tested. it works. but i suspect you will have=20
to expand the macro since it will work only on your=20
example.

Sub maccompair()
Dim ac
Dim cc
Dim acd
Dim ccd
Set ac =3D Range("A3")
Set cc =3D Range("C3")
Do While Not IsEmpty(ac)
Set acd =3D ac.Offset(1, 0)
Set ccd =3D cc.Offset(1, 0)
If ac.Value =3D cc.Value Then
Set ac =3D acd
Set cc =3D ccd
Else
Range(cc, cc.Offset(0, 1)).Select
Selection.Insert Shift:=3DxlDown
Set ac =3D acd
End If
ac.Select
Loop
End Sub
>-----Original Message-----
>I have 2 tables which contain data, some of which is the=20
same, these are
>in the same spreadsheet. I know its possible to make=20
excel match one row
>of data with another and if there isn't a match then it=20
inserts a blank
>on the side that doesn't match.=20
>
>I'll give you an example
>
>Data A                                                 =20
Data B
>
>Mr S - =A3200                                           Mr=20
S - =A3200 =20
>Mr A - =A3150
>Mrs P - =A395                                           Mr=20
P - =A395
>
>As there is no match for Mr A excel will leave a line=20
free.=20
>
>The problem is i dont know the formula for it. Any ideas?
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
``` 0 8/11/2004 1:44:13 PM
```opps, the formula needs to be =3Dif
(a1:b1=3Da2:b2,"Duplicate"," ")

You need to tell it to look at column a and b.

Hope this helps

>-----Original Message-----
>Write an if formula in a new column.
>
>Example:
>
>   Column   A     B
>Row
>   1        Doe, Jack
>   2        Doe, Jake
>   3        Doe, Jake
>   4        Doe, Jennifer
>   5        Smith, Jen
>
>First you want to sort the column that has distictive=20
>values (i.e., last name).  Then write the if formula:
>
>=3Dif(a1=3Da2,"Duplicate"," ") - This formula says that if=20
>the row below is the same (=3D to) then display the word=20
>Duplicate and if not then display a space.
>
>Hope this helped.
>
>
>
>
>>-----Original Message-----
>>I have 2 tables which contain data, some of which is=20
the=20
>same, these are
>>in the same spreadsheet. I know its possible to make=20
>excel match one row
>>of data with another and if there isn't a match then it=20
>inserts a blank
>>on the side that doesn't match.=20
>>
>>I'll give you an example
>>
>>Data A                                                 =20
>Data B
>>
>>Mr S - =A3200                                          =20
Mr=20
>S - =A3200 =20
>>Mr A - =A3150
>>Mrs P - =A395                                          =20
Mr=20
>P - =A395
>>
>>As there is no match for Mr A excel will leave a line=20
>free.=20
>>
>>The problem is i dont know the formula for it. Any=20
ideas?
>>
>>
>>---
>>Message posted from http://www.ExcelForum.com/
>>
>>.
>>
>.
>
``` 0 8/11/2004 2:20:29 PM Similar Artilces: