2D Line Chart for 'Target vs Achievement' in date wise progress, filter on Employe

[Deepakchoudhary]

Hello Friends,
Need some ideas to arrange below data in appropriate table and create a Pivot and this Pivot to use to create a 2D line chart (Target vs Achive), where I want to filter on employee.

Date - >	01-03-2024​	01-03-2024​	02-03-2024​	02-03-2024​	03-03-2024​	03-03-2024​	04-03-2024​	04-03-2024​	05-03-2024​	05-03-2024​
Employee List	Target	Achieve	Target	Achieve	Target	Achieve	Target	Achieve	Target	Achieve
AB	60	54	127	110	270	230	572	557	1212	1180
BC	66	51	141	126	298	267	632	622	1339	1300
CD	54	56	117	120	247	253	524	508	1111	1050
DE	70	67	152	144	322	317	683	664	1447	1437
EF	44	39	92	87	196	180	415	393	880	861
FG	30	34	65	74	137	158	291	281	617	605
GH	82	77	179	161	379	353	803	792	1703	1655
Group Total	406	378	872	822	1849	1758	3920	3817	8311	8088

 

[Cubist]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		01-03-2024	01-03-2024	02-03-2024	02-03-2024	03-03-2024	03-03-2024	04-03-2024	04-03-2024	05-03-2024	05-03-2024		Group	Date	Target	Achieve		Group	(All)
2	Employee List	Target	Achieve	Target	Achieve	Target	Achieve	Target	Achieve	Target	Achieve		AB	01-03-2024	60	54
3	AB	60	54	127	110	270	230	572	557	1212	1180		AB	01-03-2024	60	54		Row Labels	Group's Target	Group's Achieve
4	BC	66	51	141	126	298	267	632	622	1339	1300		AB	02-03-2024	127	110		01-03-2024	$812	$756
5	CD	54	56	117	120	247	253	524	508	1111	1050		AB	02-03-2024	127	110		02-03-2024	$1,746	$1,644
6	DE	70	67	152	144	322	317	683	664	1447	1437		AB	03-03-2024	270	230		03-03-2024	$3,698	$3,516
7	EF	44	39	92	87	196	180	415	393	880	861		AB	03-03-2024	270	230		04-03-2024	$7,840	$7,634
8	FG	30	34	65	74	137	158	291	281	617	605		AB	04-03-2024	572	557		05-03-2024	$16,618	$16,176
9	GH	82	77	179	161	379	353	803	792	1703	1655		AB	04-03-2024	572	557		Grand Total	$30,714	$29,726
Sheet5

Cell Formulas
Range	Formula
M2:N71	M2	=TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A3:A9 & "|" & B1:K1,3)),"|",",")
O2:P9	O2	=INDEX($B$3:$K$9,MATCH($M2,$A$3:$A$9,0),MATCH($N2&O$1,$B$1:$K$1&$B$2:$K$2,0))
Dynamic array formulas.

Then select the data from columns M-P to make a pivot chart.

.

 

[Deepakchoudhary]

Dear Cubist,
I'm very thankful for your reply. Functions giving #NAME? error. I am using Excel 2016 and as I googled.. this may be a problem behind this error...

 

[Joe4]

I am using Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cubist]

Dear Cubist,
I'm very thankful for your reply. Functions giving #NAME? error. I am using Excel 2016 and as I googled.. this may be a problem behind this error...

The solution above is only available in 365. Unfortunately, I don't know how to do it in the 2016 version with formulas. You probably need a VBA.

 

[Cubist]

Assuming your data starts in A1.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1		1/3/24	1/3/24	2/3/24	2/3/24	3/3/24	3/3/24	4/3/24	4/3/24	5/3/24	5/3/24
2	Employee List	Target	Achieve	Target	Achieve	Target	Achieve	Target	Achieve	Target	Achieve
3	AB	60	54	127	110	270	230	572	557	1212	1180
4	BC	66	51	141	126	298	267	632	622	1339	1300
5	CD	54	56	117	120	247	253	524	508	1111	1050
6	DE	70	67	152	144	322	317	683	664	1447	1437
7	EF	44	39	92	87	196	180	415	393	880	861
8	FG	30	34	65	74	137	158	291	281	617	605
9	GH	82	77	179	161	379	353	803	792	1703	1655
Sheet3

Try this VBA code on a copy. The code should unpivot your data and output to column M. You can use this data to make a pivot chart.

Sub UnpivotData()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim a, b, c, v
    Dim lastRow, lastCol, numRow As Long
    Dim ws As Worksheet
    Dim i As Long
   
    Set ws = ThisWorkbook.Worksheets("Sheet3") '<Change sheet name as needed
    Set rng1 = ws.Range("A3:A9") '<- Change to Employee list range
    Set rng2 = ws.Range("B1:K1") '<-Change to Date row range
    Set rng3 = ws.Range("B3:K9") '<- Change to value range
   
    a = rng1.Value
    b = rng2.Value
    v = rng3.Value
   
    numRow = rng1.Count * rng2.Count / 2
    ReDim c(1 To numRow, 1 To 4)
    k = 1
   
    For i = 1 To numRow
        j = i Mod 7
        If j = 0 Then j = 7
        If j = 1 And i > 1 Then k = k + 2

        c(i, 1) = a(j, 1)
        c(i, 2) = b(1, k)
        c(i, 3) = v(j, k)
        c(i, 4) = v(j, k + 1)

    Next i

    ws.Range("M2").Resize(UBound(c, 1), 4).Value = c
End Sub

 

[Cubist]

The mod 7 was hard coded in the above. Should've the # of rows in the a array.

Sub UnpivotData()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim a, b, c, v
    Dim lastRow, lastCol, numRow As Long
    Dim ws As Worksheet
    Dim i As Long
  
    Set ws = ThisWorkbook.Worksheets("Sheet3") '<Change sheet name as needed
    Set rng1 = ws.Range("A3:A9") '<- Change to Employee list range
    Set rng2 = ws.Range("B1:K1") '<-Change to Date row range
    Set rng3 = ws.Range("B3:K9") '<- Change to value range
  
    a = rng1.Value
    b = rng2.Value
    v = rng3.Value
  
    numRow = rng1.Count * rng2.Count / 2
    ReDim c(1 To numRow, 1 To 4)
    k = 1
  
    For i = 1 To numRow
        j = i Mod a.rows.count
        If j = 0 Then j = a.rows.count
        If j = 1 And i > 1 Then k = k + 2

        c(i, 1) = a(j, 1)
        c(i, 2) = b(1, k)
        c(i, 3) = v(j, k)
        c(i, 4) = v(j, k + 1)

    Next i

    ws.Range("M2").Resize(UBound(c, 1), 4).Value = c
End Sub