Analysising Data from 2 worksheets

2

24rjb1979

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
 
G

Guest

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
values (i.e., last name). Then write the if formula:

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

Hope this helped.
 
F

Frank Stone

and you wont find a formula for it. Formulas return a
value to the cell in which it resides. you are asking a
formula to perform an action i.e. insert a blank cell (and
shift down). formulas don't perform actions. this can be
done with a macro. I copied your example and pasted it
into a black workbook. name1 at A3 name2 at c3. i then
wrote the code below. both list would have to be sorted
the same. i tested. it works. but i suspect you will have
to expand the macro since it will work only on your
example.

Sub maccompair()
Dim ac
Dim cc
Dim acd
Dim ccd
Set ac = Range("A3")
Set cc = Range("C3")
Do While Not IsEmpty(ac)
Set acd = ac.Offset(1, 0)
Set ccd = cc.Offset(1, 0)
If ac.Value = cc.Value Then
Set ac = acd
Set cc = ccd
Else
Range(cc, cc.Offset(0, 1)).Select
Selection.Insert Shift:=xlDown
Set ac = acd
End If
ac.Select
Loop
End Sub
 
M

Mandy

opps, the formula needs to be =if
(a1:b1=a2:b2,"Duplicate"," ")

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

Hope this helps
 
Top