Find the address of the last Unique Cell in an array

Let's see if I understand this...

Data Range

	A	B	C	D
1	4/11/2016	1	2	1
2	4/12/2016	2	4	2
3	4/13/2016	3	6	3
4	4/14/2016	4	2	3
5	4/15/2016	4	3	3
6	4/16/2016	4	9	3
7	------	------	------	------

The date that corresponds to the "last" unique value in column B is 4/14/2016.

The date that corresponds to the "last" unique value in column C is 4/16/2016.

The date that corresponds to the "last" unique value in column D is 4/13/2016.

Is that correct?

Since you have the last values, use

=INDEX($A:$A, MATCH(XXXX,B:B,False))

where XXXX is a cell with the last value for B.

If you need to copy it down instead of to the right, use

=INDEX($A:$A, MATCH(XXXX,INDEX($B:$Z,ROW(A1)),False))

Originally Posted by Bernie Deitrick

=INDEX($A:$A, MATCH(XXXX,B:B,False))

where XXXX is a cell with the last value for B.

That won't necessarily find the correct location.

For example, in column C there are multiple non-consecutive instances of 2. If 2 was the last value then using INDEX/MATCH would find the first instance from top to bottom.

Try this...

Data Range

	A	B	C	D	E	F	G
1	4/11/2016	1	2	1		4	4/14/2016
2	4/12/2016	2	4	2		9	4/16/2016
3	4/13/2016	3	6	3		3	4/13/2016
4	4/14/2016	4	2	3
5	4/15/2016	4	3	3
6	4/16/2016	4	9	3
7	------	------	------	------	------	------	------

All formulas are array formulas**.

G1:

=OFFSET(A$1,ROWS(A$1:A$6)-LOOKUP(1E100,FREQUENCY(IF(B$1:B$6=F1,ROW(B$1:B$6)),IF(B$1:B$6<>F1,ROW(B$1:B$6)))),0)

G2:

=OFFSET(A$1,ROWS(A$1:A$6)-LOOKUP(1E100,FREQUENCY(IF(C$1:C$6=F2,ROW(C$1:C$6)),IF(C$1:C$6<>F2,ROW(C$1:C$6)))),0)

G3:

=OFFSET(A$1,ROWS(A$1:A$6)-LOOKUP(1E100,FREQUENCY(IF(D$1:D$6=F3,ROW(D$1:D$6)),IF(D$1:D$6<>F3,ROW(D$1:D$6)))),0)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

P.S.

Format the cells as Date (if needed).

Can you post a SMALL sample file so I can see what we're dealing with?

Try to make the sample as SMALL as possible. I hate having to scroll all around looking for stuff!

That is exactly what he wants - the first instance, of the last cell that has a unique value as the range is extended from B1, one cell down per step. After each step, ask "Is the new cell unique in the extended range?" The last time that question is true is the instance the OP wants, so 9 is the value of interest in the column C.

This is what I based my formulas on:

I need to find the corresponding date of the last unique value (from the bottom up).

The best that I can do is find the date corresponding to the last unduplicated value in the columns. I hope that this helps. So far, I haven't been able to find the value of the last series of duplicates if they exist.
To find the last unduplicated value in the columns. Enter in destination cell with Ctrl + Shift + Enter and fill across.

Formula:

=INDIRECT("A"&MAX(IF(COUNTIF(B1:B10,B1:B10)=1, ROW(B1:B10))))

This comes close but fails where the last values are duplicates. if the last value(s) in the column are not duplicated and are unique, this works. Again enter with Ctrl + Shift + Enter

Formula:

=IF(MAX(IF(COUNTIF(B1:B10,B1:B10)=1, ROW(B1:B10))) < COUNTA(A:A),OFFSET($A$1,MAX(IF(COUNTIF(B1:B10,B1:B10)=1, ROW(B1:B10))),0),OFFSET($A$1,MAX(IF(COUNTIF(B1:B10,B1:B10)=1, ROW(B1:B10)))-1,0))

In the sample file you have an incorrect result for Sample C. You have 4/13/2016 when it should be 4/15/2016.

This array formula** entered in M4:

=OFFSET(A$3,COUNTA(A$3:A$17)-LOOKUP(1E100,FREQUENCY(IF(INDEX(B$3:H$17,0,ROWS(M$4:M4))=L4,ROW(A$3:A$17)),IF(INDEX(B$3:H$17,0,ROWS(M$4:M4))<>L4,ROW(A$3:A$17)))),0)

Format as Date

Copy down as needed

You can use a User-Defined-Function in VBA, code below. Enter this formula in M3 and copy down.

=EarliestDate(A:A,INDEX($B:$H,,ROW(A1)))

Use this formula in L3, and copy down:

=INDEX(INDEX($B:$H,,ROW(A1)),COUNTA(INDEX($B:$H,,ROW(A1))))

Function EarliestDate(rDates As Range, rValues As Range) As Date
    Dim rVal As Range
    Dim Sh As Worksheet
    Dim lR As Long
    
    Set Sh = rValues.Parent
    Set rVal = Sh.Cells(Sh.Rows.Count, rValues.Column).End(xlUp)
    
    For lR = rVal.Row To rValues.Cells(1).Row Step -1
        If Sh.Cells(lR, rValues.Column).Value <> rVal.Value Then
            EarliestDate = rDates.Cells(lR + 1, 1).Value
            Exit Function
        End If
    Next lR
End Function

For what it is worth, I will add this in case it will help.
1. To get the values from columns B, C and D in the order that they appear in the lists. Enter this in G2 and fill across 3 columns total and down until the data runs out.

Formula:

=IFERROR(INDEX(B$1:B$6,MATCH(1,INDEX((COUNTIF(G$1:G1,B$1:B$6)=0)*(B$1:B$6<>""),0),0)),"")

Enter this array formula (entered with Ctrl + Shift + Enter) in G8 and fill across. (these are the cells that I used, you can use whatever you like by making minor changes to the cell references.

Formula:

=OFFSET(INDEX($A$1:$A$6,LARGE(IF(B$1:B$6=INDEX(G$2:G$6,MATCH(10^300,G$2:G$6)-1),ROW(B$1:B$6),""),1)),1,0)

What this does is find the second last value in the helper lists. Then the last location of that value is found and offset by 1 row to get the first row of the last value.

You're welcome. Thanks for the feedback!

My head hurts just thinking about this!

Let me see if I can come up with something. No guarantees!

Ok, this seems to work.

Here's the file...