0

I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B)

Sheet1

Sheet2

In other words, I want to check if EITHER dates in Sheet2 exists in Sheet1 for an ID in Column B and obtain the row in Sheet1 with the more recent date of the two selected dates in sheet2. Here is an example of the output I want:

Example Output

So far, I am getting duplicates of IDs: one with each of the two dates.

(It has to be a formula since this will be dynamic where a different date would be selected in sheet 2)

Here is my formula currently:

=IFERROR(INDEX(Sheet1!$A$2:$C$11, LARGE(IF(((Sheet1!$C$2:$C$11=Sheet2!$C$2)+(Sheet1!$C$2:$C$11=Sheet2!$C$3)>0),
MATCH(ROW(Sheet1!$B$2:$B$11), ROW(Sheet1!$B$2:$B$11)), “”), ROW(Sheet1!C1)), COLUMN(Sheet1!A1)),
“”)

Here is the output I am getting. Notice the duplicated IDs in red. I only want the rows with more recent dates in green.

I’m not opposed to helper columns but The data in sheet1 is also subject to change and I don’t want my output to have any gaps.

I’m very new to this so any suggestions are appreciated! Please! I have been at this for days now.

Here is the image of the excel sheets:

Improve this question
5
  • I think you have made the post little complicated ,,, edit the post & add some screen shots all about the expected results. Also following link. to improve the post.
    – Rajesh Sinha
    Commented Oct 5, 2023 at 4:47
  • Which version of Excel do you use? Can you use functions like filter, let and unique?
    – Máté Juhász
    Commented Oct 5, 2023 at 5:17
  • Sorry I’m new to this, not sure why the screenshots are showing up as links. But I edited and added more photos. Yes I tried filter and unique but I can only get a unique id not the date with it. I tried to do a v lookup but I can’t get the correct date column with the id
    – Allycat123
    Commented Oct 5, 2023 at 5:27
  • So when I did unique(filter(sheet1$b$2:$b$11,((sheet1$c$2:$c$11=sheet2!$c$2)+(sheet1$c$2:$c$11=sheet2!$c$3))) it’s still giving me duplicates. And idk how to incorporate MAX since it would just pick the 6/4/22 date
    – Allycat123
    Commented Oct 5, 2023 at 5:52
  • What happens if only one of the date exists? What should the output be?
    – Reddy Lutonadio
    Commented Oct 5, 2023 at 6:56

2 Answers 2

Reset to default
1

My proposal:

=LET(s, SORT(FILTER(Sheet1!$A$2:$C$11, (Sheet1!$C$2:$C$11=Sheet2!$C$2)+(Sheet1!$C$2:$C$11=Sheet2!$C$3)),3,-1), 
w, CHOOSECOLS(s,2), SORT(UNIQUE(CHOOSEROWS(s, MATCH(w,w,0))),2))
Improve this answer
0

If you have latest version of Excel, you can use this formula:
=LET(d_1,MIN(F2:F3),d_2,MAX(F2:F3),ID_2,FILTER(B2:B11,C2:C11=d_2),ID_1,FILTER(B2:B11,(C2:C11=d_1)*(IFERROR(MATCH(B2:B11,ID_2,0)=0,TRUE))),t_1,FILTER(A2:C11,(IFERROR(MATCH(B2:B11,ID_1,0),FALSE))*(C2:C11=d_1)),t_2,FILTER(A2:C11,C2:C11=d_2),VSTACK(t_1,t_2))

=LET(

  • d_1,MIN(F2:F3), - earlier date
  • d_2,MAX(F2:F3), - later date
  • ID_2,FILTER(B2:B11,C2:C11=d_2), - IDs appearing with later date
  • ID_1,FILTER(B2:B11,(C2:C11=d_1)*(IFERROR(MATCH(B2:B11,ID_2,0)=0,TRUE))), - IDs appearing with earlier date only
  • t_1,FILTER(A2:C11,(IFERROR(MATCH(B2:B11,ID_1,0),FALSE))*(C2:C11=d_1)), - records for ID_1
  • t_2,FILTER(A2:C11,C2:C11=d_2), - records for ID_2
  • VSTACK(t_1,t_2) - merged results

)

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .