Hi
I have a problem that I'm not able to overcome. I have the data as shown below (dataset much larger, but follows the logic below).
- Cells A1:C1 are merged, so are D1:F1 and so on
- I want to show a graph with Months on the X-axis, and Market Share on the Y-axis, and then different lines for the different Companies (Comp A through Comp E)
I have tried to pull out the data with INDEX-MATCH to a separate sheet, where Comp A's market shares are shown on one row, and Comp B's market share in next row and so on, but I cannot get it working to find the corresponding month with the merged cells.
Any tips on how to do this?
Thanks!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jan[/TD]
[TD]uary[/TD]
[TD]2018[/TD]
[TD]Feb[/TD]
[TD]ruary[/TD]
[TD]2018[/TD]
[TD]Ma[/TD]
[TD]rch[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Company Name[/TD]
[TD]Value[/TD]
[TD]Market Share[/TD]
[TD]Company Name[/TD]
[TD]Value[/TD]
[TD]Market Share[/TD]
[TD]Company Name[/TD]
[TD]Value[/TD]
[TD]Market Share[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Comp A[/TD]
[TD]100[/TD]
[TD]0,29[/TD]
[TD]Comp C[/TD]
[TD]140[/TD]
[TD]0,3[/TD]
[TD]Comp E[/TD]
[TD]75[/TD]
[TD]0,16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Comp B[/TD]
[TD]80[/TD]
[TD]0,23[/TD]
[TD]Comp A[/TD]
[TD]105[/TD]
[TD]0,23[/TD]
[TD]Comp B[/TD]
[TD]65[/TD]
[TD]0,14[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Comp C[/TD]
[TD]75[/TD]
[TD]0,22[/TD]
[TD]Comp D[/TD]
[TD]95[/TD]
[TD]0,21[/TD]
[TD]Comp A[/TD]
[TD]60[/TD]
[TD]0,13[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Comp D[/TD]
[TD]60[/TD]
[TD]0,17[/TD]
[TD]Comp B[/TD]
[TD]80[/TD]
[TD]0,17[/TD]
[TD]Comp D[/TD]
[TD]40[/TD]
[TD]0,09[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Comp E[/TD]
[TD]30[/TD]
[TD]0,09[/TD]
[TD]Comp E[/TD]
[TD]40[/TD]
[TD]0,09[/TD]
[TD]Comp C[/TD]
[TD]10[/TD]
[TD]0,02[/TD]
[/TR]
</tbody>[/TABLE]
I have a problem that I'm not able to overcome. I have the data as shown below (dataset much larger, but follows the logic below).
- Cells A1:C1 are merged, so are D1:F1 and so on
- I want to show a graph with Months on the X-axis, and Market Share on the Y-axis, and then different lines for the different Companies (Comp A through Comp E)
I have tried to pull out the data with INDEX-MATCH to a separate sheet, where Comp A's market shares are shown on one row, and Comp B's market share in next row and so on, but I cannot get it working to find the corresponding month with the merged cells.
Any tips on how to do this?
Thanks!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jan[/TD]
[TD]uary[/TD]
[TD]2018[/TD]
[TD]Feb[/TD]
[TD]ruary[/TD]
[TD]2018[/TD]
[TD]Ma[/TD]
[TD]rch[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Company Name[/TD]
[TD]Value[/TD]
[TD]Market Share[/TD]
[TD]Company Name[/TD]
[TD]Value[/TD]
[TD]Market Share[/TD]
[TD]Company Name[/TD]
[TD]Value[/TD]
[TD]Market Share[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Comp A[/TD]
[TD]100[/TD]
[TD]0,29[/TD]
[TD]Comp C[/TD]
[TD]140[/TD]
[TD]0,3[/TD]
[TD]Comp E[/TD]
[TD]75[/TD]
[TD]0,16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Comp B[/TD]
[TD]80[/TD]
[TD]0,23[/TD]
[TD]Comp A[/TD]
[TD]105[/TD]
[TD]0,23[/TD]
[TD]Comp B[/TD]
[TD]65[/TD]
[TD]0,14[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Comp C[/TD]
[TD]75[/TD]
[TD]0,22[/TD]
[TD]Comp D[/TD]
[TD]95[/TD]
[TD]0,21[/TD]
[TD]Comp A[/TD]
[TD]60[/TD]
[TD]0,13[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Comp D[/TD]
[TD]60[/TD]
[TD]0,17[/TD]
[TD]Comp B[/TD]
[TD]80[/TD]
[TD]0,17[/TD]
[TD]Comp D[/TD]
[TD]40[/TD]
[TD]0,09[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Comp E[/TD]
[TD]30[/TD]
[TD]0,09[/TD]
[TD]Comp E[/TD]
[TD]40[/TD]
[TD]0,09[/TD]
[TD]Comp C[/TD]
[TD]10[/TD]
[TD]0,02[/TD]
[/TR]
</tbody>[/TABLE]