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

C

chrismarince

New Member
Joined
Jul 16, 2012
Messages
9
I have this spreadsheet...

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]8:45[/TD]
[TD]26[/TD]
[TD][/TD]
[TD]85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:49[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:34[/TD]
[TD]85[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:12[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3:05[/TD]
[TD]90[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2:33[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:45[/TD]
[TD]26[/TD]
[TD][/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1:00[/TD]
[TD]90[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2:45[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]8:45[/TD]
[TD]26[/TD]
[TD][/TD]
[TD]85[/TD]
[TD]10:34[/TD]
[/TR]
[TR]
[TD]9:49[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]11:12[/TD]
[/TR]
[TR]
[TD]10:34[/TD]
[TD]85[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]9:49[/TD]
[/TR]
[TR]
[TD]11:12[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]26[/TD]
[TD]8:45[/TD]
[/TR]
[TR]
[TD]3:05[/TD]
[TD]90[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]2:45[/TD]
[/TR]
[TR]
[TD]2:33[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]90[/TD]
[TD]3:05[/TD]
[/TR]
[TR]
[TD]12:45[/TD]
[TD]26[/TD]
[TD][/TD]
[TD]26[/TD]
[TD]12:45[/TD]
[/TR]
[TR]
[TD]1:00[/TD]
[TD]90[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]2:33[/TD]
[/TR]
[TR]
[TD]2:45[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]90[/TD]
[TD]1:00[/TD]
[/TR]
</tbody>[/TABLE]


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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
chrismarince said:
I have this spreadsheet...

[TABLE="class: cms_table, width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]8:45
[/TD]
[TD]26
[/TD]
[TD][/TD]
[TD]85
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:49
[/TD]
[TD]14
[/TD]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:34
[/TD]
[TD]85
[/TD]
[TD][/TD]
[TD]14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:12
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD]26
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3:05
[/TD]
[TD]90
[/TD]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2:33
[/TD]
[TD]14
[/TD]
[TD][/TD]
[TD]90
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:45
[/TD]
[TD]26
[/TD]
[TD][/TD]
[TD]26
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1:00
[/TD]
[TD]90
[/TD]
[TD][/TD]
[TD]14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2:45
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD]90
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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

[TABLE="class: cms_table, width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]8:45
[/TD]
[TD]26
[/TD]
[TD][/TD]
[TD]85
[/TD]
[TD]10:34
[/TD]
[/TR]
[TR]
[TD]9:49
[/TD]
[TD]14
[/TD]
[TD][/TD]
[TD]30
[/TD]
[TD]11:12
[/TD]
[/TR]
[TR]
[TD]10:34
[/TD]
[TD]85
[/TD]
[TD][/TD]
[TD]14
[/TD]
[TD]9:49
[/TD]
[/TR]
[TR]
[TD]11:12
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD]26
[/TD]
[TD]8:45
[/TD]
[/TR]
[TR]
[TD]3:05
[/TD]
[TD]90
[/TD]
[TD][/TD]
[TD]30
[/TD]
[TD]2:45
[/TD]
[/TR]
[TR]
[TD]2:33
[/TD]
[TD]14
[/TD]
[TD][/TD]
[TD]90
[/TD]
[TD]3:05
[/TD]
[/TR]
[TR]
[TD]12:45
[/TD]
[TD]26
[/TD]
[TD][/TD]
[TD]26
[/TD]
[TD]12:45
[/TD]
[/TR]
[TR]
[TD]1:00
[/TD]
[TD]90
[/TD]
[TD][/TD]
[TD]14
[/TD]
[TD]2:33
[/TD]
[/TR]
[TR]
[TD]2:45
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD]90
[/TD]
[TD]1:00
[/TD]
[/TR]
</TBODY>[/TABLE]


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!
Click to expand...
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.
 
Upvote 0
It is a User-defined Function. Not exactly a macro but it does use VBA code. It seems to work fine!

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
If statement
Replies
4
Views
107
xlhelp15
xlhelp15
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
258
tezza
tezza
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
455
Sundance_Kid
S
S
  • Solved
Compare columns
Replies
7
Views
115
sharshra
S

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top