Creating unique Remittances for each user

[DamirC]

Hi There,

wondering if anyone can assist. I have a data sheet with information from all different users.
I would like to create a unique remittance for each user on a separate sheet and print it to PDF.
Some user have only one line while others have several.

If at all possible it would be good to have a total for Business hours / after hours on the remittance sheet.
thanks

Sample Data.xlsx
	A	B	C	D	E	F	G	H	I
1	Interpreter Name	Interpreter ID		Service Date	Connect Time	Client Name	AfterHours	LanguageName	Call Duration
2
3	INT1	1		24 Mar, 2022	16:05:35	Client X	false	Somali	8.00
4	INT2	2		30 Mar, 2022	13:51:11	Client Y	false	Arabic	7.00
5	INT2	2		23 Mar, 2022	11:30:26	Client Z	false	Arabic	8.00
6	INT3	3		01 Apr, 2022	12:11:46	Client X	false	Arabic	22.00
7	INT3	3		01 Apr, 2022	14:14:41	Client Y	false	Arabic	11.00
8	INT3	3		01 Apr, 2022	16:55:52	Client Z	false	Arabic	5.00
9	INT3	3		31 Mar, 2022	09:53:53	Client X	false	Arabic	5.00
10	INT3	3		31 Mar, 2022	12:40:16	Client Y	false	Arabic	3.00
11	INT3	3		31 Mar, 2022	14:40:37	Client Z	false	Arabic	5.00
12	INT3	3		31 Mar, 2022	14:59:46	Client X	false	Arabic	13.00
13	INT3	3		31 Mar, 2022	15:53:18	Client Y	false	Arabic	12.00
14	INT3	3		31 Mar, 2022	16:18:52	Client Z	false	Arabic	7.00
15	INT3	3		30 Mar, 2022	08:34:02	Client X	false	Arabic	5.00
16	INT3	3		30 Mar, 2022	08:39:08	Client Y	false	Arabic	3.00
17	INT3	3		30 Mar, 2022	09:23:03	Client Z	false	Arabic	5.00
18	INT3	3		30 Mar, 2022	10:29:58	Client X	false	Arabic	13.00
19	INT3	3		30 Mar, 2022	10:55:36	Client Y	false	Arabic	3.00
20	INT3	3		30 Mar, 2022	11:28:54	Client Z	false	Arabic	19.00
21	INT3	3		30 Mar, 2022	12:37:15	Client X	false	Arabic	8.00
22	INT3	3		30 Mar, 2022	13:20:12	Client Y	false	Arabic	32.00
23	INT3	3		30 Mar, 2022	14:15:30	Client Z	false	Arabic	17.00
24	INT3	3		30 Mar, 2022	16:54:49	Client X	false	Arabic	9.00
25	INT3	3		29 Mar, 2022	08:48:50	Client Y	false	Arabic	10.00
26	INT4	4		29 Mar, 2022	10:11:40	Client Z	false	Arabic	9.00
27	INT4	4		29 Mar, 2022	11:44:33	Client X	false	Arabic	13.00
28	INT4	4		29 Mar, 2022	16:35:03	Client Y	false	Arabic	5.00
29	INT4	4		28 Mar, 2022	09:03:40	Client Z	false	Arabic	2.00
30	INT4	4		28 Mar, 2022	12:34:50	Client X	false	Arabic	5.00
31	INT4	4		28 Mar, 2022	12:45:13	Client Y	false	Arabic	10.00
32	INT4	4		28 Mar, 2022	13:13:23	Client Z	false	Arabic	9.00
33	INT4	4		28 Mar, 2022	13:33:17	Client X	false	Arabic	2.00
34	INT4	4		25 Mar, 2022	09:29:57	Client Y	false	Arabic	11.00
35	INT4	4		25 Mar, 2022	10:25:23	Client Z	false	Arabic	14.00
36	INT4	4		25 Mar, 2022	12:06:32	Client X	false	Arabic	9.00
37	INT4	4		25 Mar, 2022	13:48:25	Client Y	false	Arabic	4.00
38	INT4	4		24 Mar, 2022	13:39:10	Client Z	false	Arabic	12.00
39	INT4	4		24 Mar, 2022	13:55:34	Client X	false	Arabic	5.00
40	INT4	4		24 Mar, 2022	15:32:04	Client Y	false	Arabic	16.00
41	INT4	4		24 Mar, 2022	16:12:35	Client Z	false	Arabic	8.00
42	INT4	4		23 Mar, 2022	09:26:52	Client X	false	Arabic	3.00
43	INT4	4		23 Mar, 2022	10:14:18	Client Y	false	Arabic	12.00
44	INT4	4		23 Mar, 2022	10:28:09	Client Z	false	Arabic	2.00
45	INT4	4		23 Mar, 2022	11:09:42	Client X	false	Arabic	7.00
46	INT4	4		23 Mar, 2022	11:21:25	Client Y	false	Arabic	17.00
Data

 

