Derived Data based on condition

[#1]

Hi,

Based on the below tables, I need to derive the third table called Required Data.
Based on Priority & Model Code from Priority table, need to compare the Data table contents & derive the resultant Required Data table.

Please refer the attached excel file for the reference.

Priority:

Priority	Model Code	Model Name
4	RZF50R	Astar
3	LF50R	Bstar
2	TGN40R	Cstar
1	KUN40R	Dstar
5	OTHERS	OTHERS

Data:

Applicable Model 1	Applicable Model 2	Applicable Model 3	Applicable Model 4	Applicable Model 5	Applicable Model 6
ACV30R	GRJ120	KUN40R	KUN51R	TGN40R
ACV30R	GRJ120	LF50R	NDE140	ZRE142
ACV30R	GRJ120	NDE140	NGK15R	NUK10R
ACV30R	GRJ120	NDE140	NUK10R	NUK15R
ACV30R	GRJ120	NDE140	ZRE142	ZVW30R
KUN40R	GRJ120	NDE140	ZRE142	ZZE142
ACV30R	GRJ120	NUK10R	NUK15R	ZVW30R
ACV30R	GRJ120	TGN40R
ACV30R	GRJ120	TGN40R	ZRE142	ZVW30R
ACV30R	GRJ120	ZVW30R
ACV30R	KUN40R
ACV30R	KUN40R
KUN40R	TGN40R

Required Data:

Model Name	Applicable Model 1	Applicable Model 2	Applicable Model 3	Applicable Model 4	Applicable Model 5	Applicable Model 6
Dstar	ACV30R	GRJ120	KUN40R	KUN51R	TGN40R
Bstar	ACV30R	GRJ120	LF50R	NDE140	ZRE142
OTHERS	ACV30R	GRJ120	NDE140	NGK15R	NUK10R
OTHERS	ACV30R	GRJ120	NDE140	NUK10R	NUK15R
OTHERS	ACV30R	GRJ120	NDE140	ZRE142	ZVW30R
Dstar	KUN40R	GRJ120	NDE140	ZRE142	ZZE142
OTHERS	ACV30R	GRJ120	NUK10R	NUK15R	ZVW30R
Cstar	ACV30R	GRJ120	TGN40R
Cstar	ACV30R	GRJ120	TGN40R	ZRE142	ZVW30R
OTHERS	ACV30R	GRJ120	ZVW30R
Dstar	ACV30R	KUN40R
Dstar	ACV30R	KUN40R
Dstar	KUN40R	TGN40R

[Gysbert_Wassenaar · Accepted solution]

See attached example

[#3]

Dear Gysbert,
Thanks for your quick response.
Solution is working fine but we need to consider the Priority number also from the Priority table while deriving the resultant data.

Based on the model code & Priority i.e. higher to lower, 1-5 we need to derive the data.

Thanks,

Raja

[#4]

Hi Raja,

Find attached file..

I have used same file which Gysbert developed..

Added Priority table separately on top of data table and did "Load  Order - Original" on Sort(Table Properties)

[#5]

Dear Karthik,

Can u share the file which u have worked on..

Thanks,

[#6]

Attached..

Thanks,

Karthik