dear members,
on daily basis I check our securities against the accounts
manually.(SECURITY ID AND NOMINAL VALUE)
is there anyway that I can let excel do the work
what I mean is puting our securities in a column and the accounts securities
in another column, and if the accounts security is avaliable in our list and
the amount match it turns to green or any indication that both columns match
(SECURITY AND NOMINAL VALUE)
thank you very much

Dear Yaseen:

You need to do some type of match, this can be achieved with the following
sumproduct (I started at cell a6:

[X] [A] [b] [C] [D] [E] [F]
[6] 1 a 0 0 1 b
[7] 2 b 0 1 3 c
[8] 3 c 1 0 4 x

A and B is one set of data and E and F is the other and then in c and d put
these formula

[C6] =SUMPRODUCT(--(A6=$E$6:$E$8),--(B6=$F$6:$F$8))
[D6] =SUMPRODUCT(--(E6=$A$6:$A$8),--(F6=$B$6:$B$8))

and copy them down and where you get a one in c there is a match in e and f
and where you get a one in d there is a match in a and b.

You can then you conditional formating to highlight the cells.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Yaseen Al-Lawati" wrote:

dear members,
on daily basis I check our securities against the accounts
manually.(SECURITY ID AND NOMINAL VALUE)
is there anyway that I can let excel do the work
what I mean is puting our securities in a column and the accounts securities
in another column, and if the accounts security is avaliable in our list and
the amount match it turns to green or any indication that both columns match
(SECURITY AND NOMINAL VALUE)
thank you very much

thanx Martin,
something in my mind
does this formula also makes sure that
the issue and the amount match or it just matchs amounts with amounts and
security with security.
thank you very much

"Martin Fishlock" wrote:

Dear Yaseen:

You need to do some type of match, this can be achieved with the following
sumproduct (I started at cell a6:

[X] [A] [b] [C] [D] [E] [F]
[6] 1 a 0 0 1 b
[7] 2 b 0 1 3 c
[8] 3 c 1 0 4 x

A and B is one set of data and E and F is the other and then in c and d put
these formula

[C6] =SUMPRODUCT(--(A6=$E$6:$E$8),--(B6=$F$6:$F$8))
[D6] =SUMPRODUCT(--(E6=$A$6:$A$8),--(F6=$B$6:$B$8))

and copy them down and where you get a one in c there is a match in e and f
and where you get a one in d there is a match in a and b.

You can then you conditional formating to highlight the cells.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Yaseen Al-Lawati" wrote:

dear members,
on daily basis I check our securities against the accounts
manually.(SECURITY ID AND NOMINAL VALUE)
is there anyway that I can let excel do the work
what I mean is puting our securities in a column and the accounts securities
in another column, and if the accounts security is avaliable in our list and
the amount match it turns to green or any indication that both columns match
(SECURITY AND NOMINAL VALUE)
thank you very much