Tie the values on the basis of 40 & 50 through client name

[Harshit]

Hi,
I have facing a problem in this file , i have a data of bank statement & client personal statement i want to tie these amount with in few minutes please provide a trick to resolve the data with in minutes. if you can guide me through any vba solution its so easy for me.

Regards
Harshit

 

[SirJB7]

Hi, Harshit!
As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/
Regards!

 

[Harshit]

i have intorduce my self. can you help out me for solve this sheet.

 

[SirJB7]

Hi, Harshit!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Tie the values on the basis of 40 & 50 through client name - Test Book (for Harshit at chandoo.org).xlsx

It uses many dynamic named ranges for easier referencing:
DataTable: =DESREF('Example File'!$A$2;;;CONTARA('Example File'!$A:$A)-1;CONTARA('Example File'!$1:$1)) -----> in english: =OFFSET('Example File'!$A$2,,,COUNTA('Example File'!$A:$A)-1,COUNTA('Example File'!$1:$1))
CompanyList: =DESREF(DataTable;;0;;1) -----> in english: =OFFSET(DataTable,,0,,1)
AccountList: =DESREF(DataTable;;1;;1) -----> in english: =OFFSET(DataTable,,1,,1)
AmountList: =DESREF(DataTable;;6;;1) -----> in english: =OFFSET(DataTable,,6,,1)
WhereList: =DESREF(DataTable;;10;;1) -----> in english: =OFFSET(DataTable,,10,,1)

It does the job at columns K:M (where?, paired match, where pair?):
K2: =SI.ERROR(COINCIDIR(A2&"_"&B2&"_"&-G2;CompanyList&"_"&AccountList&"_"&AmountList;0);"") -----> in english: =IFERROR(MATCH(A2&"_"&B2&"_"&-G2,CompanyList&"_"&AccountList&"_"&AmountList,0),"")
L2: =SI(K2="";"";SI(INDICE(WhereList;$K2)=FILA()-1;"X";"")) -----> in english: =IF(K2="","",IF(INDEX(WhereList,$K2)=ROW()-1,"X",""))
M2: =SI(L2="X";COINCIDIR(K2;WhereList;0);"") -----> in english: =IF(L2="X",MATCH(K2,WhereList,0),"")

K2 is an array formula, so remember that should be entered with Ctrl-Shift-Enter instead of just Enter.

Copy down K2:M2 as required.

Just advise if any issue.

Regards!

PS: Next time anonymize the data as indicated in the above posted link, which it wasn't only for suggesting new members to introduce themselves.