Prev: Pivot tables - Editing the data source shared by multiple pivot ta
Next: Deleting rows containing zeros
From: Tickfarmer on 22 Apr 2010 09:29
I have 4 columns of data. The first two columns have 500 rows and the second
two have 700 rows. I need to identify the differences between the columns.
The data is representative of the following:
Invoice # INV Amount Invoice # INV Amount
530878 0 530878 47.6
530878 0 530878 144
530878 127.2 530878 38.4
530878 31.2 530878 26.4
530878 47.6 530878 104
530878 30 530878 192
517605 55.2 517605 104
517605 68.8 517605 120
517605 0 517605 0
517605 0 517605 72
517605 72 517605 95.2
517605 95.2 517605 55.2
517605 104 517605 68.8
517605 120 517605 0

What is the best way to do this?
--
Tickfarmer
From: Brad on 22 Apr 2010 10:02
Not sure what you really want.

if you are only comparing information by row
assuming the information is in A-D(and starting on row 2) in E2 put in the
equation
=and(a2=c2,b2=d2)

my guess is that you want a different comparison than that.

Then (guessing what you want) what I would do is to have in column E the
formula (in Cell E2)
=text(a2,"000000")&text(int(B2*100),"0000000000")

Note the number of "0" in the text function only needs to be a large as the
largest number (plus 2)

copy down

in column F (in
=text(c2,"000000")&text(int(d2*100),"0000000000")

copy down

sort column E by itself

sort column F by itself

use either the match or lookup funtions on the shorter list on the longer
list.


--
Wag more, bark less


"Tickfarmer" wrote:

> I have 4 columns of data. The first two columns have 500 rows and the second
> two have 700 rows. I need to identify the differences between the columns.
> The data is representative of the following:
> Invoice # INV Amount Invoice # INV Amount
> 530878 0 530878 47.6
> 530878 0 530878 144
> 530878 127.2 530878 38.4
> 530878 31.2 530878 26.4
> 530878 47.6 530878 104
> 530878 30 530878 192
> 517605 55.2 517605 104
> 517605 68.8 517605 120
> 517605 0 517605 0
> 517605 0 517605 72
> 517605 72 517605 95.2
> 517605 95.2 517605 55.2
> 517605 104 517605 68.8
> 517605 120 517605 0
>
> What is the best way to do this?
> --
> Tickfarmer
 | 
Pages: 1
Prev: Pivot tables - Editing the data source shared by multiple pivot ta
Next: Deleting rows containing zeros