How to compare 2 columns in Excel and print a third column without replacement?

[chrismarince]

I have this spreadsheet...

A	B	C	D	E
8:45	26		85
9:49	14		30
10:34	85		14
11:12	30		26
3:05	90		30
2:33	14		90
12:45	26		26
1:00	90		14
2:45	30		90

<tbody>
</tbody>

I need to put a formula in E that says: compare Column D with Column B. For any cell in B that is equal to a cell in D, print the corresponding text from the cell in A in the cell in E. However, once a pair from B and D are found to match, they are no longer used for future comparisons.

I understand that sentence may not be entirely clear. I will show you what the end result should look like...

A	B	C	D	E
8:45	26		85	10:34
9:49	14		30	11:12
10:34	85		14	9:49
11:12	30		26	8:45
3:05	90		30	2:45
2:33	14		90	3:05
12:45	26		26	12:45
1:00	90		14	2:33
2:45	30		90	1:00

<tbody>
</tbody>

As you can see, the second "30" found in column D (D5) reads the time in Column A corresponding with the second "30" in column B (B9) as opposed to the first "30" in B (B4).

If this could be done with formulas as opposed to Marcos, that is preferable.

Thank you very much for your help!

 

[MikeDBMan]

Row 5, with 3:05 in column A. Why would the time not be 11:12 instead of 2:45? Never mind. I get it. That was already used.

 

[T. Valko]

I have this spreadsheet...

A	B	C	D	E
8:45	26		85
9:49	14		30
10:34	85		14
11:12	30		26
3:05	90		30
2:33	14		90
12:45	26		26
1:00	90		14
2:45	30		90

<TBODY>
</TBODY>

I need to put a formula in E that says: compare Column D with Column B. For any cell in B that is equal to a cell in D, print the corresponding text from the cell in A in the cell in E. However, once a pair from B and D are found to match, they are no longer used for future comparisons.

I understand that sentence may not be entirely clear. I will show you what the end result should look like...

A	B	C	D	E
8:45	26		85	10:34
9:49	14		30	11:12
10:34	85		14	9:49
11:12	30		26	8:45
3:05	90		30	2:45
2:33	14		90	3:05
12:45	26		26	12:45
1:00	90		14	2:33
2:45	30		90	1:00

<TBODY>
</TBODY>

As you can see, the second "30" found in column D (D5) reads the time in Column A corresponding with the second "30" in column B (B9) as opposed to the first "30" in B (B4).

If this could be done with formulas as opposed to Marcos, that is preferable.

Thank you very much for your help!

Try this array formula** entered in E2:

=INDEX(A:A,SMALL(IF(B$2:B$10=D2,ROW(B$2:B$10)),COUNTIF(D$2:D2,D2)))

** 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.

Format as h:mm.

 

[MikeDBMan]

It is a User-defined Function. Not exactly a macro but it does use VBA code. It seems to work fine!

Function Fixit(Z)  'where the calling UDF is "=Fixit(Row())"
Dim X, Y As Long
Dim Found As Integer
Dim PutIn As Long
Dim LookingFor As Variant
Dim Counter, Counter2 As Long
X = 1
Do While True
    If Cells(X, 1).Value = Empty Then Exit Do
    PutIn = PutIn + 1
    X = X + 1
Loop
'Let Z = ActiveCell.Row
If Z = 1 Then
    If Cells(Z, 2).Value = Cells(Z, 3).Value Then
        Fixit = Cells(1, 1).Value
        Exit Function
    End If
End If
Let LookingFor = Cells(Z, 3).Value
For X = 1 To Z - 1
    If Cells(X, 3).Value = LookingFor Then
        'first instance already used
        Let Counter = Counter + 1
    End If
Next
For X = 1 To PutIn
    If Cells(X, 2).Value = LookingFor Then
        Let Counter2 = Counter2 + 1
        If Counter2 > Counter Then
            Fixit = Cells(X, 1).Value
            Exit Function
        End If
    End If
Next
End Function

 

[chrismarince]

Thank you both very much! These work perfectly!

 

[T. Valko]

Thank you both very much! These work perfectly!

You're welcome. Thanks for the feedback!