1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

how can i check Over lap or value falls in between to column's values

Discussion in 'Ask an Excel Question' started by Muhammad Shuja abbasi, Dec 30, 2017.

  1. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Daer friends,


    how can i check Over lap or value falls in between to column's values, just i mentioned below i want check these values 379975 380100 from below mentioned values.


    380000 380065
    380065 380100
    380100 380125
    380125 380200
    380200 380225
    380225 380275
    380275 380325
    380325 380800
    380800 380810
    380810 380835
    380910 381000
    381000 381025
    381025 381050
    381050 381065
    381065 381085
    381085 381100
  2. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Muhammad Shuja abbasi
    =match(your_value,your_range,0)
    It would be more clear to get sample file with wanted result
    ... if that don't 'match'.
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    I am not clear on what you want , but see the attached file.

    Narayan

    Attached Files:

  4. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Respected Members & Narayan,


    Please find attached file, in this file two values are written in "A" & "B" I Want to check Values of "B" from "A" and I have required answer separately like Mentioned in "C" & "D" Highlighted in Green.

    Attached Files:

    Last edited: Dec 31, 2017
  5. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Code (vb):
    Narayan
    Respected Members & Narayan,


    Please find attached file, in this file two values are written in "A" & "B" I Want to check Values of "B" from "A" and I have required answer separately like Mentioned in "C" & "D" Highlighted in Green.

    Attached Files:

  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    719
    Hi Muhammad

    I'm not 100% sure about what you need. But I understood it like this: you want to check if values from A are in B and which are not.

    I've uploaded a file that uses a Power query (Get and transform) solution. You will notice 5 queries.
    1. gets the data from list A: I transform it so I get all values in a single column
    2. gets the data from list B: I transform it so I get all values in a single column
    -> 1+2 are saved as connection only (are handled in memory)

    -> 3, 4 and 5 are merging both list, but I use other join types.
    3. is an inner join and gives the list of matching values
    4. is a left Anti join and gives me all values from B that are not in list A
    5. is a right Anti join and gives me all values from A that are not in list B

    I hope this is what you were looking for and that this solution is workable for you.

    PS: You can use Power Query as from Excel version 2010 (free add-on) or in the later versions 2013/2016/365 it is directly available. Though in 2013 you need to activate it on the ribbon. On the blog of Chandoo there is a tutorial about that if I'm not mistaken.

    Kr
    Guido

    Attached Files:

  7. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Muhammad Shuja abbasi
    ... so You didn't test 'Match'?
    I added IFERROR ... and that combination looks 'Match' or how?

    Attached Files:

  8. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Code (vb):
    Vletm
    Dear vletm, grah guido & all Memebers


    sorry to say but still i can not get.
    In attached file, values in every Column are starting from that value and ending up to that value, for example look at the Column A in this column Values of Column (A-1) 380000 is starting and ending up to the values of Column (A-2) 380065 similarly in other column B values are starting and ending. In this case I want to check the value from 379975 to 380100 that these values in column A is falling or overlapping or matching so the answer will comes out this (from 379975 to 380000 is not available and from 380000 to 380100 is available or falls or overlapping).

    Attached Files:

  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  10. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    719
    Ok, sorry for the goof-up, Muhammad. I already thought the data lay-out was very peculiar, but now it makes sense if you say that it stands for a range of data.
    I've rewritten my PQs. Hoping these somewhat meets closer your requirements.

    Attached Files:

  11. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Hi Narayan, & Other Members

    sorry to say
    Code (vb):
    Narayan
    still i can not get, but Mr. Narayan your answer is 50% is very near which i want.
    In attached file, values in every Column are starting from that value and ending up to other value, for example look at the Column A in this column Values of Column (A-1) 380000 is starting and ending up to the values of Column (A-2) 380065 Means all values from 380000,380001,380002,380003,380004,380005,380006,380007 these are continuously……………………………………………………………………………………380058,380059,380060,380061,380062,380063,380064,380065 up to 380065 are continue values, similarly in other column B, C, D values are starting and ending. In this case I want to check the value from 379975 to 380840 that these values in column A is falling or overlapping or matching so the answer will comes out this (from 379975 to 380000 is not available and from 380835 to 380840 is not available and from 380000 to 380835 is available or falls or overlapping). Please Remember that values starting from a column and without any break these values ending to the other column values. for you reference file is attached

    Attached Files:

  12. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    719
    Sorry Muhammad Shuja abbasi, I'm getting confused.

    When I look at the result of the Power Queries, I see exactly what I believe you are looking for.
    From the Range B: 379975-380100 (the values you want to check) this range is missing in A 379975-379999 and this range is overlapping with A 380000-380100.
    I have combined both tables in a single one to make it more obvious.
    If this is indeed not fitting your requirements, then I am sorry for misunderstanding your needs.

    Kr
    Guido

    Attached Files:

  13. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Hi Mr. Kr Guido,

    Sorry dear in your file no any formula is insert and it is not which i have required please read my previous message which i sand to MR. Narayan in that message file is also attached for reference
    Code (vb):
    Kr
    Guido
  14. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    719
    There is no formulas since it uses Power Query (do you use Excel version 2016?). But I believe communication issues (both are not native English speakers) come in our way. I'll let the ninja NARAYANK991 deal with your demand. He seems to get it.
  15. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Muhammad Shuja abbasi
    ... maybe picture (chart) would help
    Check this version ... with maybe more easier test values?

    Attached Files:

    GraH - Guido likes this.
  16. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    dear vletm,

    thanks for concentrate,
    Code (vb):
    Vletm
    i have a chart for which i want checking but i have required a formula through that i can check
  17. vletm

    vletm Excel Ninja

    Messages:
    3,669
  18. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Code (vb):
    vletm
    ,


    yes there is a formulas but you changed values which i want to check if i want to check from 379975 to 380840 than answer is coming wrong please check.
  19. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Hi Mr. Narayan,
    Code (vb):
    Narayan
    i am waiting for your reply because you were near to solve my problem
  20. vletm

    vletm Excel Ninja

    Messages:
    3,669
    ouch ouch ouch
    Why You cannot change those Your own values as You need?
    and
    If something comes wrong ... then You would write which values and how those answers would come - not just - something is wrong.
  21. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    With the data from your latest uploaded file , I am not sure that formulae will work , since for one set of input values , there can be multiple output rows of data.

    VBA can certainly do the job. Is it acceptable ?

    Narayan
  22. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    Hi Vletm.
    Code (vb):
    Vletm.
    I want to check the value from 379975 to 380840 and answer will comes out this from 379975 to 380000 is not available and from 380835 to 380840 is not available and from 380000 to 380835 is available. and if data remain the same (without changing the any data) i want the answer please
  23. Muhammad Shuja abbasi

    Muhammad Shuja abbasi New Member

    Messages:
    23
    dear Narayan,
    Code (vb):
    Narayan
    I want to check the value from 379975 to 380840 and answer will comes out this from 379975 to 380000 is not available and from 380835 to 380840 is not available and from 380000 to 380835 is available. its not issue that answer will comes in different rows. actually i am Appreciating your knowledge i think you can do this. please help me because it will be very help full for my work.
  24. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    719
    Hello again Muhammad Shuja abbasi

    Sorry, but I am persevering with a Power Query solution. I know you requested formulas, but I'm doubting this can be done with "simple" formulas. I miss the solid expertise of Control + Shift + Enter to make such complex array formulas. We cannot all be Mike Girvins.

    Anyway, does a table like this one below start to make more sense to you? Here I tried my solution only for the first pair of values in B comparing to the first 3 pairs of values in A.
    upload_2018-1-3_9-35-25.png

    Attached you can find a file with the full Query, meaning for all the records in A and B.

    Adding new data below both data tables will be reflected in the green table after the refresh of PQ. If you are using Excel 2013-1016/365, PQ is available per default, but for 2010 a free add-on is available. Check out the blog of Chandoo. One of the more recent posts is about PQ.

    I hope you will get the answer you are looking for.

    Kr
    Guido

    Attached Files:

  25. vletm

    vletm Excel Ninja

    Messages:
    3,669
    You wrote:
    I want to check the value from 379975 to 380840 and answer will comes out this from 379975 to 380000 is not available and from 380835 to 380840 is not available and from 380000 to 380835 is available. and if data remain the same (without changing the any data) i want the answer please
    > I would like to get that file to see what is going on ...

Share This Page