IF statement
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 |
IF statement
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 |
IF statement
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 |
All times are GMT +1. The time now is 07:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com