• 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.

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

M

Muhammad Shuja abbasi

New Member
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
 
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.
 

Attachments

  • Example.xlsx
    9.6 KB · Views: 4
Last edited:
Code: 
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.
 

Attachments

  • Example.xlsx
    9.6 KB · Views: 5
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
 

Attachments

  • Example Potential Solution.xlsx
    24.4 KB · Views: 2
Code: 
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).
 

Attachments

  • Example.xlsx
    9.7 KB · Views: 2
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.
 

Attachments

  • Example Potential Solution.xlsx
    23.2 KB · Views: 1
Hi Narayan, & Other Members

sorry to say
Code: 
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
 

Attachments

  • Example.xlsx
    11 KB · Views: 7
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
 

Attachments

  • compare ranges.PNG
    compare ranges.PNG
    90.3 KB · Views: 7
  • Example Potential Solution.xlsx
    23.2 KB · Views: 3
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: 
Kr
Guido
 
Muhammad Shuja abbasi said:
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: 
Kr
Guido
Click to expand...
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.
 
Code: 
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.
 
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.
 
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
 
Hi Vletm.
Code: 
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
 
dear Narayan,
Code: 
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.
 
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
 

Attachments

  • Example Potential Solution.xlsx
    38.8 KB · Views: 1
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 ...
 
You must log in or register to reply here.
Back
Top