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

Date within the range for each ID and type

T

Tharabai

Member
I have a file with ID, Type, From date, To date and amt charged.

All I have to do is for the "Type - Toll" there will be From date and To date.

For Each ID in the "Type - Toll", I have to check whether there is "Type - Mil" for that ID within the date range of Type - Toll. If there is, then those to be highlighted as "Overlap" in comments column. I have to compare like this for a wide range of data (about 23,000 rows)

I have attached the sample spreadsheet for reference.
 

Attachments

  • Compare.xlsx
    13.1 KB · Views: 4
Press the button.
I did 'New Comments' -column because there are some differences with sample file and my version.
 

Attachments

  • Compare.xlsm
    25.7 KB · Views: 3
Hi,

I tried with large data..

If the type “Mil” has both “from date” and “to date”, it is not working L ,if we interchange the types, its not working. Can you please assist.

I have tried with two types “Mil” and “Toll”, can we do this for the same Type also.
 
Now,
This works like "Toll" - "Mil" -comparing.
1) There have to find word "Toll" from [Type]-column.
2) There have to be both dates.
3) There have to find previous row's 'Id-value' from [Id]-column (once) with word "Mil" from [Type]-column.
4) There is no matter if "Mil" has both dates or not! A 'from date' have to be!
5) A 'from date' have to be between 'Toll's dates'.
This should work this way.
How do You want to change rules?
What is not working?
"Toll" - "Toll" comparing is possible after minor modification.
If You need more 'keywords', it should be possible too ...
Could You send Your large data, with notes/comments...what ever,
How would You want to work this different way?
 
If there is both "From Date" and "To Date" in the Type "Mil" its not working. And the "Mil" data is more than the "Toll" data. Will the above code work in this case.

Also, I need to compare "Toll" to "Toll" with ID or Mil to Mil or any other keyword. Overlap within each type and overlap with other types both required.
 

Attachments

  • Compare All.xlsx
    124 KB · Views: 2
Last edited:
Q> If there is both "From Date" and "To Date" in the Type "Mil" its not working.
So far, it marks "Overlapping" ... Do You want that NO marking?

Q> the "Mil" data is more than the "Toll" data. ...
If "Mil"-datas "From date" is between or same than "Toll"-datas dates, this will mark "Overlap of row#" ... and if same ID's.
Could You send Your data, where You find the reason of Your question?

Q> I need to compare "Toll" to "Toll" with ID or Mil to Mil or any other keyword
Hmm... "Toll to Toll", "Toll to Mil", "Mil to Toll", "Toll to Toll" and so on
Do You really mean "Every Type" to "Every Type" ...
Or could You limit a little? Could You tell where are other keywords?
 
A > Just the comment as “Overlap” will do.
A> If "Mil"-datas "From date" is between or same than "Toll"-datas dates, this will mark "Overlap of row#" ... and if same ID's. – Yes, this is right and it should be other way also, if the "toll" data is inbetween or same than Mil then it should mark as overlap.

A> Toll – Toll, Mil to Mil for each ID. For each type in the ID, I have to compare. For Ex, if the ID 12345, there are 4 type (Toll, Mil, Food, Travel) then I need to find overlap for all the 4 (Toll – Toll, Mil – Mil, Food – Food and Travel – Travel)

In the file i have attached in the previous post has all the Types.

1. I will use “Toll” to “Mi” and “Mil” to “Toll” comparision in one file
1. Comparison on each Type for the each Id in another file.
It Wil be fine if it is two different coding but the source file for the comparison is same.
 
Last edited:
I see I see.
I have an idea

...but the last sample file has 5090 same ID's ( every row has same ID ),
then the 'Comment'-column will be ... at least long.
the 1st file has many kind of ID's.
 
I've tested with Sheet1 (2) -data.
This version should work with every sheet (if there is data).
There are same modifications ...
You can name any keywords in 'light green area' [F1:Z1].
After run the macro, You'll get 'the result'.
Every keyword has now own column.
I shorted result texts; like 'Overlap' has also 'comment' and #row.
I didn't have so much test all possible things, so test ... test.
Let's make this work.
 

Attachments

  • tharabai.xlsm
    358.5 KB · Views: 3
Hi, Thank you so much for the coding...

But am very confused on seeing the output. Ex, I took "stay" type in which I have multiple results with #, overlap and overlap with #.

Is that column display comparison of "Stay" with "stay" or with all types.

Am Mil and Toll or Toll or Mil comparison is in which column as I do see both columns separately.

Please clarify.
 
