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

#### mmr1

##### Board Regular
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
ABCDEFG
1
2
3Work Item list-1Work item list-2Combined List without Blanks,duplicatesreturn 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.
4DiversionUtilities
5machineryExacvation
6temporary works
7Production
8ProjectProject
9CivilCivil
10Constructionreturn a work item in list-2 not in list 1 sort A to Z without duplicates and blanks.
11Construction
12Construction
13Civil
Sheet1

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Fluff

##### MrExcel MVP, Moderator
Can't do your col E requirement, but for the rest, how about
+Fluff v2.xlsm
ABCDEFG
1
2
3Work Item list-1Work item list-2Combined List without Blanks,duplicatesreturn 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.
4DiversionUtilitiesDiversionCivil
5machineryExacvationmachineryConstruction
6temporary worksProductionProject
7Production
8ProjectProject
9CivilCivil
10Constructionreturn a work item in list-2 not in list 1 sort A to Z without duplicates and blanks.
11ConstructionExacvation
12Constructiontemporary works
13CivilUtilities
14
15
16
Main
Cell Formulas
RangeFormula
F4:F6F4=SORT(UNIQUE(FILTER(A4:A15,ISNA(XMATCH(A4:A15,C4:C15,0)))))
G4:G6G4=SORT(UNIQUE(FILTER(A4:A15,(ISNUMBER(XMATCH(A4:A15,C4:C15,0)))*(A4:A15<>""))))
F11:F13F11=SORT(UNIQUE(FILTER(C4:C15,ISNA(XMATCH(C4:C15,A4:A15,0)))))
Dynamic array formulas.

#### mmr1

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

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
2
Views
155
Replies
1
Views
231
Replies
26
Views
428
Replies
9
Views
181
Replies
9
Views
180

1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back