[BSALV]

like a classic pivottable but 1 page per client ?

Map1
	A	B	C	D	E	F	G
2
3	Som van Call Duration		LanguageName	AfterHours
4			Arabic	Totaal Arabic	Somali	Totaal Somali	Eindtotaal
5	Client Name	Interpreter Name	false		false
6	Client X	INT1			8	8	8
7		INT3	75	75			75
8		INT4	44	44			44
9	Totaal Client X		119	119	8	8	127
10
11	Client Y	INT2	7	7			7
12		INT3	74	74			74
13		INT4	75	75			75
14	Totaal Client Y		156	156			156
15
16	Client Z	INT2	8	8			8
17		INT3	58	58			58
18		INT4	56	56			56
19	Totaal Client Z		122	122			122
20
21	Eindtotaal		397	397	8	8	405
22
Blad3

 

[bebo021999]

How does the output look like?

 

[DamirC]

Hi Bebo021999,

something along the lines of:

Sample Data.xlsx
	A	B	C	D	E	F	G	H
1
2
3	INT NAME insert					Date 1	Company details
4	INT ID insert					Date 2	address
5	Reference INTID+text
6	INT Language insert
7
8
9
10
11		Service Date	Connect Time	Client Name	AfterHours	LanguageName	Call Duration
12
13		insert	insert	insert	insert	insert	insert
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29		Total Business hours					Total
30		Total After Hours					Total
31
32
Remittance

 

[DamirC]

like a classic pivottable but 1 page per client ?

Map1
	A	B	C	D	E	F	G
2
3	Som van Call Duration		LanguageName	AfterHours
4			Arabic	Totaal Arabic	Somali	Totaal Somali	Eindtotaal
5	Client Name	Interpreter Name	false		false
6	Client X	INT1			8	8	8
7		INT3	75	75			75
8		INT4	44	44			44
9	Totaal Client X		119	119	8	8	127
10
11	Client Y	INT2	7	7			7
12		INT3	74	74			74
13		INT4	75	75			75
14	Totaal Client Y		156	156			156
15
16	Client Z	INT2	8	8			8
17		INT3	58	58			58
18		INT4	56	56			56
19	Totaal Client Z		122	122			122
20
21	Eindtotaal		397	397	8	8	405
22
Blad3

Hi Bslav,

it should be other way around
INT can have any number of companies under their name, within business hours or outside.

 

[BSALV]

i don't know how far you want to go to automate and if you want to/can automate
This is just a starter, with the slicers you can filter the interpreters, clients, languages, dates, after hours (you can use the shift and ctrl keys too to select several options)

Without automation, you'll have to do everything manual in the right order
DamirC
The layout of a pivottable is easy to adapt, as you'll see later.
In the download of your XL2BB-data the translation of the connecttime is corrupted, so don't pay any attention to that ...

A pivottable, you have to refresh that manually or with VBA !!!
(rightclick somewhere in the pivottable and clcik refresh)