'Overlap':
'Overlap' comes only to those cells which are 'like master' (both dates).
Check Cell [K5]: "Expense Type" is 'Toll' =>'Overlap'-text goes to 'Toll'-column => because #6's 'Expense From/On Date' is between #5's dates => 'Overlap's comment-text "Food#6". The last part of 'Overlap #2'-text (#2) tells that #6's 'Master' (Overlap) is #2. You can see same in Cell[K2]'s one part of comment
=> 'Food#6'. 'Master' like [K2] comment tells all row which it 'Masters'.
Text 'Overlap' can occurs only once in one cell.

'#N': ... 'Not master'
Text comes to those rows which have 'Master'.
Now, it goes to 'Not master's column. Check Cell[G3], 'Master' is #2 and 'Not master's "Expensive Type" is 'Food'. If there are more that one 'Master' like in Cell[G6], it will show like this.
If You want, of course it can go under "Toll"-column too.

Or ... If You want only two columns 'Overlap' and 'Not Overlap',
but still there are 'Master' - 'Not Master' -combinations.

As I told, if You copy some data to like 'Sheet1' and mark wanted 'type' in range "F1:Z1", same Marco will makes the result to that sheet.
NOTE: 'Types' have to fill from cell[F1] without empty cells, just like in sample.
... that is possible to modify too.

Did You get answer?
I can do modifications ...
 
Thank you for the clarification. Still it is very confusing for me..

To be clear.. all I need to do is...

1. Toll to Mil and Mil to Toll in one column
2. For each type in each Id, overlap to be find (comments should be "overlap") in a single column.

As I have to put a pivot at last to find only the overlap types. if I have too many comments split in columns, consolidation and putting pivot will be difficult. So I request you to have only two columns one for "Toll" to "Mil" and viceversa and other column for All other types. if there is overlap then "overlap" comments tats all..
 
Something is possible ...
1) You use 'Filter'-button to search You wanted 'Expense Types' - Like now 'Mil & Toll' => those selected types will set automatic after press [ Solve ]
This always set all possible 'Expense Types', no matter what has manually written to area "F1:Z1".
2) Press [ Solve ] ... to solve ...
3) E-col = 'Overlaps' will show text 'Overlap' if need and cell's comment will shoe information of 'Overlapping'
Like [E51]: 'Overlaps', and comments shows what/which overlapping; 'Mill#54 & Toll#56'
4) Every selected (instruction #1) 'Expense Type' has own column. The cells will shows what/which 'Expense Type row' overlaps and cell's comment will show information of 'Overlapping'.
Like [F54]: '#54, #56', and comment shows what/which are Overlap items; 'Toll#54, Toll#56'
Better now.
 

Attachments

  • tharabai.xlsm
    341.8 KB · Views: 6
I have selected the Id 10002 to check the types "Toll" and "Mil". in E2 in found the comment as Toll#9, Toll#10, Toll#18, Toll#19, Toll#20, Toll#21, Toll#24, Toll#30, Toll#33, Toll#38. The Toll # 18 does not belong to ID 1002.

Instead of comment can we use some other way to show the data. Also, if I put pivot for this it will be difficult as the row number changes in pivot than source data.

I know am bothering you much .. still ....
 
#2: 10002 Toll 03/11/2014-31/12/2015
#18: 10002 Toll 17/04/2015-17/04/2015
=> Same ID's Okay? ,
and 03/11/2014 <= 17/04/2015 <= 31/12/2015 Okay?
What makes 'not belong'?

about Comments... We/You can hide those comments...
Is it better that You can see only text 'Overlap' but You cannot know what/which makes 'Overlap' ... same same with the other type comments?
'Overlap'-text can cause from many rows.

Of course, it's possible to do like 'no change with Overlap and every #row will show in own cell' ... but ... is it better?
Sample =>
A | | E | F | G | H | ...
#109 | ... | | #103 |
#110 | ... | Overlap | #113 | #115 |
#113 | ... | Overlap | #110 | #115 |
#115 | ... | | #110 | #113 |
#490 | ... | Overlap | #484 | #485 | #489 |

about Row-number... It's not so easy to do this with fixed row-number!
Every time when You will change data, Row-numbers can change too!

And ... You don't need to use pivot-table to find 'Overlaps'.
Just 'Filter' 'Overlaps'!
Check photos!
 

Attachments

  • All selected.png
    All selected.png
    18.1 KB · Views: 14
  • Only Overlaps from selected.png
    Only Overlaps from selected.png
    16.7 KB · Views: 16
Let me try how this works and get back to you in case of any issues.

Thank you for all your support you have given to me.. thank you so much..
 
You must log in or register to reply here.
Back
Top