Compare Data and Show Differences

[VBA~NOOB]

Hello,

I have four columns of data - column "a" has a name column "b" has a dollar amount column "c" has a name and column "d" has a dollar amount.

I want to compare the names in column "a" to the names in column "c" and if they match subtract column "b" from column "d" to show the difference.

I've tried everything I can think of, but can't get it to work.

Any assistance would be appreciated.

Thank you

 

[VBA~NOOB]

I think we're all over thinking this.



Compare:

A1 = C1

Subtract:

D1 - B1

If:

If(A1=C1,D1-B1,0)

That would work great if everything was neat and clean. The names are the same but not all in the same rows.

Thanks though

 

[NeonRedSharpie]

Ok, so you want them subtracted no matter what. That's also easy enough. For starters, you could make a pivot table. But if you don't want to do that, then just use a sumif function.

=SUMIF($A:$A,$C1,$B:$B)-D1

 

[VBA~NOOB]

Ok, so you want them subtracted no matter what. That's also easy enough. For starters, you could make a pivot table. But if you don't want to do that, then just use a sumif function.

=SUMIF($A:$A,$C1,$B:$B)-D1

Sorry there's always a caveat.. Not everyone in column "a" is in column "c" and vice versa

 

[NeonRedSharpie]

So...I don't see the problem with that point. If they aren't listed in column A, it will return 0-D1, which is still a result. If they aren't in column D, they won't be calculated. What is the end goal here? I seriously think a pivot table is what you need to look at based on the bits and pieces of discussion.

 

[VBA~NOOB]

If there is no value in column "a", but a value in column "c" it returns with the value in column "c"

 

[VBA~NOOB]

If there is no value in column "a", but a value in column "c" it returns with the value in column "c"

 

[Oeldere]

See the attached file, on the free dutch site

I used the formula, I posted before.

(oeldere) Example mr excel.xlsx downloaden

 

[VBA~NOOB]

See the attached file, on the free dutch site

I used the formula, I posted before.

(oeldere) Example mr excel.xlsx downloaden

Why won't it go longer than 999 rows?

=SUMPRODUCT(($A$1:$A$3000=A2)*($B$1:$B$3000))-SUMPRODUCT(($C$1:$C$3000=A2)*($D$1:$D$3000)) error message#VALUE!

Thanks!

 

[Oeldere]

VBA-Noob

It should work, I tested it.

Try it again and please reply.

 

[VBA~NOOB]

VBA-Noob

It should work, I tested it.

Try it again and please reply.

The formula works fine until I add the remainder for the rows (about 3000) then I get the error message.. confusing