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
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
Re: Convert data in table to data in list
Quote from Herbds7;695444Excel 2010 Table, PivotTable
De-Normalize Array into PivotTable friendly Table.
http://www.mediafire.com/view/4p0si0k24cq20ka/12_17_13.xlsx
Herbds7
Many thanks for the solution but haven't got a clue how this is working. Any methodology I can follow so that I can expand to a much wider set of data?
TC
Hi
Can somebody show me how to convert a table of data showing hours by date to a list so that I can pivot, etc. I just don't seem to be able to work this out.
Example shows data in its native format in the first part of the spreadsheet and how I would like it to look in the second part.
Many thanks in anticipation.
TC
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
TC
Re: Compare value in a range A-B to see if it lies within range Y-Z
Hi, there is an error in the formula where the range starts before and finishes after the second range (see attached for rows 6 and 11). How does the formula need to be amended to catch all?
Re: Compare value in a range A-B to see if it lies within range Y-Z
Many thanks
I messed up on the formula and I think this works perfectly now
Hi
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