Compare Two Lists

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Hi Everyone,

I have Two Lists.
List One ( A ) is in Cells B2:G1000.
List Two ( B ) is in Cells I2:N1250 ish.
It is to do with Pupils in a School over a Two Year Period.
List One for Arguments Sake is Year 06.
List Two for Arguments Sake is Year 07.
Some Pupils are in Year 06 but NOT in Year 07 and Vice Versa.
What I would like to do is Scatter List A & List B to be Next to Other ( in Another Sheet Named "Combined" for Example Starting in Cell B2:G? & I2:N? Whatever ) Relevant to the Pupil.
So if a Pupil is in List A ( Year 06 ) but NOT in List B ( Year 07, because they might have Left in Year 06 for Example ), Leave the Cells I:N Blank etc BUT Carry on Looking for that Particular Pupil in List A against List B until a Match is Found, if it is NOT, then List A will be Blank in Cells B:N.
It will Also Work from List B to List A.
I am Confused Trying to Explain this so I Very Much Doubt that Anyone can Possibly Understand what I am Trying to Achieve.

Any Ideas will be Very Much Appreciated.
All the Best.
SHADO
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Hi Again,

Thinking about a Clearer Explanation.
If the Entry in List A does NOT Correspond with List B Leave the Cells Blank until it does.

All the Best.
SHADO
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
When it's complicated to explain, the best way is to post an HTML version of your worksheet ( see the sticky FAQ thread ).

Is this what you are trying to do?
Book1
FGHIJ
1920062007(CombinedSheet)
20chrisjohnchris
21johnkenjohnjohn
22kenmarthakenken
23marthamikemarthamartha
24marymary
25timmike
26tim
Sheet1
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Hi tmcfadden,

Thanks for the Reply.
That is Exactly what I am Trying to do.

All the Best.
SHADO
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915

ADVERTISEMENT

Any Help will be Greatly Appreciated.

All the Best.
SHADO
 

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
A dictionary object with keys maybe helpful. Google it for a solution.

Best Regards.
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915

ADVERTISEMENT

Thanks for the replies,

I KNOW Very Little about VB.
There is a Unique Student ID Number being the Username.
For Example, the Username of 06handg, is made up of the Year ( 06 ), the First 4 Letters of their Surname ( hand ) and the First Letter of their Christian Name.
The Username will Only Change when the Student goes up a Year. For Example, the Student Username 06handg will Become Student Username 07handg.
The only thing being the Student might NOT be in Year 06 but Started Sometime in Year 07. Also a Student might Leave in Year 06 so will NOT be there in Year 07.

Any Help will be Greatly Appreciated.
All the Best.
SHADO
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,026
No need for VBA. Suppose you have the name only (no leading 2 character year) for the 1st year are in C6:C11 and the 2nd year in D6:D9. Also suppose the result is in F6:G{whatever}

Then in F6 enter =C6. In G6 enter =IF(COUNTIF($F$6:F6,D6)=1,"",D6).
In F7 enter =IF(COUNTIF($G$6:G6,C7)=1,"",C7)

Copy F7 as far down as you have data in C. Copy G6 as far down as you have data in D.
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Thanks for the reply tusharm,

What I would Ideally like is for the Result to be as tmcfadden Answer above Please.
The First List ( A ) is in Cells B2:G1000.
The Second List ( B ) is in Cells I2:N1250 ish.
The Unique Username is in Cells B2:B1000 & I2:I Whatever.
I would like ALL the Data that is in Both Lists, the Only Difference will be that there will be Blank Cells ( Ranges B:G & I:N ) in BOTH Lists.

Thanks in Advance.
All the Best.
SHADO
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Hi Everyone,

The Reason I would like Code to do this is because it will take MANY Hours to Insert Cells Down ( in BOTH Lists ) so that Both Lists Tie Up to the Username.

Thanks in Advance.
All the Best.
SHADO
 

Watch MrExcel Video

Forum statistics

Threads
1,114,037
Messages
5,545,667
Members
410,697
Latest member
srishtijain0708
Top