Hi all,
I've been playing for a couple of days, but I'm stuck again... any advice please.
From the very small sample I've attached, can someone tell me how to enhance the VLOOKUP.
I the real world, the column of dates is long and there are variable distances between the actual date entries (which are calculated).
How do I get all of the reindeers for a particular date in range B4-D13 to show in Cols F&G.
Also how do I prevent the #N/A error if there is no date entered in F3.
Thank you,
J
Last edited by Jo-Jo; 12-09-2009 at 06:26 AM. Reason: Marked Solved.
List the repeat dates in column B in every cell,
Then in column E4, add formula:
=B4&"-"&COUNTIF(B$4:B4,B4)
copied down
Then in G3, add formula: =COUNTIF($B$4:$B$13,F3)
then in F4, add formula: =IF(ROWS($F$4:$F4)>$G$3,"",INDEX(C$4:C$13,MATCH($F$3&"-"&ROWS($F$4:$F4),$E$4:$E$13,0)))
copied to next column and down as far as you want.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If you don't want to continue the dates down, then you can add 2 helper columns
Insert a new column before column E
In E4:=LOOKUP(9.999999E+307,$B$4:B4) copied down
in F4: =E4&"-"&COUNTIF(E$4:E4,E4) copied down
In H3: =COUNTIF(E4:E13,G3)
In G4: =IF(ROWS($G$4:$G4)>$H$3,"",INDEX(C$4:C$13,MATCH($G$3&"-"&ROWS($G$4:$G4),$F$4:$F$13,0))) copied to next column and down
Hi NBVC,
Before I try, is it alright to do this, because as mentioned the dates in ColB are calculated and contain e.g. filled down:-
=IF(AND(MOD(ROW($A1:$IV1);games)=0;ROW($A1:$IV1)<weeks*games);OFFSET(B19;-games;0)+7;"")
Oops posted before I saw your second... just reading.
Hi NBVC,
I realise it's due to abilities and learning by you guys, but I can't help being in awe of how you come out with these things... it does exactly what's said on the tin
If it is not asking something above and beyond, could you just tell me a VERY brief overview of how/why that works. I'll never figure out how you've done it, but I can't get my head around the connection the numbers your formula generates to produce the text results.
Just need to port it to my real sheet and I'll chuffed.
Thank you VERY much.
J.
This formula: =LOOKUP(9.999999E+307,$B$4:B4) finds the last numeric entry in range B4:B4 by looking for 9.999999e+307 (the largest number Excel recognizes) and if it can't find it, return last occurance that is smaller than that number... as you copy the formula down, only the second B4 changes to B5, then to B6, etc... so you are looking at finding last number in those consecutive ranges... you will note that December 24th is a date (but Excel sees this as a serial number 40171 and is the last number found until you reach B9 where December 25 is displayed, from now on, this is the last number in the ever expanding range... until it reaches the next date.
This formula: =E4&"-"&COUNTIF(E$4:E4,E4) is used to count the number of times the date exists in your list... so I have concatenated the date serial number to a dash and the occurance number in the list as the formula is copied down (note: again the ever expanding range, that always begins at row 4). This formula will be used to match up against so that we can extract from column C and D...
This formula: =COUNTIF(E4:E13,G3) simply gives a count of how many cells in E4:E13 contain the date that is in G3. Used to determine when to stop extracting values from your list and return blanks instead of errors...
This formula: =IF(ROWS($G$4:$G4)>$H$3,"",INDEX(C$4:C$13,MATCH($G$3&"-"&ROWS($G$4:$G4),$F$4:$F$13,0))) is responsible for the actual matching and extracting according to the criteria... first part counts the number of rows the formula is occupying as you copy down. If the number exceeds the counted number of matches in the Countif() formula in H3, then it will return TRUE and put a blank in the cell (to avoid showing error). The INdex/Match is similar to Vlookup, but works in both directions, i.e. the lookup column can be to the right of the column you are extracting from. So we Index column C4:C13 and find a Match in column F4:F13 that matches the date in G3 (transformed to serial number), the dash and the count number (equiv. to the row number of the range your in). The row number changes as you copy down, therefore extracting corresponding item in F with same number as row number concatenated with the date.
Copying to the right, indexes column D, and with same Matching conditions.
Hope that does it.
Thanks NBVC,
I worked my way through your explanation and believe it not I actually followed the logic (I won't for the life of me be able to put it to use without continual reference, but things are slowly starting to stick). I also got it all over onto my proper sheet
One question, if you don't mind (and I'm not trying to drag you into a thread you don't want to be in), but do you think those expressions would be the kind of thing I need to try/work/adapt for my List 1 in this thread, or are they totally unsuitable?
I can see some similar functionality if I can work out the 'group' and 'page' break formatting.
Thanks again,
J.
I think that is exactly what darkyam is suggesting...Originally Posted by Jo-Jo
Hi NBVC,
I've used darkyam's suggestions, so perhaps I've missunderstood because I couldn't figure the double row (lists) and page formatting/breaking properly. The double listing hit me whilst moving your formula over my sheet (ding!... I can do this twice, kind of thought) and intergrate with darkyam's.
Thanks for your help... I'm off to have another go.
J.
Hi NBVC, darkyam, all,
I don't know if your still following this.
I've been trying to adapt your formula (and add bits), all with varying degrees of failure. Can I once again ask for help?
I'm trying to duplicate the holiday rota onto another sheet and to display like the grouped format shown in green (two lists, left to right/top to bottom). I don't need to be selective/conditional in what I duplicate, every entry from the list gets duplicated, but I do need to group by date. I also need to allow for variable numbers of reindeer and holiday dates.
I nearly had it once, I worked out an expression that I could place in groups. I don't know how, but when I moved it to another sheet I discovered that whatever I'd done had made it kind of dependant on being in the same row numbers as the list and I couldn't make my expression arbitrarily placeable... so I ditched that!
Any suggestions please?
Thanks.
J.
New sheet attached.
So you are trying to duplicate the "green" blocks onto Sheet2?
Are there are known number of rows for each...or can we set a "max" number of rows between?
Hi NBVC,
No, I'm now trying to duplicate the data in the leftside list and place it into a new sheet in the format of the green blocks (ie the left list is the source and the green blocks the desired result).
The left list is row variable, a max number could be set at 400 if needed.
Sorry if I'm confusing... hope this helps.
[E]sorry, if number of rows between is referring to rows in each group... there will be a max of 10 rows per group.[/E]
Last edited by Jo-Jo; 12-03-2009 at 02:11 PM.
See attached... hopefully what you needed...
You will just need to adjust the ranges in the first row within the 2 columns of each group to match your actual database size... then copy down all formulas within their respective groups.
Hi NBVC,
Sorry for the delay in replying. Living in the Spanish mountains, the local electricity company, plus storms = power cuts!
I'm going to download quick (in case any more black-outs) and get back to you as soon as I've tried it.
Thank you very much for your help.
J.
Hi NBVC,
In between power cuts I tried and couldn't get that one intergrated. I did so many things and kept restarting that I eventually lost track of what I had and hadn't tried.
I'm going to have another bash at it today by moving the cells in your example so that the cols, rows and sheets are all in exactly the same place and named the same as the ones I'm trying to port it over to.
Thanks,
J.
Hi NBVC, all,
I've got it.
Not sure if it was the way you intended or what I should have done, but as well as changing the row-sizes I also needed to change a couple of other 'absolute' values and copy/change each group individualy... probably something I messed up on.
One thing I was hoping was to return the date in the lookup rather than manually entering for each group. Is this something that I have messed with as well or is it not possible?
Thanks,
J.
Ok, not easy.. but see attached.
First insert a row in Sheet2 above all groups.
Then you only need to change the database ranges in A2, B2 and A3.. then copy A3 to next column and down the 10 rows of the 1st group.
Do the same in D2, E2 and D3 and copy D3 to next column and down 10 rows.
Then select range A2:E12 and copy. Go to A14 and paste, then go to A26 and paste, etc, continuing the pattern of skipping 1 row after each group and continue as far as you want...
Hi NBVC,
Many apologies for it being a difficult one. You should have said before doing it, or told me to push off and find another solution, but I am ever so grateful to you for doing this... I am off to try. I'll let you know how I get on.
VERY many thanks,
J.
Hi NBVC,
I've been trying all weekend and referring to your previous description (but I think the new expression works a little differently), but so far its beaten me.
I did as you said and changed the ranges, but I must admit to not understanding if I should change some of the ranges... the ones that go up to 60k+, I didn't know if those are arbitrary/catch-all figures or if go that high for specific purpose, I left those ones alone in the end.
The main problem I'm having is working out how to adjust the expression(s) so as to be able to place the groups (green) in a different cell location in my sheet. I seem to be able to move the groups to different columns, but everything I've tried fails if I try to move the groups to a different row. Believe it or not, I've got 43 modified versions on my desktop... all do different (wrong) things, but I can't work out this bit to allow selective placement on the sheet.
Any hints on what part(s) define the placement would be appreciated.
Thanks,
J.
PS: If this is something that involves more work or difficulty, then please say and I'll try to think of an alternative way round this.
My solution is designed to work as per the sample I provided... i.e.
Two groups of 10 side by side starting at row 2 and then each 2 groups separated by one empty row.
If you change that layout, the formulas will not work so neatly and would need fixing up.
Hi NBVC,
The two groups, side by side and one space apart vert & hori., is no problem. Nor is the column placement, because I seem to be able to adjust that. It is just the row start position (2) I have been trying to adjust.
Not to worry further, I am sincerely grateful for all of your help, I will tinker.
Thanks again,
J.
What row do you want to start at?
Hi NBVC,
I'm starting (with the date cell) at row 33, exactly as attached format.
I managed to adjust your first example and as mentioned I seem to be able to move this one around for the columns. Is there some extra magic in the second one that defines/fixes the row?.
I'm, as ever, grateful for your help, but I'm also happy keep tinkering if there is something daft I've missed and you can perhaps point me in the right direction.
See attached.
First you have to change the range references in the formulas in B33 and G33.
These ranges are you used to count how many date entries you have to that point, so that it knows if is should continue entering the next date or leave the cell blank.
B33:
=IF(COUNT(Setup!$F$18:$F$397)>=COUNT($B$32:$B32)+COUNT($G$32:G32)+1,SMALL(Setup!$F$18:$F$397,COUNT($B$32:$B32)+COUNT($G$32:G32)+1),"")
G33:
=IF(COUNT(Setup!$F$18:$F$80)>=COUNT($B$32:$B32)+COUNT($G$32:G32)+2,SMALL(Setup!$F$18:$F$80,COUNT($B$32:$B32)+COUNT($G$32:G32)+2),"")
The difference between these 2 is the +1 or +2 depending on which side... this is because you have 2 groups side by side.. so as you go down the left side, you are adding on to the total number of dates entered above.. and you are on the right side you are adding 2 to the total numbers of dates entered above..
In the main extraction formula, the part that is dependent on the row you are in is MOD(ROW()-9,12) in the new formula. It used to be MOD(ROW()-2,12)
the MOD() function returns the remainder when a number is divided by the divisor.... so the Mod() of the first row you are divided by the spacing to the next group (12 rows) is Mod(34,12) which is 10.. I want a 1 so I can use that as the Match for my original Index() formula... so I will offset by 9 rows.. i.e =MOD(Row()-9,12). If you enter that formula in any cell in row 34, you should get 1. If you copy that down, you will see that it sequentially adds 1 until it hits 12 and then restarts at 0, and the next 1, lands nicely at row 46, where you need it to be so that you can again concatenate that 1 to the date and find the match in the other sheet.
I hope that helps.
Hi NBVC,
I've spent the last few hours trying to work out the new expression, comparing with previous and your notes etc., and got some of it, but in all honesty, some I would never have understood. However, I wanted to try/learn rather than take the easy way and copy/paste your example, but as I say... I sadly failed
Good news... I eventually copied your example, changed a couple of ranges and whoaaa... it works perfectly.
There isn't enough I can say to thank you for all of your effort and help. I really do mean it... thank you very much.
Jo-Jo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks