VBA or formula to copy value from one column into another column where certain conditions are met.

[junkforhr]

I have a dataset (see example below)

Where Key_Text equals AAAInterventionType, where the Ref matches, find AAAReviewReason in the Key_Text column return the value of Key_Code in the column called ReviewReason.

DTS	Ref	KEY_Text	KEY_Code
11/04/2023 8:03​	9995a	AAAChannel	PHO
11/04/2023 8:03​	9995a	AAAInterventionStatus	STA
11/04/2023 8:03​	9995a	AAAInterventionType	IQI
11/04/2023 8:03​	9995a	AAAOrigin	INT
11/04/2023 8:03​	9995a	AAAReviewReason	CDI
11/04/2023 8:05​	9995a	AAAChannel	PHO
11/04/2023 8:05​	9995a	AAAInterventionStatus	STA
11/04/2023 8:05​	9995a	AAAInterventionType	IQI
11/04/2023 8:05​	9995a	AAAOrigin	INT
11/04/2023 8:05​	9995a	AAAReviewReason	CDI
11/04/2023 8:06​	9995a	AAAChannel	PHO
11/04/2023 8:06​	9995a	AAAInterventionStatus	STA
11/04/2023 8:06​	9995a	AAAInterventionType	IQI
11/04/2023 8:06​	9995a	AAAOrigin	INT
11/04/2023 8:06​	9995a	AAAReviewReason	CDI

Desired Outcome

DTS	Ref	KEY_Text	KEY_Code	ReviewReason
11/04/2023 8:03​	9995a	AAAChannel	PHO
11/04/2023 8:03​	9995a	AAAInterventionStatus	STA
11/04/2023 8:03​	9995a	AAAInterventionType	IQI	CDI
11/04/2023 8:03​	9995a	AAAOrigin	INT
11/04/2023 8:03​	9995a	AAAReviewReason	CDI
11/04/2023 8:05​	9995a	AAAChannel	PHO
11/04/2023 8:05​	9995a	AAAInterventionStatus	STA
11/04/2023 8:05​	9995a	AAAInterventionType	IQI	CDI
11/04/2023 8:05​	9995a	AAAOrigin	INT
11/04/2023 8:05​	9995a	AAAReviewReason	CDI
11/04/2023 8:06​	9995a	AAAChannel	PHO
11/04/2023 8:06​	9995a	AAAInterventionStatus	STA
11/04/2023 8:06​	9995a	AAAInterventionType	IQI	CDI
11/04/2023 8:06​	9995a	AAAOrigin	INT
11/04/2023 8:06​	9995a	AAAReviewReason	CDI

 

[Peter_SSs]

Like this?

23 07 05.xlsm
	B	C	D	E
1	Ref	KEY_Text	KEY_Code
2	9995a	AAAChannel	PHO
3	9995a	AAAInterventionStatus	STA
4	9995a	AAAInterventionType	IQI	CDI
5	9995a	AAAOrigin	INT
6	9995a	AAAReviewReason	CDI
7	9995a	AAAChannel	PHO
8	9995a	AAAInterventionStatus	STA
9	9995a	AAAInterventionType	IQI	CDI
10	9995a	AAAOrigin	INT
11	9995a	AAAReviewReason	CDI
12	9995a	AAAChannel	PHO
13	9995a	AAAInterventionStatus	STA
14	9995a	AAAInterventionType	IQI	CDI
15	9995a	AAAOrigin	INT
16	9995a	AAAReviewReason	CDI
Get Code

Cell Formulas
Range	Formula
E2:E16	E2	=IF(C2="AAAInterventionType",INDEX(D:D,AGGREGATE(15,6,ROW(D$2:D$16)/((C$2:C$16="AAAReviewReason")*(B$2:B$16=B2)),1)),"")

 

[junkforhr]

Perfect!
Thanks mate.

 

[Peter_SSs]

You're welcome.