I recently had a situation where I have part measured repeatedly after rework. The number of times the each parts measured and which measurement belongs to which rework is only can be identified by "time stamp" (the time of the measurement). Please refer to the sample data. Please note the data is sort by time stamp and part name.
Now I need to a Stacked Column Chart like below. This is to have visual representation of the effect of rework. My original intention to use Qliksense or Tableau but excel can do the job with an additional table.
Step 1: First sort the table by part and then by time stamp.
Step 2: Add a column (column D) with the formula
="Measurement No" & COUNTIF(B$2:B2,B2)
Step 3: Drag the formula all the way.
Step 4: Now you can create the table for the Stacked Column Chart.
The table should be like the image below.
Add formula =SUMIFS($C:$C,$B:$B,J$1,$D:$D,$I2) in cell J2.
Fill the table with the formula.
Use insert → chart → stacked column to create the chart.