Can we filter the data without using VBA and only with the formulas?

[khelgadi]

Dear Excel gurus....

I have a specific query to be solved. I have a data in the following manner in one particular file:


Monthly Payment 2012-13

Sr. No. Particulars Frequency Type Due Date Person Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar

1 Salary & Wages Monthly Payment 5 GSK 1 1 1

2 Stock Statement(BOM) Monthly Submission 5 RSP 1 1 1

3 T D S Challan Monthly Payment 7 GSK 1 1 1

4 Intt on Term Loan Monthly Payment 9 GSK 1 1 1

5 MR Salary Monthly Payment 12 GSK 1 1 1

6 PF & ESIC Monthly Payment 15 PBP 1 1 1

7 Profession Tax Monthly Payment 20 PBP 1 1 1


In this, the concerned person writes 1, as he finishes it.

On another sheet, I wish to have a list of payments which are due and yet not been done...


How can we do that...?

Please help.

 

[khelgadi]

Counting on you.... Hui !

 

[Hui]

Khelgadi


Firstly, Welcome to the Chandoo.org forums.


Secondly, note I have removed the Urgent from your Title

I recommend that you read the following post: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting


Do you have a sample file you can upload? http://chandoo.org/forums/topic/posting-a-sample-workbook


The Table is self explanatory as to what isn't complete isn't it?


Do you want to supply a data and know what isn't complete that month?

 

[khelgadi]

Dear Hui,

Sorry for skipping those rules.... I'll go through the rules, upload the data and get back to you.thanks though Sir!

 

[khelgadi]

Dear Hui,

I have uploaded the file at http://sdrv.ms/OUjEtk

1. The first sheet contains the Entry form where different person marks the completion of their tasks

2. Sheet to should reflect to me only the tasks which are started and not yet completed

3. the format on sheet 1 can be changed.

4. for every task I can specify Start date and end date.


I hope I'm as per the guidelines.... Waiting for the reply...

 

[khelgadi]

Dear Hui,

Uploaded the data as required. Please suggest me the solution at earliest possible. Thanks.

 

[SirJB7]

Hi, khelgadi!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Can%20we%20filter%20the%20data%20without%20using%20VBA%20and%20only%20with%20the%20formulas%20-%20Annual%20Checklist%2001%20%28for%20khelgadi%20at%20chandoo.org%29.xlsx


Because of format of columns F:G I used many auxiliary columns:

Y:Z : for decoding F:G values

AA : value for previous period (always done, 1)

AB:AM : value for period corresponding to I:T columns (not done = 0, done = 1)

AN : value for period corresponding to inputted date

AO : auxiliary for INDEX function


Also defined named ranges:

CellDate : inputted cell

FrequencyList : AB1:AE2, months per period


Cell backcolor:

a) Rose Y:AO : cells added

b) Red A : you marked them in your output list, I don't get them in my output list

c) Cyan A : you didn't mark them in your output list, I get them in my output list

d) Green A : both lists equal

e) Yellow B : your output list

f) Red C : incorrect value, corrected


Observations:

1) Column F for first group doesn't get used, just column G, it's Ok? If not, explain detailed procedure calculation with start and end task's dates

2) Differences in b) and c): I think you didn't consider the due date values of column G. I tested them with G13, and if it'd be 05/Jul/2012 so until now it isn't uncompleted, but if I change G13 value to "03 of next month" it becomes selected.


Check all output and just advise if any issue.


Regards!

 

[khelgadi]

Dear SirJB7

Thanks for the Solution... In first glance, looks fine, but lemmi check it out... Actually I'm sorry for making the confusion.... in case of the result page... When i mentioned the output, i was actually referring to the format... and not the exact tasks... I'll go through and get back to you.

Thanks once again guys..

 

[khelgadi]

Thanks SirJB7 & Hui...

I made few modification in the formula you have provided... works nice...

Thanks all of you for this noble initiative...

 

[SirJB7]

Hi, khegaldi!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

 

[Guest]

• 
  <div id="hw21"><img title=" Prada Polarized Sunglasses AAA SH 34 Black " alt="Prada Polarized Sunglasses AAA SH 34 Black" src="http://www.pradaoutletsalesonline.com/bmz_cache/a/a1169f4c90a495ea390e614247063156.image.150x112.jpg" width="150" height="112" style="position: relative" /></div>
  
  <div id="hw22">
  
  <div style="height: 70px">Prada Polarized Sunglasses AAA SH 34 Black </div>
  
  <span class="normalprice">$174.95 </span> <span class="productSpecialPrice">$87.95</span><span class="productPriceDiscount">
  
  Save: 50% off</span></div>
  
• 
  <div id="hw21"><img title=" Prada Polarized Sunglasses AAA SH 33 Black " alt="Prada Polarized Sunglasses AAA SH 33 Black" src="http://www.pradaoutletsalesonline.com/bmz_cache/8/8bac451b30ab28a62b727407b78654f1.image.150x112.jpg" width="150" height="112" style="position: relative" /></div>
  
  <div id="hw22">
  
  <div style="height: 70px">Prada Polarized Sunglasses AAA SH 33 Black </div>
  
  <span class="normalprice">$174.95 </span> <span class="productSpecialPrice">$87.95</span><span class="productPriceDiscount">
  
  Save: 50% off</span></div>
  
  
  <div id="hw21"><img title=" Prada Polarized Sunglasses AAA SH 32 Gray/black " alt="Prada Polarized Sunglasses AAA SH 32 Gray/black" src="http://www.pradaoutletsalesonline.com/bmz_cache/3/33fe3bb5786520d18c4cca3963bfaae1.image.150x112.jpg" width="150" height="112" style="position: relative" /></div>
  
  <div id="hw22">
  
  <div style="height: 70px">Prada Polarized Sunglasses AAA SH 32 Gray/black </div>
  
  <span class="normalprice">$174.95 </span> <span class="productSpecialPrice">$87.95</span><span class="productPriceDiscount">
  
  Save: 50% off</span></div>
  
  
  相关的主题文章：
  
  http://www.way2zenith.com/forums/topic/dior-%e6%99%82%e8%a8%88-%e5%b0%8f%e5%8c%ba%e5%b1%85%e6%b0%91%e6%8a%84%e8%bf%91%e8%b7%af%e7%94%a8%e7%bb%b3%e7%b4%a2%e6%94%80%e5%b2%a9%e5%9b%9e%e5%ae%b6%e5%9b%be?replies=2#post-3943