Muhammad Shuja abbasi
New Member
Mr, vletm
file is attached for your reference
file is attached for your reference
how can i check Over lap or value falls in between to column's values
- Thread starter Muhammad Shuja abbasi
- Start date
Muhammad Shuja abbasi - okay
1) Sometimes it would help if sets calculation automatic!
2) I copied rows 5 to 7 to my version ...
3) I (tried to) compare[d] 'my values' with Yours...
ex note: How You have 380000 in both categories?
4) I enclosed my version with graph.
5) Would You explain what should be different?
6) ... someway this seem to something same,
which I have 'done' a long time ago 'road works'?
1) Sometimes it would help if sets calculation automatic!
2) I copied rows 5 to 7 to my version ...
3) I (tried to) compare[d] 'my values' with Yours...
ex note: How You have 380000 in both categories?
4) I enclosed my version with graph.
5) Would You explain what should be different?
6) ... someway this seem to something same,
which I have 'done' a long time ago 'road works'?
Muhammad Shuja abbasi
New Member
Code:
vletmsir exactly this for road work, i want to check the whole value of column B from Whole values of Column A not one by one like you done. like MR. Narayan done 50% Correct you can check that file which is attached on my yesterday's reply at 4:12 AM. thanks for concentrate.
GraH - Guido
Well-Known Member
Hi Muhammad Shuja abbasi
Would this be what you are after?
It is just logical formulas (a nested IF statement) that identifies 6 scenarios:
1. range A starts and ends before start of range B
2. range A starts and ends after end of range B
3. range A starts before start of range B, but ends in Range B
4. range A starts and ends within range B
5. range A starts in range B, but ends after end of range B
6. range B starts and ends within range A
File containing formulas is added. I could only find a workable solution after reworking your initial data lay-out. The PQ solution would also work, but I have made a logical error somewhere and I do not find it.
Could this meet the requirement?
Kr
Guido
Would this be what you are after?
It is just logical formulas (a nested IF statement) that identifies 6 scenarios:
1. range A starts and ends before start of range B
2. range A starts and ends after end of range B
3. range A starts before start of range B, but ends in Range B
4. range A starts and ends within range B
5. range A starts in range B, but ends after end of range B
6. range B starts and ends within range A
File containing formulas is added. I could only find a workable solution after reworking your initial data lay-out. The PQ solution would also work, but I have made a logical error somewhere and I do not find it.
Could this meet the requirement?
Kr
Guido
Muhammad Shuja abbasi ... for road work
There are two sheets.
1) Sheet1: read instructions from Cell[K4] and [P4]
2) mode1: make selection below Your red B
... and get results to columns G..I
3) Press [ReFresh]-button
... SEE Your road work ... as almost as previous same kind of version.
... You can scroll that sheet and find You ranges visual ...
There are two sheets.
1) Sheet1: read instructions from Cell[K4] and [P4]
2) mode1: make selection below Your red B
... and get results to columns G..I
3) Press [ReFresh]-button
... SEE Your road work ... as almost as previous same kind of version.
... You can scroll that sheet and find You ranges visual ...
Muhammad Shuja abbasi
New Member
Dear Vletm,
I am very thankful for your cooperation, I want to check one by one values of “B” of K5 & L5, K6 & L6, K7 & L7, K8 & L8 continue up to last values of column K & L Values from column D & E
Values of “A” means values of “B” of K5 & L5 I want to check whole values of Column D & E that value of K5 & L5 falls or overlap between how many values of D & E. and if I am check values of K5 (379975) & L5 (380840) so the answer will comes this( 379975 to 380000 not available) & from (380000 to 380835 is matching) & from 380835 to 380840 not available). But remember I want check one value of Column K & L with whole values of Column D & E.
I am very thankful for your cooperation, I want to check one by one values of “B” of K5 & L5, K6 & L6, K7 & L7, K8 & L8 continue up to last values of column K & L Values from column D & E
Code:
vletm
Last edited:
#26 You wrote: i want to check the whole value of column B from Whole values of Column A not one by one like you done.
#31 You wrote: I want to check one by one values of “B” of K5 & L5, K6 & L6, K7 & L7, K8 & L8 continue up to last values of column K & L Values from column D & E
Would You be lucky that ... now You can do that both ways!
Select 'row's values from 'K3:L3' - okay?
Your long interesting sentence maybe have some connection of next
so the answer will comes this( 379975 to 380000 not available) & from (380000 to 380835 is matching) & from 380835 to 380840 not available).
If answer is as You have written,
then You would explain where/how those values red comes from those row 5 values ...
or do not write loooooooog sentences!
Value 380835 can find from cell [E14].
If You 'whole' means ... all ... even then
maybe better way to see those values from 'Sheet2':
I just cut those Your needed values for You.
So far,
from Sheet1, You could find one-by-one those values and
from Sheet2, You could see all values as those would be out there (road)!
I recommend to use Sheet2,
because there could be 'missing parts' with 'values A' and
those would be interesting to show only with numbers.
as You wrote:
But remember I want check one value of Column K & L with whole values of Column D & E.
#31 You wrote: I want to check one by one values of “B” of K5 & L5, K6 & L6, K7 & L7, K8 & L8 continue up to last values of column K & L Values from column D & E
Would You be lucky that ... now You can do that both ways!
Select 'row's values from 'K3:L3' - okay?
Your long interesting sentence maybe have some connection of next
so the answer will comes this( 379975 to 380000 not available) & from (380000 to 380835 is matching) & from 380835 to 380840 not available).
If answer is as You have written,
then You would explain where/how those values red comes from those row 5 values ...
or do not write loooooooog sentences!
Value 380835 can find from cell [E14].
If You 'whole' means ... all ... even then
maybe better way to see those values from 'Sheet2':
I just cut those Your needed values for You.
So far,
from Sheet1, You could find one-by-one those values and
from Sheet2, You could see all values as those would be out there (road)!
I recommend to use Sheet2,
because there could be 'missing parts' with 'values A' and
those would be interesting to show only with numbers.
as You wrote:
But remember I want check one value of Column K & L with whole values of Column D & E.
Muhammad Shuja abbasi
Are these values what You should would like to see?
Color tells which category those belongs.
I didn't check how this works if someone would like to skip one part of 'value A'.
Do not want to know how do this work?
or ... Check 'Sheet2'!
Are these values what You should would like to see?
Color tells which category those belongs.
I didn't check how this works if someone would like to skip one part of 'value A'.
Do not want to know how do this work?
or ... Check 'Sheet2'!
Muhammad Shuja abbasi
New Member
Vletm,
,
exactly this is 100% same which i have needed. but it is possible through the formula? not through visual basis and an other little changing i want in the answer just see answer at top of column "N" (Not Available Values) i want answer like this (379975...380000) and similarly in Column "O" (380000... 380035) and in column "P" (380035...380040).
Code:
vletmexactly this is 100% same which i have needed. but it is possible through the formula? not through visual basis and an other little changing i want in the answer just see answer at top of column "N" (Not Available Values) i want answer like this (379975...380000) and similarly in Column "O" (380000... 380035) and in column "P" (380035...380040).
Muhammad Shuja abbasi
1) This would be solved for many day ago if there would be needed information.
So far, Your needed 'A-values' are ... from 380000 to 383550, not those 27rows!
Now, it is possible to take ex 10th rows values away ... and You would get needed results.
2) If more that one 'A-values' then there could be more than three answers (N,O&P).
3) ... what would Your question but it is possible through the formula?
ReRead my #1 and fill 383550 to cell E5!
4) As I have written ... I have done this kind of earlier too.
These can do many way ... even more clear => Sheet2.
1) This would be solved for many day ago if there would be needed information.
So far, Your needed 'A-values' are ... from 380000 to 383550, not those 27rows!
Now, it is possible to take ex 10th rows values away ... and You would get needed results.
2) If more that one 'A-values' then there could be more than three answers (N,O&P).
3) ... what would Your question but it is possible through the formula?
ReRead my #1 and fill 383550 to cell E5!
4) As I have written ... I have done this kind of earlier too.
These can do many way ... even more clear => Sheet2.
Muhammad Shuja abbasi
New Member
vletm,
in sheet2 values are mentioned in gaps, not continue just like for example 380000 380835 i have required not like mentioned in sheet2
in sheet2 values are mentioned in gaps, not continue just like for example 380000 380835 i have required not like mentioned in sheet2
Code:
vletmMuhammad Shuja abbasi
Would I write what would I require?
Would I write what would I require?
Muhammad Shuja abbasi
New Member
vletm,
yes you can write which you want
Code:
vletmyes you can write which you want
Muhammad Shuja abbasi
New Member
Muhammad Shuja abbasi
Of course, You haven't change anything ... like ... deleting 'Sheet2'?
I tested my version and it don't give that message.
... I found other one ... if press that button from 2nd sheet - fixed
Of course, You haven't change anything ... like ... deleting 'Sheet2'?
I tested my version and it don't give that message.
... I found other one ... if press that button from 2nd sheet - fixed
Last edited:
Muhammad Shuja abbasi
New Member
Code:
vletmstill its not working same message is apearing
Muhammad Shuja abbasi
New Member
Code:
vletmif you have the simple formula for checking same work so please shear actually this is in visual basis not a simple formula
Muhammad Shuja abbasi
New Member
Code:
vletmhow can i read 42, i can not find 42
Muhammad Shuja abbasi
New Member
Code:
vletmi find and read my reply, i only asking you, if you have simple formula for checking that work so please shear with me because which file you sheared, that file that is in visual basis
Muhammad Shuja abbasi
1) I'm someones dear ... but that would be another story.
2) You only asking ... but You won't answer Yourself, have You noticed it?
3) case 'simple formula'
3a) as written in #32's the last line
3b) as written in #35 ... even try to reread it
3c) as written in #21 ... even try to reread it
4) The previous file...
4a) Did You watch that file? It includes VBA too.
4a) Are You allergic or something with VBA?
4b) Can You even try learn to use something new?
1) I'm someones dear ... but that would be another story.
2) You only asking ... but You won't answer Yourself, have You noticed it?
3) case 'simple formula'
3a) as written in #32's the last line
3b) as written in #35 ... even try to reread it
3c) as written in #21 ... even try to reread it
4) The previous file...
4a) Did You watch that file? It includes VBA too.
4a) Are You allergic or something with VBA?
4b) Can You even try learn to use something new?
Muhammad Shuja abbasi
New Member
Code:
vletmyes i learned lot of new things and also i am using, but unfortunately that file is not working, i am only asking you that if you have any simple formula for that work so please shear because you told that you done that many ways.
Muhammad Shuja abbasi ... this seems to be ... I gotta try to remember sheet policy!
Okay
... You cannot read older replies
... You cannot answer questions
... You cannot figure that there is NO ANY SIMPLY formula ( like =a+b) !
... and You still write 'I want'.
If something not working
... then it is challenge to modify, if You cannot tell details!
Any file won't work ex if You don't use it ,
... of course there could be other reasons too!
Here is another file which would give answers for You.
There is
> two kind of versions of 'formula'-solution
> two kind of versions of VBA-solution.
There is NO formula version ... like =a+b!
Have a nice day.
Okay
... You cannot read older replies
... You cannot answer questions
... You cannot figure that there is NO ANY SIMPLY formula ( like =a+b) !
... and You still write 'I want'.
If something not working
... then it is challenge to modify, if You cannot tell details!
Any file won't work ex if You don't use it ,
... of course there could be other reasons too!
Here is another file which would give answers for You.
There is
> two kind of versions of 'formula'-solution
> two kind of versions of VBA-solution.
There is NO formula version ... like =a+b!
Have a nice day.
GraH - Guido
Well-Known Member
I believe to finally have found a way of dealing with this... With Power Query Lists functions.
Only posting it as an alternative and perhaps learning opportunity, since the requester is looking for a formula only solution. In the previous PQ trials there were some major logical issues due to lacking "M"-function insight.
Only posting it as an alternative and perhaps learning opportunity, since the requester is looking for a formula only solution. In the previous PQ trials there were some major logical issues due to lacking "M"-function insight.