copy data based on part of item when matching more than 34 sheets and invert value for one item

[MKLAQ]

Hi experts ,
I need macro for about 4000 rows for each sheet and about more than 34 sheets when matching part of item across sheets
so the parts of items waht I want matching across sheets are
(CCS DEL,MASROUFA FGRTERE,SSMT TRYU,PTT REF)
then should copy data to FTT sheet start arrainging date fro old to last with sort items from A-Z and invert the value for just SSMT TRYU from column D to column C and delete any characters before or after parts of items as show the result in FFT sheet and should deal any new data or change in others sheets without repeat copying data have already copied.

MK.xlsm
	A	B	C	D
1	DATE	ID	BUYING	SELLING
2
3
4
5
6
7
8
9
FTT

MK.xlsm
	A	B	C	D
1	DATE	ID	BUYING	SELLING
2	07/20/2023	CCS DEL CCR1-CMB1 G80000	233
3	07/20/2023	CCCH Y8888 HJJJ 7000LM	123
4	02/21/2023	CCS DEL CVBBBB20000 NMUI 345000	124
5	02/21/2023	CVBBBB20000 NMUI 345001	123	2
6	02/21/2023	CVBBBB20000 NMUI 345002	44	3
7	02/21/2023	MASROUFA FGRTERE 80000 JKHHGTTT100		4
8	02/22/2023	HGJGG PTT REF FGRTERE 90000 4555	66
9	02/23/2023	SSMT TRYU CCR1-CMB8BB NMJ7000		700
10	02/24/2023	PTT REF MNDFHHHH 50000 JK900	88
ASH

MK.xlsm
	A	B	C	D
1	DATE	ID	BUYING	SELLING
2	07/20/2023	CCS DEL CCR1-CMB1 G180000	50
3	07/20/2023	CCCH Y8888 HJJJ 17000LM	123
4	07/20/2023	CVBBBB20000 123NMUI 345000	11
5	07/20/2023	CVBBBB20000 NMUIGH34 345001	22	2
6	02/21/2023	TRYCVBBBB20000 NMUI 345002	11	3
7	02/21/2023	SSMT TRYU NMJFRTERE 80000 JKHHGTTT100		4
8	02/21/2023	MTHYHGJGG FGRTERE 90000 4555	23
9	02/21/2023	ASDFFCCR1-CMB8BB NMJ7000	77
10	02/21/2023	MNDFHHHH 50000 JK900 PTTTR500	23
GHF2

result should be

MK.xlsm
	A	B	C	D
1	DATE	ID	BUYING	SELLING
2	07/20/2023	CCS DEL	233
3	07/20/2023	CCS DEL	50
4	02/21/2023	CCS DEL	124
5	02/21/2023	MASROUFA FGRTERE		4
6	02/21/2023	SSMT TRYU	4
7	02/22/2023	PTT REF	66
8	02/23/2023	SSMT TRYU	700
9	02/24/2023	PTT REF	88
FTT

 

[MKLAQ]

Did you put keyvalue in Column G Sheets FTT?

Ok this is my bad ,sorry

I tried run 38sheets (each sheet around 4k rows) , it takes roughly 5-10second (macos)

May you tell me how many rows for each sheet?

 

[RudRud]

Ok this is my bad ,sorry

May you tell me how many rows for each sheet?

Around 4k rows each sheet, I only can offer you with Dictionary and Instr for now

 

[MKLAQ]

Around 4k rows each sheet, I only can offer you with Dictionary and Instr for now

ok I appreciate your help and time.

 

[MKLAQ]

there is problem about in column G will also take another values from others sheets without show items in FTT sheet . it seems the
problem from empty cells in column G, will brings the all of data whether matched with others sheets or not existed in column G how I can fix it?

 

[RudRud]

there is problem about in column G will also take another values from others sheets without show items in FTT sheet . it seems the
problem from empty cells in column G, will brings the all of data whether matched with others sheets or not existed in column G how I can fix it?

Im guessing it might be loop through sheets problem? Are there any sheets that you dont want to execute?

It would be good if you send the exact error picture or data for debugging, Otherwise a little bit hard to get your thought.

 

[MKLAQ]

I'm not sure what happens, but I have to find out what's my bad before I answer you again.
thank you