How to devive same word when few characters in 2 words are different.

**lalaarif1** · 10-11-2018, 03:11 PM

Dear Friends,

PFA

1. In col U, when I give the formula, it sometimes it shows "#N/a".
Is there any formula which gives the output mentioned in col V.?

2. Is there any formula which give the output mentioned in col W?

Please assist.

I need to reconcile huge database. Manually exercise is very time consuming.

Please assist.

**¯\_(ツ)_/¯** · 10-11-2018, 03:19 PM

To answer your first question, try:

=IFERROR(IFERROR(VLOOKUP(T2,$B$11:$C$14,2,0),VLOOKUP(T2,$B$2:$C$4,2,0)),"")

To answer your second question, try:

=IFERROR(IFERROR(VLOOKUP(T2,$B$11:$D$14,3,0),VLOOKUP(T2,$B$2:$D$4,3,0)),"")

**FDibbins** · 10-11-2018, 03:19 PM

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional

)

**lalaarif1** · 10-12-2018, 12:15 AM

Wow...
Thanks a lot... u made my day.

**FDibbins** · 10-12-2018, 12:19 AM

Your post does not comply with Rule 6 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification.

All participants:
Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

**lalaarif1** · 10-12-2018, 01:53 AM

Dear Friends.

This is in continuation of my previous post. I apologise for my previous post's title.

PFA

Is there any way where we can derive output mentioned in W5, W6.

Formula in cells W2:W4 is give by the forum. But it fails in W5, W6.

I need to reconcile huge database and manually doing the task is very time consuming and there are chances of making errors also.

Please assist.

**FDibbins** · 10-18-2018, 01:41 AM

lalaarif1 you will get no further help until you comply with my request to show a link to other sites you posted this on. I know of at least 1 member who is waiting to assist you.

This is not optional, and further disregard of my request could result in your privileges to this site being removed.

**lalaarif1** · 10-18-2018, 01:58 AM

Dear FDibbins,

I have not posted on any other site.

**FDibbins** · 10-19-2018, 01:41 AM

lalaarif1 my apologies - for 2 different reasons...
1. I see that you had changed your title, just not on the 1st post so it would carry through - apologies for that.
2. I indicated - in error - that you had posted on another site. I have NO idea why I claimed that, so again, my apologies

**Bo_Ry** · 10-19-2018, 05:40 AM

Please try at W2 and drag down

Formula:

Please Login or Register  to view this content.

**lalaarif1** · 10-20-2018, 04:51 AM

Thanks a lot... Bo_Ry.

I am very thankful to u.

**AliGW** · 10-20-2018, 04:57 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**lalaarif1** · 10-20-2018, 05:06 AM

Bo_Ry,

I just noticed that if I put "1067" in D3, then output in W3 is "1064" which is not correct.
Correct output should be #N/a.

The sequence in D2:D6 and D11:D14 will be random sequence. But your formula takes the output in first-***-first basis.

Please assist.

**Bo_Ry** · 10-20-2018, 05:42 AM

Please give us more samples and logic on how each final bill are selected.

**lalaarif1** · 10-20-2018, 02:49 PM

Dear Bo_Ry,

PFA the revised file.

Is there any way where we can derive output mentioned in col W, X.

Please assist since the data is huge.

**Bo_Ry** · 10-21-2018, 12:25 AM

Please try at W3 press Ctrl+Shift+Enter drag to X3 and drag down

=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($B$11:$B$17)/ISNUMBER(SEARCH(INDEX($D:$D,N(IF(1,ROW($B$11:$B$17)/($B$11:$B$17=$T2)))),$D2)),1)),"")

This is assume that T3:T6 will be the same as B3:B6
If not then another long formula need to replace $D2 at the end.

**lalaarif1** · 10-22-2018, 02:25 PM

Bo_Ry,
Thanks a lot. God bless you.

1 thing.
I typed the formula in W2, pressed Ctrl+Shift+Enter drag to X2 and dragged down.
It worked absolutely fine.

**lalaarif1** · 10-26-2018, 01:54 AM

Dear Bo_Ry,

Sorry for bothering you.

I observation.

For Cell V2 and V4, the output is not correct.

