Required a formula to return combined a list, common values in a both list, values not included in each other List.

[mmr1]

Hi,

Required a formula help for do a different types of workouts as based on data (Work items list-1 & 2) contains in Column A to C, for there required details as follows.

1. In a Column E, Cell number E4 to be return a Combined work items lists (merged list) of both lists into one list, Sort A to Z without Blanks, duplicates.
2. In a Column F, starting from Cell number F4 return a work item list-1 which are not in work item list-2,as same manner starting from Cell number F11 return a work item list-2 which are not includes in work item list-1, Sort A to Z without Blanks, duplicates.
3. In a Column G return a Common and repeated work items in both lists, Sort A to Z without Blanks, duplicates.

Thanks for the help,

Required a combined list and common values in the both list and values in not repeated in both lists.xlsx
	A	B	C	D	E	F	G
1
2
3	Work Item list-1		Work item list-2		Combined List without Blanks,duplicates	return a work item in list-1 not in list 2 sort A to Z without duplicates and blanks.	return a repeated work item in both lists sort A to Z without duplicates and blanks.
4	Diversion		Utilities
5	machinery		Exacvation
6			temporary works
7	Production
8	Project		Project
9	Civil		Civil
10			Construction			return a work item in list-2 not in list 1 sort A to Z without duplicates and blanks.
11	Construction
12			Construction
13			Civil
Sheet1

 

[Fluff]

Can't do your col E requirement, but for the rest, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1
2
3	Work Item list-1		Work item list-2		Combined List without Blanks,duplicates	return a work item in list-1 not in list 2 sort A to Z without duplicates and blanks.	return a repeated work item in both lists sort A to Z without duplicates and blanks.
4	Diversion		Utilities			Diversion	Civil
5	machinery		Exacvation			machinery	Construction
6			temporary works			Production	Project
7	Production
8	Project		Project
9	Civil		Civil
10			Construction			return a work item in list-2 not in list 1 sort A to Z without duplicates and blanks.
11	Construction					Exacvation
12			Construction			temporary works
13			Civil			Utilities
14
15
16
Main

Cell Formulas
Range	Formula
F4:F6	F4	=SORT(UNIQUE(FILTER(A4:A15,ISNA(XMATCH(A4:A15,C4:C15,0)))))
G4:G6	G4	=SORT(UNIQUE(FILTER(A4:A15,(ISNUMBER(XMATCH(A4:A15,C4:C15,0)))*(A4:A15<>""))))
F11:F13	F11	=SORT(UNIQUE(FILTER(C4:C15,ISNA(XMATCH(C4:C15,A4:A15,0)))))
Dynamic array formulas.

 

[mmr1]

Many thanks fluff for your solution/help, i really appreciate it.

 

[Fluff]

You're welcome & thanks for the feedback.