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

Looking for a formula as an alternative to VBA based solution

K

kaushik03

Member
Good morning all,


There was a post by "mrbilly" around 4 days ago. I provided him a solution with VBA based approach.

But before I get into the coding, I tried to resolve the problem with formula only, but I could not come up with proper formula as a solution for this problem.


Below here is the thread which which I am referring to:

http://chandoo.org/forums/topic/macro-to-evaluate-date-range-and-copypaste-corresponding-cell


My question here is to get a formula, as an alternative to VBA solution, which I could not come up with.


I am sure, someone of you would easily write a formula for this problem and help me to learn better.


Looking forward to your solution.


Kaushik
 
Hi Kaushi03,


Give me some time.. I will come back.. with more reliable solution..(for multiple Date Range)


BTW please have a look, and confirm if I am going in right direction..

[pre]
Code: 
START DATE	END DATE	EVENTS
12-11-12	15-11-12	Event1[/pre]

In E2 [code]=IFERROR(INDEX(ROW(INDIRECT($A$2&":"&$B$2)),ROW(A1)),"")

In F2 =IF(E2="","",$C$2)[/code]


https://dl.dropbox.com/u/78831150/Excel/Data%20Arrangement%20Part%201%20%28Kaushik03%29.xlsm


Regards,

Deb
 
Guys,


Not necessarily what Kaushik is looking for! Since we are handling consecutive dates, I've created a shameless copy of Keymaster's project bar chart idea.

https://www.box.com/s/3t1mfkfrkqibdjlcbkna


Edit: Deb, congrats man for your 500th post!!
 
Hi Shrivallabha..


Thanks for the pointing, that I am going in the right direction..

No.. I am not talking about above my post..

but talking about

* Joining in my Daily Free Tuition..

* Huge list of Not Payable Guide cum Friend..

* and huge satisfaction for all little compliments..

* Thanks to all my lifesaver.. who force me to change my homepage from facebook to Chandoo (thats also only in 9 months)..


Regards,

Deb
 
@Debraj Roy

Hi, half a Ninja!

I call you Nin? or Ja? :p

Regards!

PS: I join to shrivallabha's congratulations and wish you a soon first Kb.
 
Hi all,


Thank you very much for all of your responses so far..


@Debraj: Yes you are in the right direction ( as always). Looking forward to have a more robust and complete solution.

And yes, many congratulation for crossing 500 posts which, indeed, help many of us to learn excel better..


@Shri: I like your approach.. it's really very innovative and visually appealing...But what more I am interested in is to create a formula that would give me the exact result what I have got through code.


: You are looking very sweet:)


Thank you


Kaushik
 
Hi Kaushik and all...


Here is a pure formula solution. I use a helper column, but I think we can avoid it with a named formula.


See it here: http://img.chandoo.org/playground/event-date-listing-kaushik.xlsx
 
You must log in or register to reply here.
Back
Top