+ Reply to Thread
Results 1 to 34 of 34

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

  1. #1
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-19-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    208

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

    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)),"")

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,903

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

    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 )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

    How to remove #N/A in formula using vlookup

    Wow...
    Thanks a lot... u made my day.
    Last edited by lalaarif1; 10-12-2018 at 12:20 AM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,903

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

    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.

  6. #6
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.
    Attached Files Attached Files
    Last edited by lalaarif1; 10-12-2018 at 01:56 AM. Reason: Title was wrong

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,903

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

    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.

  8. #8
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    Dear FDibbins,

    I have not posted on any other site.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,903

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

    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

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

    Please try at W2 and drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    Thanks a lot... Bo_Ry.

    I am very thankful to u.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,605

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

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  13. #13
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

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

  15. #15
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.
    Attached Files Attached Files

  16. #16
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

    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.

  17. #17
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.

  18. #18
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    Dear Bo_Ry,

    Sorry for bothering you.

    I observation.

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

    Kindly assist.
    Attached Files Attached Files

  19. #19
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

    Please try at V2

    Formula: copy to clipboard
    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?

  20. #20
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.

  21. #21
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

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

  22. #22
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    Thanks a lot.

    Its working fine.

  23. #23
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.
    Attached Files Attached Files

  24. #24
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

    Please try at W2

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

  25. #25
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    Hi Bo_Ry,

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

    Please assist.
    Attached Files Attached Files

  26. #26
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

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

  27. #27
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.

  28. #28
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

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

    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?

  29. #29
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    Friends,
    Please assist ...

  31. #31
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,070

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

    Modify the formula to read:
    Formula: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  32. #32
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.
    Attached Files Attached Files

  33. #33
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    242

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

    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.
    Attached Files Attached Files

  34. #34
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,903

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

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

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to derive same word when few characters in 2 words are different.
    By lalaarif1 in forum Microsoft Windows Help
    Replies: 1
    Last Post: 10-12-2018, 01:09 AM
  2. Replies: 1
    Last Post: 02-21-2016, 02:39 AM
  3. Count words and characters of hyperlinked word documents?
    By asterixo500 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-19-2015, 12:39 PM
  4. Sort on number of words (words with least characters first)
    By greenpine in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-23-2012, 10:00 PM
  5. [SOLVED] Replace Random Chinese Words (Not Characters) with Blank Spaces in MS Word
    By mlexcelhelpforum in forum Word Programming / VBA / Macros
    Replies: 8
    Last Post: 08-05-2012, 06:42 AM
  6. Sort on number of words (words with least characters first)
    By greek in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-23-2010, 04:10 PM
  7. search for a specific word and copy the word and the preceeding words until a comma
    By DHANANJAY in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-31-2005, 09:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1