Posts by NCFC

    Re: Convert data in table to data in list

    Worked it out but didn't twig at first the double clicking on the count of values pivot and was wondering where on earth Table 2 came from!

    So easy when you know how - many thanks

    I am trying to transpose 3,000 rows of data that has a unique reference number and 10 components attributed to each reference number i.e. for every 1 row of data in the data table there will be 10 new rows with each row showing the unique reference number against one of the individual component values

    I have attached a simplified version of the data set along with the proposed solution.

    Any help gratefully appreciated.

    Re: Combined Line & Scatter Graph

    I tried the revised version of the chart based on % completion allocated to each stage above and it doesn't work and, therefore, revisited your version and think it may work although until I can see everything I'm still not certain. I have a number of questions

    How do you add in all six stages (you are currently showing Stage 1 and Stage 2) as this would be a key to this exercise; I could have 20 - 30 of these stages.

    Also, at present workflows are numbered 1-5. If I change these to 'names', the Stage markers default to the primary X axis. How can I add in milestone names without this happening.

    Just for my information, how did you add in the second date axis to mirror the productivity dates and maintain the scale proportion?

    Thanks again

    Re: Combined Line & Scatter Graph

    Thanks for getting back to me

    Each Stage would be equal to 16.66% (100%/6 stages). It's not what I was looking to do but it may be a solution. If the solution cannot be achieved as I had anticipated then may have to apply percentages to the completion date of each to align with the productivity percentages (productivity on the primary axis, stage completion % on the secondary axis)?

    Therefore, Stage 1 = 16.66, Stage 2 = 33.33%, Stage 3 = 50.00%, Stage 4 = 66.66%, Stage 5 = 83.33%, Stage 6 = 100%

    Re: Combined Line & Scatter Graph

    Thanks for your attempt but it doesn't work in that visual format.

    The closest I got was in the chart in the second attachment but the scale just don't work; I'm trying to show downtime i.e. less than 100% against certain stages i.e. waiting on others, etc.

    The only way I can see of doing it is to identify %'s complete for each stage i.e. six stages at 16.66% complete and show it in this manner.

    Any ideas?

    Re: Combined Line & Scatter Graph

    Hi again,

    Since my original question, I have produced a chart which is roughly what I am looking for but there are a couple of things I still cannot work out and am seeking a little bit of help (again).

    I need the dates of the stages to be on the same axis as the timeline in the productivity graph (otherwise the scale is not in proportion). Any help on how to do this would be brilliant

    Secondly, the format of the data series are fine for each of the five workflows (triangles, circles, etc) but how do I change the individual Stages to the same colour (Stages 1-6 in the example) i.e. stage 1 is always black (with its respective shape), stage 2 is always red (with its respective sheet), etc. Again, any help would be gratefully appreciated.

    I did not get any replies on my first question (perhaps it was so easy) but am struggling here

    Cannot for the life of me work this graph out.

    The first part is to plot a simple line chart i.e. % efficiency over time (row 2). Then combine the line chart with the 5 work stages (1A to 5A in cells A3:A21) and the dates achieved for each stage.

    I want to identify the dates achieved for each stage in the timeline as a coloured symbol or something similar (there are six types shown in cells A23:A28). It would be good to identify the work stage against each but as this is only a small sample (there could be over 100 work stages each with the six dates achieved) it may get too busy dependent upon how the data can be arranged and the scale of the graph

    Thanks for any help in advance



    Trying to compare a specific value (a metre value) between two points (held in two cells [start and finish]) to see if the start and/ or finish value lies between another interval (held in a further two cells) - 10,000 item list

    Example 1
    'Start' value 60 and 'Finish' value 650 (i.e between 60 and 650) lie in the range 'Start' value 600 to 'finish' 1000 (i.e between 600 and 1000) - TRUE

    Example 2
    'Start' value 60 and 'Finish' value 650 (i.e between 60 and 650) lie in the range 'Start' value 45 to 'finish' 55 (i.e between 45 and 55) - FALSE

    Thanks in anticipation