Macro Alternate for Vlookup?? Expert Needed

[Barklie]

Hi All,

---

I'm looking for any method to automatically cross reference two tables in order to populate matches from the 2nd table. While normally Vlookup accomplishes this task with ease, it cannot in this case for three reasons:

---

1) There are duplicates in table 1 that I don't want in table 3

---

2) There are duplicates in table 2 that I DO want in table 3

---

3) Autofilling creates blank spaces in table 3, which I do not want

---

Here is 2 example tables and the results I would like in table 3 (formulas for G2:I5):

---

[TABLE="width: 652"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Table 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Matches (desired)
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Frank
[/TD]
[TD][/TD]
[TD]Robert
[/TD]
[TD]May
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]Frank
[/TD]
[TD]June
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Frank
[/TD]
[TD][/TD]
[TD]Sam
[/TD]
[TD]April
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD]Brett
[/TD]
[TD]July
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Brett
[/TD]
[TD][/TD]
[TD]Brett
[/TD]
[TD]July
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]Brett
[/TD]
[TD]September
[/TD]
[TD="align: right"]9
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Edney
[/TD]
[TD][/TD]
[TD]Brett
[/TD]
[TD]September
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD]Chad
[/TD]
[TD]February
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Dave
[/TD]
[TD][/TD]
[TD]Frank
[/TD]
[TD]June
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Chad
[/TD]
[TD][/TD]
[TD]Ted
[/TD]
[TD]May
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Paul
[/TD]
[TD]August
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Chad
[/TD]
[TD]February
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ned
[/TD]
[TD]December
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have already spent over 6 hours (pathetic- I know) trying to figure this out using array formulas and VBA loops to no avail. Any advice would be greatly, greatly appreciated. Thanks!

 

[Rick Rothstein]

This will produce the correct list, but not in the name order you show it (is that okay?)...

Sub ProcessTable1andTable2()
  Dim X As Long
  Range("C2:E" & Cells(Rows.Count, "C").End(xlUp).Row).Copy Range("G2")
  For X = 2 To Cells(Rows.Count, "G").End(xlUp).Row
    If Range("A2", Cells(Rows.Count, "A").End(xlUp)).Find(Cells(X, "G")) Is Nothing Then Cells(X, "G").Resize(, 3).Clear
  Next
  Range("G2", Cells(Rows.Count, "G").End(xlUp)).Resize(, 3).SpecialCells(xlBlanks).Delete xlShiftUp
End Sub

 

[Andrew Poulsom]

Formula in G2 copied down and across:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Table 1		Table 2				Matches (desired)
2	Frank		Robert	May	6		Brett	July	4
3	Frank		Sam	April	7		Brett	September	9
4	Brett		Brett	July	4		Frank	June	5
5	Edney		Brett	September	9		Chad	February	11
6	Dave		Frank	June	5
7	Chad		Ted	May	3
8			Paul	August	8
9			Chad	February	11
10			Ned	December	6
Sheet1

Cell Formulas
Range	Formula
G2	{=IFERROR(INDEX(C$2:C$10,SMALL(IF(COUNTIF($A$2:$A$7,$C$2:$C$10),ROW(C$2:C$10)-ROW(C$2)+1),ROWS(G$2:G2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Barklie]

Wow, both those work perfectly (the order of them doesn't matter, actually I prefer y'alls order). I'm not sure which one I'll end up using, but I appreciate both of your help very much. This will save me tons of time (after I finish investigating each of your methods). Thanks a ton!!