how can i extract data from cells?

[smilyben]

I have an excel file; all heading are repeated and some of data are repeated in cells. I want to transform this file to one heading and a line off data.
Since the original document contains almost 25000 lines, i want to do it with formula . I attached mini sheet and and the outcome i wanted.
Can you help me please.

HC sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	Dosya No	Adı Soyadı	Name	Name	Date	Age	Sex	phone
3	1724	MC	MC	MC	15.02.1964	54	K	543	No 1	No 1	P	S	O	S0	S	T	T	ST	IT	IT	UO	UO
4	1724	MC	MC	MC	15.02.1964	54	K	543	1	1	219	117	52	KD	KD	AH	AH	10.60 Positive	18.09.2019 14:32	18.09.2019 14:32	BF	BF
5									1	1	219	117	52	KD	KD	AH	AH	10.60 Positive	18.09.2019 14:32	18.09.2019 14:32	BF	BF
6	26838	FC	FC	FC	1.01.1987	36	K	536	No 1	No 1	P	S	O	S0	S	T	T	ST	IT	IT	UO	UO
7	26838	FC	FC	FC	1.01.1987	36	K	536	9	9	211	116	521	EH	EH	AH	AH	13.03 Positive	20.09.2019 09:47	20.09.2019 09:47	FE	FE
8									9	9	211	116	521	EH	EH	AH	AH	13.03 Positive	20.09.2019 09:47	20.09.2019 09:47	FE	FE
9	8826	AH	AH	AH	1.01.1936	84	E	536	No 1	No 1	P	S	O	S0	S	T	T	ST	IT	IT	UO	UO
10	8826	AH	AH	AH	1.01.1936	84	E	536	3	3	21	11	52	EH	EH	AH	AH	13.65 Positive	20.09.2019 11:02	20.09.2019 11:02	BF	BF
11									3	3	21	11	52	EH	EH	AH	AH	13.65 Positive	20.09.2019 11:02	20.09.2019 11:02	BF	BF
12	414	GK	GK	GK	25.06.1974	51	K	537	No 1	No 1	P	S	O	S0	S	T	T	ST	IT	IT	UO	UO
13	414	GK	GK	GK	25.06.1974	51	K	537	20	20	2199	118	521	EH	EH	AH	AH	13.88 Positive	20.09.2019 11:26	20.09.2017 11:26:39	FE	FE
14									20	20	2199	118	521	EH	EH	AH	AH	13.88 Positive	20.09.2019 11:26	20.09.2019 11:26	FE	FE
15	129	MA	MA	MA	20.11.1983	38	E	534	No 1	No 1	P	S	O	S0	S	T	T	ST	IT	IT	UO	UO
16	129	MA	MA	MA	20.11.1983	38	E	534	3	3	2180	1166	522	EH	EH	AH	AH	13.29 Positive	20.09.2016 14:44	20.09.2016 14:44	FE	FE
17									3	3	2180	1166	522	EH	EH	AH	AH	13.29 Positive	20.09.2016 14:44	20.09.2016 14:44	FE	FE
Sayfa1

HC sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Name	Name	Date	Age	Sex	phone	No 1	No 1	P	S	O	S0	S	T	T	ST	IT	IT	UO	UO
2	MC	MC	15.02.1964	54	K	543	1	1	219	117	52	KD	KD	AH	AH	10.60 Positive	18.09.2019 14:32	18.09.2019 14:32	BF	BF
3	FC	FC	1.01.1987	36	K	536	9	9	211	116	521	EH	EH	AH	AH	13.03 Positive	20.09.2019 09:47	20.09.2019 09:47	FE	FE
4	AH	AH	1.01.1936	84	E	536	3	3	21	11	52	EH	EH	AH	AH	13.65 Positive	20.09.2019 11:02	20.09.2019 11:02	BF	BF
5	GK	GK	25.06.1974	51	K	537	20	20	2199	118	521	EH	EH	AH	AH	13.88 Positive	20.09.2019 11:26	20.09.2019 11:26	FE	FE
6	MA	MA	20.11.1983	38	E	534	3	3	2180	1166	522	EH	EH	AH	AH	13.29 Positive	20.09.2016 14:44	20.09.2016 14:44	FE	FE
Sayfa2

 

[traveler4]

Assuming that this is a one-time task (rather than a recurring one) and assuming that the data is sorted for column Q (or you don't care about the sorting), I would do it as follows:
- paste A2:H2 to A3:H3
- delete rows 1 and 2
- sort for column Q => all yellow rows will be in one block
- from this yellow block delete all rows but the one which has the full headings (including the contents of the inital A2:H2)
- sort for column H => all white rows will be in one block
- delete the block with the white rows
- sort for column Q
- move the yellow row (heading) to the beginning of your table

 

[smilyben]

thank you very much...