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

Extract records between two dates. Excel 2010.

6

6tel

Member
Hi.


Back again, sorry. :-S


At the following Excel ‘10 document:


https://www.dropbox.com/s/663lapi607iw3uu/exercise_to_chandoo_3.xlsx


I'm trying to list the values on the "I" column (at the "calcula" worksheet) between two dates (at the "data-offsite-radian6" worksheet).


The dates that will set the criteria for extracting these records are at the "Portada" worksheet, cell "D20" (start date) and cell "D21" (end date).


The array formula I need has to be placed at the "BY8" cell of the "calcula" worksheet.


The array formula refers to another one at the "BZ6" cell in the "calcula" worksheet, which is working fine and is this:


=COUNTIFS('data-offsite-radian6'!$D:$D,">="&Portada!$D$20,'data-offsite-radian6'!$D:$D,"<="&Portada!$D$21)


However, the array formula I wrote seems to have an error (even after doing Ctrl+Shift+Enter). Can someone tell me what am I doing wrong in it?


{=IF(ROWS(calcula!BY$8:BY8)>$BZ$6," ",INDEX('data-offsite-radian6'!$D:$D,SMALL(IF('data-offsite-radian6'!$D:$D>=Portada!$D$20,IF('data-offsite-radian6'!$D:$D<=Portada!$D$21,ROW('data-offsite-radian6'!$D:$D-ROW('data-offsite-radian6'!$D$3)+1)),ROWS(calcula!BY$8:BY8))))}


The end. :-(


---------------------------------------


PS: The array formula is unfortunately not mine... I made a version from a another formula I took from a video tutorial that seemed pretty good, but considering the results, I guess I'm a bit ignorant to understand some of the things this guy is saying?


http://www.youtube.com/watch?v=6jcqN3swdW8


As he suggest, I already ran an experiment substituting SMALL by AGGREGATE, substituting IF by IFERROR, and I’m aware that I’m not pointing INDEX at the "I" column, but it doesn’t work either way. I think I’m having some sort of circular reference because of the pointing at the BY8 cell, but this is what the guy is suggesting in the video. Therefore, I ran out of ideas for tests and can't think of anything more… :-(
 
Now I'm strongly suspecting my array formula is not working because of some needed limits in the following highlighted ranges which, considering the perpetual growing of data at the "data-offsite-radian6" worksheet, will have to be dynamic (dynamic ranges):


{=IF(ROWS(calcula!BY$8:BY8)>$BZ$6," ",INDEX('data-offsite-radian6'!$D:$D,SMALL(IF('data-offsite-radian6'!$D:$D>=Portada!$D$20,IF('data-offsite-radian6'!$D:$D<=Portada!$D$21,ROW('data-offsite-radian6'!$D:$D-ROW('data-offsite-radian6'!$D$3)+1)),ROWS(calcula!BY$8:BY8))))}


Could it be that, messing everything up? Maybe I need to set some limits and need to set this ranges dynamically?
 
Hi 6tel,


As far as I can understand from your formula is, you want to fetch all the dates from Col D of "data-offsite-radian6" sheet in between the date range of >=D20 and <= D21.


After dragging the formula, the number of dates(in the rows) should be equal to the value at BZ6.


I am not able to open your file from my location.


Meanwhile, can you plz try the following and let us know if you are getting the right output:


=IF(ROWS(calcula!BY$8:BY8)>$BZ$6," ",INDEX('data-offsite-radian6'!$D:$D,SMALL(IF('data-offsite-radian6'!$D:$D>=Portada!$D$20,IF('data-offsite-radian6'!$D:$D<=Portada!$D$21,ROW('data-offsite-radian6'!$D:$D)+3)),ROWS(calcula!BY$8:BY8)))) CTRL+SHIFT+ENTER and drag down


Kaushik
 
Hi ,


The problem is a missing right parenthesis after the highlighted portion :


=IF(ROWS(calcula!BY$8:BY8)>$BZ$6," ",INDEX('data-offsite-radian6'!$D:$D,SMALL(IF('data-offsite-radian6'!$D:$D>=Portada!$D$20,IF('data-offsite-radian6'!$D:$D<=Portada!$D$21,ROW('data-offsite-radian6'!$D:$D-ROW('data-offsite-radian6'!$D$3)+1)),ROWS(calcula!BY$8:BY8))))


I have not verified whether the formula will give you what you are looking for , but including the right parenthesis will eliminate the syntax error.


Narayan
 
@kaushik03


Thanks for your help. Unfortunately, your formula is not working the way I expect. It is listing values (author names) out of date range I need. I guess it must be the "+3" you added to the formula... :-(


@narayank991


Thank you very much for your keen observation. You were right. I have corrected the formula to the below form:


Code: 
=IF(ROWS(calcula!BY$8:BY8)>$BZ$6," ",INDEX('data-offsite-radian6'!$I:$I,SMALL(IF('data-offsite-radian6'!$D:$D>=Portada!$D$20,IF('data-offsite-radian6'!$D:$D<=Portada!$D$21,ROW('data-offsite-radian6'!$D:$D)-ROW('data-offsite-radian6'!$D$3)+1)),ROWS(calcula!BY$8:BY8))))


Somehow, I noticed this formula is not listing unique distinct values... I feel I'm very close to what I need, but without the ability to extract unique distinct names I guess I still have to suffer for a bit more... Arg! :-(


The pain gets even more intense when suspecting I'll soon be needing to substitute some ranges in the above formula for dynamic ranges relating the D column... Arg (twice)! :-(


I'll have to read the two last topics I posted here at the forums and try to finally understand what others have given me as formulas to help me. But understanding them completely and getting familiar to them is so hard! (sobbing)
 
Hi, 6tel!


Sorry for the late reply but been away half work half leisure. Give a look at this file:

https://dl.dropbox.com/u/60558749/Extract%20records%20between%20two%20dates.%20Excel%202010.%20-%20exercise_to_chandoo_3%20%28for%206tel%20at%20chandoo.org%29.xlsx


I created these named ranges:

PasadoCorteStart: Portada!$D$20, start date

PasadoCorteEnd: Portada!$D$21, end date

DOR6Base: data-offsite-radian6!$C$3, first cell in range

DOR6Table: =DESREF(DOR6Base;0;0;CONTARA('data-offsite-radian6'!$C:$C)-1;CONTARA('data-offsite-radian6'!$2:$2)-2) -----> in english: =OFFSET(DOR6Base,0,0,COUNTA('data-offsite-radian6'!$C:$C)-1,COUNTA('data-offsite-radian6'!$2:$2)-2)

(whole data table at source sheet)

DOR6List: =DESREF(DOR6Table;0;1;;1) -----> in english: =OFFSET(DOR6Table,0,1,,1)

(column D at source sheet)


Now go to worksheet 'calcula' and check yellow shaded cells:

BZ6: =CONTAR.SI.CONJUNTO(DOR6List;">="&PasadoCorteStart;DOR6List;"<="&PasadoCorteEnd) -----> in english: =COUNTIFS(DOR6List,">="&PasadoCorteStart,DOR6List,"<="&PasadoCorteEnd)

(count for date ocurrences between PasadoCorteStart and PasadoCorteEnd)

BY8:BY...: =SI(FILAS(calcula!BY$8:BY8)>$BZ$6;" ";INDICE(DOR6List;K.ESIMO.MENOR(SI(DOR6List>=PasadoCorteStart;SI(DOR6List<=PasadoCorteEnd;FILA(DOR6List)-FILA(DOR6Base)+1));FILAS(calcula!BY$8:BY8)))) -----> in english: =IF(ROWS(calcula!BY$8:BY8)>$BZ$6," ",INDEX(DOR6List,SMALL(IF(DOR6List>=PasadoCorteStart,IF(DOR6List<=PasadoCorteEnd,ROW(DOR6List)-ROW(DOR6Base)+1)),ROWS(calcula!BY$8:BY8))))

(array formula to retrieve dates in selected range, entered with Ctrl-Shift-End instead of the normal Enter)


I think it retrieves the dates you required, but please check it with different values.


The dynamic named ranges DOR6Table and DOR6List let you get rid of the addition or deletion of rows in source range, and make formulas simpler and easier to read. Named ranges PasadoCorteStart and PasadoCorteEnd just do the last thing, but when I started I didn't know how I would end.


Just advise if any issue.


Regards!
 
Hi SirJB7.


Thanks for stepping in. You really are very kind.


Well, I don’t want to sound neither whiny nor ungrateful regarding this solution of yours… Let’s see how I explain myself:


At first, I’m noticing your formula at the “BY” column (in the “calcula” worksheet) is not listing the “I” column (authors) from the “data-offsite-radian6” worksheet, and this is the data I need to list (authors), not dates. But I need it under these criteria:


1.- It has to create a list of unique distinct values from the “I” column (“data-offsite-radian6” worksheet). No name repetition.


2.- It has to list these “I” column values only between the date ranges I’m setting at the Portada worksheet (cells D20 and D21) and do it continuously, one row after the other (no blanks in between)…


I guess this formula is doing something good, because it lists 22 values, as the calcula!BZ6 cell sets... But I wished it would list author names and not dates…)


Any help is tremendously appreciated. :-(


-------------------------------------------------------


PS: I feel too much intimidated at the named ranges you’ve created for almost everything in the formula at Calcula!BY8… I can understand to use them for the dynamic ranges (which I guess must be those “DOR6Table” and “DOR6List” you’ve created), but for the D20 and D21 cells at the “Portada” worksheet and the C3 cell at the “data-offsite-radian6” worksheet (“DOR6Base”) I wonder if these names are really necessary…


PS2: as an alternative, I tried using the CA column of the “calcula” worksheet as a helper since I it is an already unique distinct value list. That being said, I tried using the formula below (extracting between dates), but I wasn’t successful. It copied the same list and did not extract the list of values between dates…


At Calcula!BY8:

Code: 
{=IFERROR(INDEX(Accumulated_influencers,MATCH(0,COUNTIF($BY$7:BY7,Accumulated_influencers),0))," ")}


At Name Manager:

Named range: [code]“Accumulated_influencers”

Refers to: =calcula!$CA$8:INDEX(calcula!$CA:$CA,MATCH(REPT("z",255),calcula!$CA:$CA))[/code]
 
Hi, 6tel!


I apologize for extracting dates from column D, but I'd swear that in your originally uploaded file there were formulas at worksheet 'calcula' column BY that referred to dates in D of DOR6 ('data-offsite-radian6). Let me change them to I column Author and see what happens.


But I'm afraid that in the youtube's video (which I watched) it didn't extract unique values but yet values within a date range criteria, so only your 2nd rule is being considered. For extracting unique values you'd have to use a couple of helper columns, of pray for me to find an example I saw a few days ago but I don't remember where exactly and if it actually does these, but I'm almost sure I read something like "extracting unique values from a list".


So doing what Jack said circa 1880, let us go by parts. Please now check that your authors are being correctly pulled out instead of dates, and that they're respecting the date from/to criteria.


I added a new worksheet 'calcula_2' where I don't use any intimidating dynamic named ranges. If previous point is suitable for you, why not comparing both yellow shaded formulas at 'calcula' and 'calcula_2' worksheets? Maybe the differences are not so frightening now.


I think that the most annoying part it must be the range definitions, so join me to review them at worksheet 'freddy.vs.jason'.


I suggest you to analyze both worksheets 'calcula' and decide which one to keep, while I try to find out where did I saw the magic formula, if it exists. The only thing that changes is referring to cells and ranges as by their addresses (column A) or their defined names (column D) of frightening worksheet. ;)


Just advise if any issue.


Regards!


PS: please download again the file from same original link.
 
Hi SirJB7.


Thank you very much for the freddy.vs.jason worksheet. It has been very helpful and illustrative. So my sincere thanks.


As for the other thing, I'm very sorry for my mistake on the formula. Yes, I pointed it at the wrong place. Sorry. :-(


Hmm... You know what?, I've been thinking: Don't worry. If one day you happen to remember the URL you are telling me about, just post here the link to the information and I'll follow it. If not, please, don't waste your time. I already continued to other aspects of the template I'm preparing and, after some sort of illumination, I decided to stop being obsessed about this formula. Please go to the movies, to the beach, to sleep... :)


Again, thank you very much, mr. ninja, and please rest. Greetings.
 
Hi, 6tel!


But (and there's always a but, as my friend bobhc uses to say) do you know what? Now I'm curious and eager to find it! So tonight or tomorrow I think I'd find it.

Controlled obsession is the fuel for learning, so for his weekend I have plans for a Celtic music recital, properly irrigated with Carlsberg (and/or a new Imperial Stout Ale, recently released here), and a barbecue at a club outside the city.


Have a nice weekend too.


Regards!
 
Hi, 6tel!


Well, after this now I can go to sleep like a baby or like an angel.


Please download again the file from same previous link and check for worksheets 'calcula (2)' and 'calcula_2 (2)'. They have the unique list modifications, borrowed without permission from these two links, specifically form the 2nd. one:

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

http://www.get-digital-help.com/2009/04/16/create-unique-list-from-column-where-an-adjacent-column-meets-criteria/


The workbook is a little resource consuming for updating because of the quadruplication of worksheets, but despite of this, the new method is heavier than the older one... an expected cost, I guess.


Just advise if any issue, and good night and better weekend.


Regards!


PS: Actually the 4th worksheet 'calcula_2 (2)' doesn't get fully calculated as it doesn't circumscribe the range to used cells, taking instead the whole column... Excel doesn't support it: if you want to use the non-frightening version, you should replace whole column references for specific used ranges... So this is a strong reason to adopt dynamic named ranges references.
 
Hey, you've done it! Wow!


Well, looking at it just on the surface, I guess it is better to use the BY8 cell formula from the "calcula (2)" worksheet... I'll let you know after testing if this method pushes too much calculation processes in the file...
 
Hi, 6tel!

The worksheet 'calcula (2)' is the unique-list version of previously frightening worksheet 'calcula', and uses those scaring dynamic named ranges. This is my first choice since it's very efficient at calculation time, the one which is unbearable is 'calcula_2 (2)' which didn't end the recalc process and I had to kill Excel task twice, so in fact it's the same as 'calcula_2' since I couldn't get it working with the new formulas applied.

Summary? Delete 'calcula', 'calcula_2', 'calcula_2 (2)' and rename 'calcula (2)' as 'calcula'.

Regards!
 
You must log in or register to reply here.
Back
Top