VBA code to transpose data from vertical to horizontal based on certain conditions.

[dsubash]

Hi,
I am looking for a VBA code to transpose data from vertical to horizontal based on certain conditions.

My Excel spreadsheet has two sheets.

Sheet 1(Data), where in details of tests run for each patient is given in separate rows (one row for one test) – For a single Patient, multiple rows are present based on the number of tests conducted for the particular patient. My data runs to 15000 or more rows. This data have been downloaded from our software.

Sample Test Details.xlsx
	A	B	C	D	E	F	G	H	I								Q
1	No.	Sample No.	Sample ID	Test	Result	Register Date	Name	Age	Gender								Test Date
2	1	106233900	0106233900	UREA	29	01/01/2025 08:57	NithishKumar	0Years	Male								01/01/2025 09:13
3	2	106233900	0106233900	SGOT	19.6	01/01/2025 08:57	NithishKumar	0Years	Male								01/01/2025 09:16
22	21	106234100	0106234100	GLUC	116	01/01/2025 08:57	Maharaja	0Years	Male								01/01/2025 09:19
23	22	106234100	0106234100	CAL A	10.4	01/01/2025 08:57	Maharaja	0Years	Male								01/01/2025 09:20
27	26	106234000	0106234000	TGL	192	01/01/2025 08:58	VetrivelS	0Years	Male								01/01/2025 09:19
28	27	106234000	0106234000	GLUC	402	01/01/2025 08:58	VetrivelS	0Years	Male								01/01/2025 09:18
29	28	106234200	0106234200	GLUC	97	01/01/2025 09:59	AnandKumarV	0Years	Male								01/01/2025 10:13
30	29	500573600	0500573600	GLUC	150	01/01/2025 10:19	John	0Years	Male								01/01/2025 10:34
31	30	500573500	0500573500	GLUC	94	01/01/2025 10:19	Pooranima	0Years	Female								01/01/2025 10:33
32	31	500573501	0106234400	GLUC	101	01/01/2025 10:29	logapriya		Male								01/01/2025 10:49
33	32	500573502	0106234400	UA	3.6	01/01/2025 10:36	logapiya		Male								01/01/2025 10:50
58	57	106235600	0106235600	SGOT	33	01/01/2025 12:41	AbdulAlphaf	0Years	Male								01/01/2025 12:55
59	58	106235600	0106235600	SGPT	58	01/01/2025 12:41	AbdulAlphaf	0Years	Male								01/01/2025 12:55
60	59	106235600	0106235600	ALP	79	01/01/2025 12:41	AbdulAlphaf	0Years	Male								01/01/2025 12:56
61	60	500573506	20250101500573506	PHOS	6.27	01/01/2025 13:39	sivakamasundari		Male								01/01/2025 13:53
62	61	106237500	0106237500	BILL-D	3.38	01/01/2025 14:22	Sara	0Years	Female								01/01/2025 14:37
Data

Sheet2 (Report) – I need the same data in a horizontal format, i.e, I need all the tests run for a single patient in a single row. I need only the names of the tests run for each patient and the same to be based on the header. Column A will be serial number, Columns B to G will be names of the tests conducted, eg. If a patient had taking only Urea, GGT, Gluc, etc, then the name of the tests conducted for the particular patient to be mentioned under the header. I have realigned some headers for the ease of my reporting. Sample No. or Sample ID is unique to a particular patient and the same can be used for filtering.

Sample Test Details.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	No.	Sample No.	Sample ID	Register Date	Name	Gender	Test Date	UREA	SGOT	GGT	TP	CHOL	BILL-D	ALP	BILL-T	CAL A	TGL	SGPT	CREATIN	ALB	UA	GLUC	PHOS	ACE	IRON	AMYLASE	MICRO	CKNAC	MAG	LDH	CKMB	ZINC-CO
2	1	106233900	0106233900	01/01/2025 08:57	NithishKumar	Male	01/01/2025 09:13	UREA	SGOT	GGT	TP	CHOL	BILL-D	ALP	BILL-T	CAL A	TGL	SGPT	CREATIN	ALB	UA	GLUC
3	2	106234100	0106234100	01/01/2025 08:57	Maharaja	Male	01/01/2025 09:21	UREA				CHOL				CAL A	TGL		CREATIN		UA	GLUC	PHOS
4		106234000	0106234000	01/01/2025 08:58	VetrivelS	Male	01/01/2025 09:19					CHOL					TGL		CREATIN			GLUC
5		106234200	0106234200	01/01/2025 09:59	AnandKumarV	Male	01/01/2025 10:13															GLUC
6		500573500	0500573500	01/01/2025 10:19	Pooranima	Female	01/01/2025 10:33															GLUC
7		106240200	0106240200	01/01/2025 16:39	Sumithra	Female	01/01/2025 16:54		SGOT		TP		BILL-D	ALP	BILL-T	CAL A		SGPT		ALB				ACE
8		106243500	0106243500	02/01/2025 09:23	BhuvaneshwariB	Female	02/01/2025 09:50	UREA
9		500573900	0500573900	02/01/2025 11:53	Suryaraj	Male	02/01/2025 12:12	UREA	SGOT	GGT	TP	ALP	BILL-D		BILL-T			SGPT	CREATIN	ALB		GLUC
10		106274103	0106278000	03/01/2025 15:39	JOHN	Male	03/01/2025 15:51																			AMYLASE
11		106308700	0106308700	05/01/2025 09:20	Savitha	Female	05/01/2025 09:21	UREA	SGOT	GGT	TP		BILL-D	ALP	BILL-T	CAL A		SGPT	CREATIN	ALB	UA						MICRO
12		106355402	0106357100	07/01/2025 14:23	murali	Male	07/01/2025 14:36																					CKNAC
13		106360902	0520103200	07/01/2025 16:05	kumutha	Male	07/01/2025 16:18																						MAG
14		106360906	20250107106360906	07/01/2025 16:14	karthik	Male	07/01/2025 16:27												CREATIN			GLUC			IRON					LDH
15		106434700	0106434700	10/01/2025 16:35	Suntharesan	Male	10/01/2025 16:48																								CKMB
16		106475808	0106478200	13/01/2025 16:04	THIYA	Male	13/01/2025 16:15																									ZINC-CO
Report

I am looking for a VBA Code that would ease my work. I am using Excel 2019 Professional

Thanks in Advance
Subash D

 

[ranman256]

if you have Microsoft Access, this result can be made with a simple Crosstab query, no code.
link (or import) the data, run the query.

 

[dsubash]

if you have Microsoft Access, this result can be made with a simple Crosstab query, no code.
link (or import) the data, run the query.

View attachment 121354

Thanks Ranman,
i am not used to MS Access, moreover, i need the data in the above format so that I need to do some validation from workbook
.