# Compare Two Lists

#### S.H.A.D.O.

##### Well-known Member
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.

### 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
Hi Again,

If the Entry in List A does NOT Correspond with List B Leave the Cells Blank until it does.

All the Best.

##### Board Regular
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

That is Exactly what I am Trying to do.

All the Best.

#### S.H.A.D.O.

##### Well-known Member

Any Help will be Greatly Appreciated.

All the Best.

#### northwolves

##### Well-known Member
A dictionary object with keys maybe helpful. Google it for a solution.

Best Regards.

#### S.H.A.D.O.

##### Well-known Member

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.

#### tusharm

##### MrExcel MVP
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

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.

All the Best.

#### S.H.A.D.O.

##### Well-known Member
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.

All the Best.

Replies
9
Views
296
Replies
0
Views
111
Replies
1
Views
805
Replies
0
Views
205
Replies
8
Views
385

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.

### Which adblocker are you using?

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

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