Excel Magic Trick 1111: Item In Both Lists? Extract With Better Array Formula

preview_player
Показать описание


See how to extract items in List 2 that are also in List 1 using a great array formula from Exceli Adam at YouTube that uses the functions IF, INDEX, MATCH and AGGREGATE.

MATCH contains a function argument array operation that creates a resultant array of items that AGGREGATE can handle without having to use Ctrl + Shift + Enter.
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:05:11

Excel Magic Trick 1111: Item In Both Lists? Extract With Better Array Formula

Excel Magic Trick 0:05:12

Excel Magic Trick 1108: Compare 2 Lists and Extract Records: Filter Method

Excel Magic Trick 0:04:31

Excel Magic Trick 1113: Extract All Characters In Cell To Separate Cells: PPPP to P, P, P, P

Excel Magic Trick 0:01:13

Excel Magic Trick 1115: PivotTable to Count How Many of Each Item There Are In a Column

Excel Magic Trick 0:06:58

Excel Magic Trick 1116: Lookup Every Third Item or Lookup Every 'Nth' Item, 3 Formula Meth...

Excel Magic Trick 0:05:17

Excel Magic Trick 1109: Compare 2 Lists and Extract Records: Advanced Filter Method

Excel Magic Trick 0:11:57

Excel Magic Trick 1122: Repeat Row Headers Vertically For Each Day Activity Exists: Array Formula

Excel Magic Trick 0:12:59

Excel Magic Trick 1110: Compare 2 Lists and Extract Records: Array Formula Method

Excel Magic Trick 0:04:44

Excel Magic Trick 1123: Dynamic Formula To Create A List of Dates For Month

Excel Magic Trick 0:07:51

Excel Magic Trick 1112: Clean Transactional Data, Then Create PivotTable Monthly Cost Report

Excel Magic Trick 0:01:51

Excel Magic Trick 1049 Copy Page Setup From One Sheet To Another (nowtelugu.com)

Convert Date into 0:00:25

Convert Date into a Month TEXT Formula

Excel Auto Serial 0:00:54

Excel Auto Serial Number Trick in just 10 sec😲🔥 #viral #computer #excel #exceltips

xavier memes #memes 0:00:06

xavier memes #memes

Crazy tick removal? 0:00:11

Crazy tick removal? Or fake?

Excel Magic Trick 0:13:46

Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month

Excel Magic Trick 0:06:30

Excel Magic Trick 1114: Formula For Sequential & Repeating Numbers: 18400 1, 18441 1, 18442 2...

Excel Magic Trick 0:04:26

Excel Magic Trick 1038 Make Table Reference Absolute With Colon and Double Square Brackets

gas pe khade 0:00:16

gas pe khade ho gaye ladki | vj pawan singh | shorts

Thomas train | 0:00:12

Thomas train | Special effects | 3d animation | future technology imagination

Excel Tutorial | 0:00:59

Excel Tutorial | excel magic tricks | Excel for beginner to expert|| Learn Excel #shorts #Excel

Excel Magic Trick 0:08:51

Excel Magic Trick 1103: Formulas for Quarters, Fiscal Quarters & Fiscal Years

Excel Magic Trick 0:04:35

Excel Magic Trick 1106: 3-D Gradient Conditional Formatting For Row with AND Criteria

Calculate Hour When 0:01:00

Calculate Hour When Date & Time Combined in cell | Excel Tips & Tricks

Комментарии
Автор

Excel Magic Trick 1111: Item In Both Lists? Extract With Better Array Formula 
Great Trick from Exceli Adam at YouTube!!

excelisfun
Автор

I will need to complete this excercise a number of times, I would also like to be able compare sets to each other, ie, in Set A, B&C there are 200 duplicates, in Set A&C there are 50 and so on.

MichaelByrneWX
Автор

I would like to see this done with dynamic arrays. I can create a dynamic drop down menu on my data, create a dropdown menu that is based off the option chosen in that first dropdown and create array lists based off those selection and would like to be able to compare those 2 arrays (which can both change depending on the selections) and return those in the second that are not in the first without having to fill a column of formulas to do it.

dankoft
Автор

Ingenuity
But 2007 what is the solution
I wish writing functions on comment the video

Reduce_Scan
Автор

Is there a formula for this with three lists?. I have three databases with a combined 18k customers and have extracted the duplicates as follows:
1 - Created 'unique' codes by joining first 3 letters of name with DOB as follows "MIC24/04/2014" in column A and a database code in column B to represent the data source
2 - Copied the three lists into
3 - Used conditional formatting to highlight dupliciates and filtered list by color
4 - Copied and pasted values to new list
5 - Used advanced filters to create a unique list

MichaelByrneWX
Автор

how about this as another aggregate match for who is not in the list:
=IF(ROWS(K$6:K7)>$E$6, "", INDEX($C$6:$C$16, AGGREGATE(15, 6, (--ISNA(MATCH($C$6:$C$16, $A$6:$A$14, 0)))*MATCH($C$6:$C$16, $C$6:$C$16, 0), ROW())))    you could use ROWS($G$6:G6)-1+$E$6 just so happens that its the 6th row so row() evaluates to 6

Love these videos

excelfan