Match,Difference and Copy VB Issue

[tchan05]

Hello,

I am new to this forum and very new to VB. I would like to write a VB Code to do the following: I have Sheet1 that has four columns with headers. Column A and B data goes together and column C and D data goes together. What I would like to do is 1) have Column C find Match in Column B and display difference on to sheet2 but need Column A to display the names as wee. Next I would like to do reverse and have Column B match column C and display difference on to sheet 2 but need corresponding column D names to match the results as well. The code I have so far only display the badge numbers on column E and F ( I realized that the results did not go on to sheet 2, just testing to on the same sheet at this point). What's pulling hair at this point is that Column C and D contains duplicates, but I would like to results to show one name and badge number. The code below is what I have so far and from this point, I am confused and going crazy. Please advise and help.

Sub DisplayUniques()


Dim rngCell As Range


For Each rngCell In Range("B2:B99")
If WorksheetFunction.CountIf(Range("C2:C99"), rngCell) = 0 Then
Range("E" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
For Each rngCell In Range("C2:C99")
If WorksheetFunction.CountIf(Range("B2:B99"), rngCell) = 1 Then
Range("F" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next

End Sub

Here's what my data looks like:

Employee In	Badge Number	Badge Number	Emp Name
ARIAS, FRANCISCO E (A004)	8014	12080	Jasper Grimaldo
BALLESTEROS, MIGUEL VIDAL (A5CX)	11985	16011	Adal Betanzos
BEASLEY, ZACKARY (AD7Y)	8580	16011	Adal Betanzos
BERMUDEZ, NOE ADALBERTO (ABRH)	6200	13276	Alberto Torres
BETANZOS, ADAL VOLT (AD31)	16011	13276	Alberto Torres
BONNER, ERYN HELENA (A5CD)	9905	8635	Andreas Villegas
BROWN, JAHEEME SEMAJ (AD7X)	15434	8789	Anthony Gutierrez
BUCIO-GARCIA, HAIRI (A75P)	13320	8789	Anthony Gutierrez
BUI, HUY NGUYEN (A011)	7635	21700	Arturo Santana
BYRD WHEELER, JESSICA EVELYN (A013)	7481	6897	Augustine Silva
CHA, ELY JJ (ACP3)	8620	18022	Beatrice Orozco
CHANHMANY, SUH (A017)	7597	18067	Chia Xyong
CIRILO, JOEL SELECT (AD1K)	16725	6595	Chu Her
CORRALES, MIRANDA CIARA (ACUP)	17160	8620	Ely Cha
CORRALES, RANDY REUBEN (ACJH)	17865	10931	Enrique Martinez
DECKER, MISTY DAWN (AACF)	16352	12747	Erik Nieto
ERICKSON, ILENE ANINA (AAOQ)	6968	8014	Frank Arias
GALINDO, GRISELDA (A5OO)	9342	9342	Griselda Galindo
GALINDO, JACQUELIN GIOVANA (ABJJ)	6753	9342	Griselda Galindo
GARCIA, STEVEN (A028)	3144	9342	Griselda Galindo
GOMEZ, JOSE MANUEL (A034)	7627	7635	Huy Bui
GONZALEZ, RICARDO (A037)	8062	6968	Ilene Erickson
GRIMALDO, JASPER VOLT (AD6M)	12080	13473	Joseph Tapia
GUTIERREZ, ANTHONY DARWIN (A1CM)	8789	13473	Joseph Tapia
HAGAN, ANDRES VOLT (AD6K)	11150	27337	Kenneth Yang
HER, CHU (A046)	6595	18556	Kia Xiong
HER, PHENG ALEX (A9GR)	5976	9088	Kou Yang
HER, YANG (A047)	18171	1416	Laura Cantor
HERRERA, LUIS FELIPE (AD04)	5077	1416	Laura Cantor
HINOJOSA MORALES, RAUL ANTONIO (A7WT)	10509	5077	Luis Herrera
HOBBS, LYRICK SELECT (AD4V)	14740	14740	Lyrick Hobbs
HYDER, ROBERT EDWARD (A7OJ)	12772	10017	Macy Xiong
JACKSON, WILLIAM EDWARD (ACKW)	6076	11261	Markys Rivera
LEE, MICKY (A6UV)	6865	17218	Martha Tascon
LOPEZ, GEORGE MANUEL (ABNB)	9180	1461	Melissa Perez
LOR, BAO (AD59)	23006	1461	Melissa Perez
LOZANO JR, STEVEN (ABAM)	8146	1461	Melissa Perez
MARTINEZ, ENRIQUE VOLT (AD2P)	10931	11985	Miguel Ballesteros
MEDINA, CONNIEFRANCES CLORINDA (A3BC)	6958	11985	Miguel Ballesteros
MENDOZA, LAURA (A3OQ)	1416	17160	Miranda Corrales
MINNIEWEATHER JR, ROBERT LEE (A3B6)	2388	3494	Nicholas Silva
MONTOYA, TYLER AVERY (ACL6)	6823	7816	Pamela Yang
NIETO, ERIK SELECT (ACZW)	12747	5976	Pheng Her
OROZCO, BEATRICE (A085)	18022	5976	Pheng Her
ORTEGA, RICHARD SELECT (AD2N)	8829	5976	Pheng Her
OUK, RY (A1A8)	4900	17865	Randy Corrales
PEREZ, MELISSA (ACUS)	1461	10509	Raul Morales
PEREZ, REBECCA KRISTINE (ACJX)	12085	10509	Raul Morales
PUENTE, GERARDO PARTNERS (AD2T)	26349	10509	Raul Morales
REYES HERRERA, MARTIN (AAWE)	4353	8879	Raul Rodriguez
RICO, ANGIE (A102)	7337	8879	Raul Rodriguez
RIVERA, MARCOS A (A104)	18122	8879	Raul Rodriguez
RIVERA, MARKYS ALEXANDER (AD13)	11261	11896	Robert Vang
RODRIGUEZ, AMBER DANIELLE (AB77)	19080	8714	Sam Vong Xiong
RODRIGUEZ, RAUL MENDOZA (ABFE)	8879	8714	Sam Vong Xiong
ROSALES, JASON (A3B7)	2442	19169	Sandy Sanouvong
SANCHEZ, SERENA FELICIA (ACO0)	12852	19169	Sandy Sanouvong
SANOUVONG, SANDY (A7L7)	19169	19169	Sandy Sanouvong
SANTANA, ARTURO (AD97)	21700	4141	Sara Nou Lee Xiong
SEM, SUNNY ROTH (AD5N)	9585	9585	Sunny Sem
SILVA, AUGUSTINE CAUSTRITA (AATT)	6897	1630	Tony Torrez
SILVA, NICHOLAS STORM (A9KZ)	3494	2767	Touchershee Yang
TAPIA, JOSEPH (ACWN)	13473	2767	Touchershee Yang
TASCON, MARTHA ROTH (AD3U)	17218	2767	Touchershee Yang
TORRES, ALBERTO (AD2S)	13276	6823	Tyler Montoya
TORREZ, TONY JR (AAVF)	1630	4888	Vang Yee
UNG, PINA (A5CW)	1947	6076	William Jackson
VANDERLAAN, WILLIAM (AAT4)	10794	18171	Yang Her
VANG, ANNRIE (A7C0)	1371
VANG, GAO HLEE (A7X2)	1486
VANG, MARK (A126)	7995
VANG, ROBERT (A6GN)	11896
VANG, YEE (A4JP)	4888
VERDUGO, JESSE RICHARD (ACZN)	4299
VIGIL, ANTHONY ALBERT (A130)	72352
VILLA, ABRAN (A131)	18013
VILLEGAS, ANDREAS FIDENCIO (A1J6)	8635
WASHINGTON, ARSHEAL OKEITH (A6QE)	2027
XIONG, CHEE NOU JOHNNY (A24W)	14333
XIONG, KIA (AAKC)	18556
XIONG, MACY (A7NU)	10017
XIONG, SAM VONG (A11A)	8714
XIONG, SARA NOU LEE (A6CR)	4141
XYONG, CHIA (A138)	18067
YANG, KENNETH HU CHEE (AC4Q)	27337
YANG, KOMOCHAN (A7FB)	5812
YANG, KOU (A11D)	9088
YANG, PAMELA (A144)	7816
YANG, TOUCHERSHEE JONATHAN (ACX4)	2767
YANG, YEELENG (AA1I)	0435

 

[JLGWhiz]

You can substitute the actual sheet names for the index numbers I used.

Sub t()
Dim c As Range
    With Sheets(1) 'Edit sheet name
        For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp))
            If Application.CountIf(.Range("C:C"), c.Value) = 0 Then
                .Range("A" & c.Row).Resize(, 2).Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        Next
    For Each c In .Range("C2", .Cells(Rows.Count, 3).End(xlUp))
            If Application.CountIf(.Range("B:B"), c.Value) = 0 Then
                .Range("C" & c.Row).Resize(, 2).Copy Sheets(2).Cells(Rows.Count, 3).End(xlUp)(2)
            End If
        Next
    End With
End Sub

 

[tchan05]

Wow! Thank you so much. I see where my approach wasn’t working and where I looked at it wrong. Thank you again...I gotta tell my co- workers to register. You are awesome!

 

[JLGWhiz]

Wow! Thank you so much. I see where my approach wasn’t working and where I looked at it wrong. Thank you again...I gotta tell my co- workers to register. You are awesome!

Tell your coworkers that when they register to be sure and read the posting guidlines and forum rules. Too many ignore those two items when joining the forum and do not get good results in their first few posts because they ignored them.

Thanks for the feedback,
Regards, JLG

 

[tchan05]

Thank you, will do...as a matter of fact, we were browsing through the site this morning and getting a better feel of the requirements. Thank you again.