Lookup multiple columns and return cell in different column when they become unique

[CB5]

Hello,
My goal is to find a function that looks at multiple columns and when the multiple columns are no longer unique return a the value of the last duplicated info from a different column. Sorry that's probably really confusing. Here's an example, I want the formula to look at column 1 and 2 and when they are no longer unique return the date associated to the last duplicate value to the first occurrence of the similar cells. So once Shark Tank is no longer matching return the date of 10/18 to the first row shark tank appeared in columns 1 and 2. Then do the same for octopus cage and shark aquarium.

1 Column 1 Column2 Column 3
2 Shark Tank 10/15/2017 10/18/2017
3 Shark Tank 10/16/2017
4 Shark Tank 10/17/2017
5 Shark Tank 10/18/2017
6 Octopus Cage 11/15/2017 11/21/2017
7 Octopus Cage 11/16/2017
8 Octopus Cage 11/17/2017
9 Octopus Cage 11/18/2017
10 Octopus Cage 11/19/2017
11 Octopus Cage 11/20/2017
12 Octopus Cage 11/21/2017
13 Shark aquarium 12/15/2017 12/25/2017
14 Shark aquarium 12/16/2017
15 Shark aquarium 12/17/2017
16 Shark aquarium 12/18/2017
17 Shark aquarium 12/19/2017
18 Shark aquarium 12/20/2017
19 Shark aquarium 12/21/2017
20 Shark aquarium 12/22/2017
21 Shark aquarium 12/23/2017
22 Shark aquarium 12/24/2017
23 Shark aquarium 12/25/2017

Thanks

[GC Excel]

Hi CB5,

Here's a suggestion in the file attached using 2 helper columns.
Note that the formula in column H (giving the result) is an Array Formula that you must validate using CTRL + SHIFT + ENTER.
Type the formula in H2, press CTRL + SHIFT + ENTER, then copy down.
(Adjust the range as necessary)

GC

[leelnich]

Hi all- Paste this in D2 and copy down:
Updated 22-08-2017 23:43

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[CB5]

Hey Leelnich and GC Excel, Thanks for the responses. I'm still having trouble getting this to work. Do either of you know a way to just compare one column and when the value of the next cell is unique return the cell data from the right to the first occurrence of the previous unique value. To put it into context I have a spreadsheet with thousands of events and I'm trying to find the last date each event occurs. Some events occur only one day others occur for numerous days in a row. My goal is to find a formula that returns the end date of each event and place it next to the first occurrence of that event.

Here is an example of what I'm hoping for.

WHAT DATA CURRENLTY SHOWS
ABC 09/30/17
DEF 11/03/17
GHI 09/29/17
GHI 09/29/17
JKL 10/14/17
MNO 02/07/18
PQR 01/21/18
STU 09/21/17
STU 09/22/17
STU 09/22/17
STU 09/23/17
STU 09/23/17
VWX 09/27/17
VWX 09/28/17
VWX 09/29/17
VWX 09/29/17
VWX 09/30/17
VWX 09/30/17

WHAT THE FORMULA WOULD RETURN
ABC 09/30/17
DEF 11/03/17
GHI 09/29/17
GHI
JKL 10/14/17
MNO 02/07/18
PQR 01/21/18
STU 09/23/17
STU
STU
STU
STU
VWX 09/30/17
VWX
VWX
VWX
VWX
VWX

[63falcondude]

This response is based off of post #4:

Assuming your data is in A2:B20, try this in C2

=IF(COUNTIF(A$2:A2,A2)=1,MAX(IF(A$2:A$20=A2,B$2:B$20)),"") Ctrl Shift Enter

It gives the result that you shared in post #4.

[CB5]

Thanks 63falcondude, For some reason I can't get this formula to work. It only returns the date of 02/07/18 for every value in column A. It does however return just one date in the correct place.

ABC 09/30/17 02/07/18
DEF 11/03/17 02/07/18
GHI 09/29/17 02/07/18
GHI 09/29/17
JKL 10/14/17 02/07/18
MNO 02/07/18 02/07/18
PQR 01/21/18 02/07/18
STU 09/21/17 02/07/18
STU 09/22/17
STU 09/22/17
STU 09/23/17
STU 09/23/17
VWX 09/27/17 02/07/18
VWX 09/28/17
VWX 09/29/17
VWX 09/29/17
VWX 09/30/17
VWX 09/30/17

[63falcondude]

You have to confirm the formula using Ctrl Shift Enter instead of just Enter.

This is an array formula.

[CB5]

I see, it works now. Thanks again

[63falcondude]

You're welcome. Happy to help.

If that solved your question, please mark this thread as SOLVED.