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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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,029
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
 

Forum statistics

Threads
1,141,665
Messages
5,707,696
Members
421,524
Latest member
Bharath99

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
Top