Formatting SAP Report Macro

[KMK_79]

Hi all

I regularly download SAP reports of varying filters but pretty much standardised list of columns. Many of these columns require conversion to number values, column width adjustments, groupings, alignments etc before I am comfortable to put them into a standard report for sharing with others or to have some analysis carried out on the data.

I am rather fastidious about working with data that is not formatted in a way that I want and that is just a personal bugbear. I am willing to share a small anonymised data samples (raw and after my manual formats) for help in providing me with relevant code that can embedded as a macro in excel itself.

I do not know how to embed a macro for use by ALL files opened by excel however I would only execute these for raw SAP downloaded data reports. Could I understand if this is something possible and can be done?

I look forward to your helpful responses. Always impressed with the support people get on this site!

Thanks, KMK

 

[KMK_79]

I recognise it is hard to send out a general message on my issue.

In the interests of getting a favourable response from people, I am posting the raw SAP data and the formatted data sheet which is what I am aiming for. There are a number of other columns but the formatting is typically similar and can be applied easily if I have the original coding.

Really appreciate some support here.

Thanks
KMK

 

[KMK_79]

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	EMP. FIRST NAME	MI	SSN/ID#	GENDER	NATIONALITY	BIRTH DATE	AGE	HIRE DATE	ADJUSTED SERVICE DATE	YEARS OF SERVICE	MONTHS OF SERVICE	POS. DATE	POS. #
2	Zhiyan			Female	Chinese	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50085337
3	Charles			Male	Australian	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50092989
4	Gareth			Male	Australian	31-08-82	35	01-02-11	01-02-11	6	7	01-12-15	50083863
5	Rosaline			Female	Irish	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50084813
6	Kristy			Female	Australian	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50083297
7	Simon			Male	British	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50083876
8	Vivien			Female	Singaporean	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50083193
9	Paul			Male	British	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50084903
10	Daniel			Male	Chinese	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50083203
11	Terry			Male	Australian	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50084893
12	Suhail	K		Male	Indian	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50083634
13	Wharton	J		Male	British	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50085190
14	Koen	P		Male	Belgian	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50083241
15	Barry	J		Male	British	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50083671
16	Ann			Female	Belgian	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50083209
17	Evanna			Female	Spanish	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50083240
18	Wael			Male	Moroccan	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50084317
19	Timothy			Male	Belgian	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50084315
20	Michelle			Female	Belgian	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50083206
21	Caroline	J		Female	New Zealand	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50084014
SAP Raw

 

[KMK_79]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	SAP ID	EMP. LAST NAME	EMP. FIRST NAME	MI	SSN/ID#	GENDER	NATIONALITY	BIRTH DATE	AGE	HIRE DATE	ADJUSTED SERVICE DATE	YEARS OF SERVICE	MONTHS OF SERVICE	POS. DATE	POS. #
2	791	Zhu	Zhiyan			Female	Chinese	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50085337
3	798	Morten	Charles			Male	Australian	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50092989
4	814	Smith	Gareth			Male	Australian	31-08-82	35	01-02-11	01-02-11	6	7	01-12-15	50083863
5	828	Kavanagh	Rosaline			Female	Irish	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50084813
6	1013	Hobson	Kristy			Female	Australian	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50083297
7	1016	Shiers	Simon			Male	British	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50083876
8	1019	Gilligan	Vivien			Female	Singaporean	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50083193
9	1027	Champion	Paul			Male	British	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50084903
10	1035	Zhu	Daniel			Male	Chinese	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50083203
11	1037	Hatton	Terry			Male	Australian	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50084893
12	3060	Akhtar	Suhail	K		Male	Indian	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50083634
13	20806	Pippa	Wharton	J		Male	British	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50085190
14	20812	Heusdens	Koen	P		Male	Belgian	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50083241
15	20814	Dunbridge	Barry	J		Male	British	09-04-76	41	01-07-03	01-07-03	14	2	13-06-16	50083671
16	20835	Lombaert	Ann			Female	Belgian	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50083209
17	20933	Martinez	Evanna			Female	Spanish	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50083240
18	21106	Al Banna	Wael			Male	Moroccan	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50084317
19	21107	Suckling	Timothy			Male	Belgian	23-09-65	51	01-01-04	01-01-04	13	8	01-10-15	50084315
20	21108	De Smedt	Michelle			Female	Belgian	19-10-76	40	01-07-02	01-07-02	15	2	01-10-15	50083206
21	21109	Tuite	Caroline	J		Female	New Zealand	25-01-78	39	01-02-11	01-02-11	6	7	01-12-15	50084014
22
23
24
25	Convert to Number, Align to Center	Middle Align, Left Align Text, Single Indent Increase	Middle Align, Left Align Text, Single Indent Increase	Middle Align, Center Align	No Format	Middle Align, Left Align Text, Single Indent Increase	Middle Align, Left Align Text, Single Indent Increase	Middle Align, Center Align	Middle Align, Center Align	Middle Align, Center Align	Middle Align, Center Align	Middle Align, Center Align	Middle Align, Center Align	Middle Align, Center Align	Convert to Number, Align to Center
SAP Formatted