Kindly assist.

**Bo_Ry** · 10-26-2018, 04:20 AM

Please try at V2

Formula:

Please Login or Register  to view this content.

But V7 will show 9 but both D2 and D7 are missing from D11 :D20,
Why does V2 show 100 but V7 is blank?

**lalaarif1** · 10-26-2018, 08:10 AM

Bo_Ry,
Sorry to inform you that formula is not working as per the desired output.
In V2 its working fine.
But in V3:V6, formula fails.

By mistake V7 was blank. Output in V7=9.

Please assist.

**Bo_Ry** · 10-26-2018, 08:35 AM

I forgot to mention that you need to press Ctrl+Shift+Enter .

**lalaarif1** · 10-29-2018, 10:55 AM

Thanks a lot.

Its working fine.

**lalaarif1** · 02-28-2019, 08:26 AM

Dear Friends,

This is in continuation of my previous post.

PFA.
Is there any formula which can give me the desired output mentioned in W2:W9.

The database is huge and it takes lot of time doing it manually.

Please assist.

**Bo_Ry** · 02-28-2019, 10:10 AM

Please try at W2

=IFERROR(LOOKUP(,-SEARCH(LEFT(D2,3),$D$11:$D$19),$D$11:$D$19),D2)

**lalaarif1** · 03-05-2019, 03:25 AM

Hi Bo_Ry,

PFA
Sorry to inform you that formula fails in W5. Maybe its bcos of different party names.

Please assist.

**Bo_Ry** · 03-05-2019, 03:41 AM

D14 =KHR/00099/2017
D18 =99
Both cells have 99 from D5
What is logic for select D14, not D18 ?

**lalaarif1** · 03-05-2019, 04:59 AM

D14 =KHR/00099/2017 ---> Party name is ZAID MOHD YUNUS MOMIN (Cr)
D18 =99 ---> Party name is MARUTI ENTERPRISE (Cr)

Our reconciliation is based on Party name wise and bill no. wise.

Please assist.

**Bo_Ry** · 03-05-2019, 06:34 AM

So for W6 859, Party name U6 MARUTI ENTERPRISE (Cr)
D15 859, U15 Party name ZAID MOHD YUNUS MOMIN (Cr) not match.

Why do you choose 859, not D6 859/17-18?

**lalaarif1** · 05-16-2019, 03:07 PM

Hello Bo_Roy,

This is in continuation of the post.

PFA the revised file.

Can you please help me in modifying the formula ?

The output in R571 and R572 is not correct.
Correct output is mentioned in T571 & T572.

Note :
If GST no. (P) is same, then output should be Invoice no. (R)

Please assist since the data is huge.

**lalaarif1** · 05-17-2019, 08:31 AM

Friends,
Please assist ...

**JeteMc** · 05-21-2019, 08:51 PM

Modify the formula to read:

Formula:

Please Login or Register  to view this content.

Remember to activate by holding the Ctrl and Shift keys while pressing the Enter key.
Let us know if you have any questions.

**lalaarif1** · 05-23-2019, 01:29 AM

Dear JeteMC.

Thanks a lot for the formula. It works fine in some.
PFA the different situation where the formula is failing. (these are just example).

Is it possible to have a formula which can work in all situation.

Please assist.

**lalaarif1** · 02-28-2020, 04:02 PM

Dear All,

This is in continuation of the previous thread.

PFA the revised file.

I am unable to get the value in U8:U10.
Correct values are given in W8:W10.

In U12:U14, values are picked up correctly.

Could you please assist.

**FDibbins** · 02-29-2020, 03:17 PM

Seeing as this thread is a year old, I suggest you start a new thread and reference this 1

Thanks

How to devive same word when few characters in 2 words are different.

re: How to devive same word when few characters in 2 words are different.

re: How to devive same word when few characters in 2 words are different.

How to remove #N/A in formula using vlookup

re: How to devive same word when few characters in 2 words are different.

How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Re: How to devive same word when few characters in 2 words are different.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Replace Random Chinese Words (Not Characters) with Blank Spaces in MS Word

[SOLVED] search for a specific word and copy the word and the preceeding words until a comma

Bookmarks

Bookmarks

Posting Permissions