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
80
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
Many thanks fluff for your solution/help, i really appreciate it.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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
Back
Top