Return list of names which DON'T appear in contact table

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Hello All

Can anyone help me please? I am using Excel 365. I have two tables (all fake data):

Table 1 (A-C): Contact details of individuals
Table 2 (G-J): Orders made by individuals

My actual data is likely to be 1000s of rows. I want to be able to perform some kind of lookup between table 1 and 2, which returns any names of individuals who haven made entries in Table 2, but their contact details don't appear in Table 1. I only want to see each name once. So based on the example below, I would want to see a list showing the names, David Smith, Naomi Chillcott and Lexi Tracy.

Book1
ABCDEFGHIJ
1NameEmailTelNameData 1Data 2Data 3
2Joe BloggsJoebloggs@whatever.com1234Joe BloggsHappy12yes
3Amy GeeAmy Gee@whatever.com15312Amy GeeHappy45yes
4Helen WaterHelen Water@whatever.com1523132Helen WaterExcited1No
5Sam FranksSam Franks@whatever.com1531Sam FranksMiserable564No
6Richard OsgoodRichard Osgood@whatever.com15345David SmithAngry43yes
7Tom PondTom Pond@whatever.com86451Richard OsgoodAnxious54No
8Charlie NewCharlie New@whatever.com15876Lexi TracyHappy74No
9Charlie NewMiserable23No
10Tom PondHappy75No
11Charlie NewAnxious1yes
12Joe BloggsHappy7No
13Amy GeeHappy4No
14Amy GeeHappy43yes
15Naomi ChillcottMiserable1No
16Helen WaterHappy74yes
17Sam FranksAnxious36No
18Richard OsgoodHappy4yes
19Tom PondHappy643yes
20Charlie NewMiserable24No
21Lexi TracyHappy1yes
Sheet1


Thanks in advance to anyone who can help.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,395
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1NameEmailTelNameData 1Data 2Data 3Name
2Joe BloggsJoebloggs@whatever.com1234Joe BloggsHappy12yesDavid Smith
3Amy GeeAmy Gee@whatever.com15312Amy GeeHappy45yesLexi Tracy
4Helen WaterHelen Water@whatever.com1523132Helen WaterExcited1NoNaomi Chillcott
5Sam FranksSam Franks@whatever.com1531Sam FranksMiserable564No
6Richard OsgoodRichard Osgood@whatever.com15345David SmithAngry43yes
7Tom PondTom Pond@whatever.com86451Richard OsgoodAnxious54No
8Charlie NewCharlie New@whatever.com15876Lexi TracyHappy74No
9Charlie NewMiserable23No
10Tom PondHappy75No
11Charlie NewAnxious1yes
12Joe BloggsHappy7No
13Amy GeeHappy4No
14Amy GeeHappy43yes
15Naomi ChillcottMiserable1No
16Helen WaterHappy74yes
17Sam FranksAnxious36No
18Richard OsgoodHappy4yes
19Tom PondHappy643yes
20Charlie NewMiserable24No
21Lexi TracyHappy1yes
22
Work
Cell Formulas
RangeFormula
M2:M4M2=UNIQUE(FILTER(G2:G21,ISNA(MATCH(G2:G21,A2:A8,0))))
Dynamic array formulas.
 

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
67
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1NameEmailTelNameData 1Data 2Data 3Name
2Joe BloggsJoebloggs@whatever.com1234Joe BloggsHappy12yesDavid Smith
3Amy GeeAmy Gee@whatever.com15312Amy GeeHappy45yesLexi Tracy
4Helen WaterHelen Water@whatever.com1523132Helen WaterExcited1NoNaomi Chillcott
5Sam FranksSam Franks@whatever.com1531Sam FranksMiserable564No
6Richard OsgoodRichard Osgood@whatever.com15345David SmithAngry43yes
7Tom PondTom Pond@whatever.com86451Richard OsgoodAnxious54No
8Charlie NewCharlie New@whatever.com15876Lexi TracyHappy74No
9Charlie NewMiserable23No
10Tom PondHappy75No
11Charlie NewAnxious1yes
12Joe BloggsHappy7No
13Amy GeeHappy4No
14Amy GeeHappy43yes
15Naomi ChillcottMiserable1No
16Helen WaterHappy74yes
17Sam FranksAnxious36No
18Richard OsgoodHappy4yes
19Tom PondHappy643yes
20Charlie NewMiserable24No
21Lexi TracyHappy1yes
22
Work
Cell Formulas
RangeFormula
M2:M4M2=UNIQUE(FILTER(G2:G21,ISNA(MATCH(G2:G21,A2:A8,0))))
Dynamic array formulas.
That is perfect! Thank you so much once again. I'm learning a lot from this.
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,578
Members
418,401
Latest member
B_A_M155

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