Need a correct formula
Source:
ID Entity Name Trade Date Side
88k BC 66.00 18-Aug-09 S
1391 BED 89.00 18-Aug-09 P
74890 BED 74.00 18-Aug-09 P
74891 BED ABCD0036 18-Aug-09 S
74892 BED ADFGH0004 18-Aug-09 S
74893 BED XFGT0057 18-Aug-09 P
74894 BED CVBFGR0058 18-Aug-09 P
74895 BED VVBRTG0041 19-Aug-09 P
74896 BED FGFGFHC0 19-Aug-09 P
74897 BED GHGHTGH0183 19-Aug-09 P
Target:
ID Entity Name Trade Date Side
88k BC 66.11 18-Aug-09 S
74894 BED CVBFGR0058 18-Aug-09 P
74897 BED GHGHTGH0183 19-Aug-09 P
1391 BED 89.00 18-Aug-09 P
74890 BED 74.00 18-Aug-09 P
74895 BED VVBRTG0041 19-Aug-09 P
74896 BED FGFGFHC01 19-Aug-09 P
74891 BED ABCD0036 18-Aug-09 S
74892 BED ADFGH0004 18-Aug-09 S
7489222 BED ADFG 18-Aug-09 S
Requirements:
1. If ID from the source matches with the target then need to check whether the corresponding columns are same.
2. If Records under the name is a number then the number needs to be converted to an integer and then compare based on ID.
I used the below formula
=IF(ISNUMBER(MATCH($A2,$A$15:$A$24,0)),ISNUMBER(MATCH(1,INDEX(($A$15:$A$24=$A2)*(IF(ISNUMBER(B2),INT(B$15:B$24)=INT(B2),B$15:B$24=B2)),0),0)),"")
It is not working.
Please help me with this
RE: Need a correct formula
Hi,
Firstly, it is not clear enough how many columns you have, because it seems that your headers are 6 (six) and the data below them are 5 (five).
In your first requirement, I suppose that you mean "... to check whether the corresponding rows are same" instead of "... to check whether the corresponding columns are same". Correct me if I'm wrong.
Your second requirement will be more clear when you explain which records belong to column Name.
Based on the above, this: "It is not working" is ambiguous (at least to me). Please, explain what actually you expect your formula to return.
It will be better if you can attach a sample file...
Best regards.