for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

run free Feb 2019 10:15pm, 7 Feb 2019 3,347 posts	Quote Pin run free 10th March
GlennR Feb 2019 11:47am, 8 Feb 2019 40,734 posts	Quote Pin GlennR Thanks.
larkim Feb 2019 9:12am, 18 Feb 2019 6,748 posts	Quote Pin larkim I was trying to summarise nicely our family's parkrun history by pulling together a summary sheet from separate sheets of our own parkrun performances. I had dates incrementing by 7 down column A, and then index-matching that to pull through the performances. All going well until I realised I was missing some, due to special day runs (Christmas, New Year). So I manually added those in, but then I was still missing runs - of course, NYD doubles. So the excel question is - if I have a list which I'm index matching but have two identical matches on, how do I return into appropriate cells the first match and the second match?
um Feb 2019 9:27am, 18 Feb 2019 790 posts	Quote Pin um Hi Larkim This site has an example of index to return 2nd, 3rd occurences : extendoffice.com But it looks like you have to know what you want to match first. Or you could modify the data (simplistically add -a, -b or _1, _2, or make it date and start time to the date values for multiples?)
larkim Feb 2019 10:06am, 18 Feb 2019 6,749 posts	Quote Pin larkim Cheers - it does seem there's no straightforward way to achieve it without some additional helper data being created. It's easy to work around manually, and given the subject matter it's hardly critical!!
run free Feb 2019 9:53pm, 18 Feb 2019 3,352 posts	Quote Pin run free Data Consolidate possibly or otherwise try Power Query (built into 2016; downloadable from M'soft website for 2010 / 2013)
Rosehip Feb 2019 6:02am, 20 Feb 2019 11,808 posts	Quote Pin Rosehip Trying to help with homework... Student would like to produce a Gantt chart, for a 12-month project and then include it as an image in another document. I haven't needed to do anything like this for about 10 years. Apparently, this can be done using excel (I could probably remember how to do it if I had access to Project ) -is this the case? I tried using google-sheets, but couldn't find a way of adjusting the scale such that months were shown rather than weeks. As ever, this has been left to "last minute"...
EdJ Feb 2019 7:00am, 20 Feb 2019 288 posts	Quote Pin EdJ Not a big fan of doing it in Excel, but... Column for each month (with the month entered into the top row of each column), list of tasks down the left hand column, and then fill the cells with a colour of your choice to show the task durations. Or were you thinking something more sophisticated?
GlennR Feb 2019 9:46am, 20 Feb 2019 41,007 posts	Quote Pin GlennR I've done versions of that where the cells auto-fill based on dates entered, a la Project. Happy to send code, Rosehip, if that's not using a sledgehammer to crack a nut.
GregP Feb 2019 9:52am, 20 Feb 2019 110,881 posts	Quote Pin GregP I've been wrestling with Visio and Project this week. They both serve to remind me why I'm happier with Excel.