• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Narration from Bank in front of party name

Status
Not open for further replies.

Sarfraz

New Member
Dear Sir,

I have got two Sheets one is Ledger Name and another one is BS.
BS has got narration from Bank.
Ledger Name has got Name of party from Books of accounts.
I want get result like Narration from BS in front of Party.
Eg : Party Name is UNIQUE SERVICE I want to get narration from Bank Contains Unique Service as example says.
I have highlighted yellow where the same report is expected.
these are the few example file is big please suggest a good formula.
 

Attachments

  • BS help.xlsx
    8 KB · Views: 12
Hi,

example in cell B4 :

=IF(ISNUMBER(MATCH("*"&A4,BS!$C$2:$C$4,0)),OFFSET(BS!$C$1,MATCH("*"&A4,BS!$C$2:$C$4,0),0))

Ok for B5 but not for B3 'cause of double space in UNIQUE SERVICE in BS!C2.
 
Hi,

example in cell B4 :

=IF(ISNUMBER(MATCH("*"&A4,BS!$C$2:$C$4,0)),OFFSET(BS!$C$1,MATCH("*"&A4,BS!$C$2:$C$4,0),0))

Ok for B5 but not for B3 'cause of double space in UNIQUE SERVICE in BS!C2.
Thats good sir,

But is there any solution for such a problem like double space, or can you make it basis on first 5 character and last 5 character.
 
Thats good sir,

But is there any solution for such a problem like double space, or can you make it basis on first 5 character and last 5 character.

Hi Sarfraz,

You can add TRIM in Marc's formula:
=IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0))

but now you have to enter with Ctrl+Shift+Enter

and of course you need IF statement to check if the cell is not blank:
=IF(A3="","",IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0)))
with CSE

Regards,
 
Hi Sarfraz,

You can add TRIM in Marc's formula:
=IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0))

but now you have to enter with Ctrl+Shift+Enter

and of course you need IF statement to check if the cell is not blank:
=IF(A3="","",IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0)))
with CSE

Regards,
Works Perfectly.thanks sir.
 
whether reverse is possible
i have got two Sheets one is Ledger Name and another one is BS.
BS has got narration from Bank.
Ledger Name has got Name of party from Books of accounts.
I want get result like ledger from books of accounts in front of Narration from Bank
 

Attachments

  • TempBS help.xlsx
    11.7 KB · Views: 1
Status
Not open for further replies.
Back
Top