Searching through multiple columns for matches then return first date from another column if true

AmyH_NHSBI

New Member
Joined
Sep 23, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Hi all
I have had this problem before but found a work around in SQL, unfortunately it won't work for this particular piece of work.

I need a formula that will look through Col E to see if there is a duplicate, if so, I want it to then look at Col G to see if the dates are the same, if the dates are the same I would then want it to look at Col H and return the first chronological date. If the conditions are not true, so no dupes in Col E to start with, it would just return the date in Col H.

I hope this makes sense? I have pasted a sample of data with sensitive info removed.

Thank you!


ABCDEFGH
1​
ACondition 1C111A-C111HT
29/01/2020​
13/02/2020​
2​
BCondition 2C222B-C222AM
24/01/2019​
04/02/2019​
3​
CCondition 3C333C-C333C
05/12/2019​
30/12/2019​
4​
DCondition 4C444D-C444S
24/09/2019​
04/11/2019​
5​
ECondition 5C555E-C555C
25/03/2020​
19/06/2020​
6​
FCondition 6C666F-C666C
28/01/2020​
11/02/2020​
7​
GCondition 7C777G-C777S
12/09/2019​
01/10/2019​
7​
GCondition 7C777G-C777S
12/09/2019​
15/10/2019​
7​
GCondition 7C777G-C777HT
12/09/2019​
30/10/2019​
8​
HCondition 8C888H-C888HT
25/10/2019​
25/10/2019​
8​
HCondition 8C888H-C888C
25/10/2019​
26/11/2019​
9​
ICondition 9C999I-C999C
31/10/2019​
03/12/2019​
10​
JCondition 10C100J-C100S
11/07/2019​
11/07/2019​
11​
KCondition 11C110K-C110S
08/02/2019​
18/03/2019​
12​
LCondition 12C120L-C120C
11/02/2019​
26/03/2019​
12​
LCondition 12C120L-C120AM
11/02/2019​
22/01/2020​
12​
LCondition 12C120L-C120C
11/02/2019​
03/06/2020​
13​
MCondition 13C130M-C130C
07/01/2020​
05/02/2020​
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
ABCDEFGHI
1ABCDEFGH
21ACondition 1C111A-C111HT29/01/202013/02/202013/02/2020
32BCondition 2C222B-C222AM24/01/201902/04/201902/04/2019
43CCondition 3C333C-C333C12/05/201930/12/201930/12/2019
54DCondition 4C444D-C444S24/09/201911/04/201911/04/2019
65ECondition 5C555E-C555C25/03/202019/06/202019/06/2020
76FCondition 6C666F-C666C28/01/202002/11/202002/11/2020
87GCondition 7C777G-C777S09/12/201910/01/201910/01/2019
97GCondition 7C777G-C777S09/12/201915/10/201910/01/2019
107GCondition 7C777G-C777HT09/12/201930/10/201910/01/2019
118HCondition 8C888H-C888HT25/10/201925/10/201925/10/2019
128HCondition 8C888H-C888C25/10/201926/11/201925/10/2019
139ICondition 9C999I-C999C31/10/201912/03/201912/03/2019
1410JCondition 10C100J-C100S07/11/201907/11/201907/11/2019
1511KCondition 11C110K-C110S02/08/201918/03/201918/03/2019
1612LCondition 12C120L-C120C02/11/201926/03/201926/03/2019
1712LCondition 12C120L-C120AM02/11/201922/01/202026/03/2019
1812LCondition 12C120L-C120C02/11/201906/03/202026/03/2019
1913MCondition 13C130M-C130C01/07/202002/05/202002/05/2020
Master
Cell Formulas
RangeFormula
I2:I19I2=AGGREGATE(15,6,$H$2:$H$19/($E$2:$E$19=E2)/($G$2:$G$19=G2),1)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,006
Messages
5,575,527
Members
412,672
Latest member
Tupelo1984
Top