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

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 25, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Many thanks fluff for your solution/help, i really appreciate it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top