Compare Two Lists

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

Paul Black

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.
Paul
 
Do you mean this :
2 sheets
first contain 2 list
second is result of match found that are both list A and B

i dont know if you know a bit of database but i you do explain t as it
was one
it's sometime easier.


Paul Black a écrit :
 
Do you have any sample data you can post? Is there something unique to
the students such as student i.d. number? You may have John Smith in
both list but not be the same John Smith.
 
i think i get it, his format is like the same thing 2 time, column B is
the same thing that you found in columns 1
instead of putting 2007 jsut after 2006 they put it on the right but
they are actually the same format i think.
Acampbell is right you need unique id because 2 different people can
have the same name.
 
Thanks for the replies,

Yes there is a unique student ID number being the user name.
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 year06 so will not
be there in year 07.

Any help will be greatly appreciated.
All the Best.
Paul
 
Thanks for the replies,

Yes there is a unique student ID number being the user name.
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 year06 so will not
be there in year 07.

Any help will be greatly appreciated.
All the Best.
Paul
 
let's simplify your problem first in order to understant it better

So in other word
in 2006 not 2007 = leave
in 2006 and 2007 = still there
in 2007 only = still there
in your new spreadsheet you want (example) :
06smitJ ... 07simoT...
06randF... 07burkR...
06bushG... 07smitJ...

Compiled
07smitJ...(dont show 06 because 07 is more recent data)
and all other because they arent double so no new data
so in other case everyone from my list would appear except the 06smitJ

am i right ?
or only show people that were there both year ?

to be in this list whats the parameter the student have to meet
ex :
was in 06 not in 07
was in 06 and 07
was not in 06 and in 07
was in 06 and in or not 07
was in or not 06 and in 07
 
I think it become more clear :P

so what you want is take all name no matter what but if the same person
appear in both list (but they will have 06 and 07 in difference)
you put the 06 version of the student on the list but the 07 version
put it on the right with X cells after the last "other info (E to G)",
sumthign like i has username C has surname ...

but the complicated thing i get it, a person can be in 07 but not in 06
so thats tough to make.

For sure you will need a vba code.
If the problem is that, i got the solution.

You need a kind of complex code.
lets check how we want the solution

first we want the most left list being only fill with 06 user or 07 is
they were not there in 06

and after that we want put 07 users
and after that put 07users that were in 06 on the right of themself

So i think that resume the most of it.
Now what you need is : 3 SpreadSheets (let me explain)
Sheet 1 : Have the 2 lists of the users (where we gonna take data)
Sheet 2 : Our result
Sheet 3 : The buffer (we gonna use it for 2 exact same users)

Here are the step :
Read all users from first of 06 to last and first 07 to last
meanwhile
at each record you will have to do some stuff
REad this generic code below (it`s not real code just to show you my
point)

Do until Allrecordread
readsheet1record
saveittosheet3 ' the buffer sheet
If recordis06 then 'if it a 06 user
saveittosheet2(A:G) 'save it on our list because it can't
exist already
else 'if it a 07 user
'here the trick is to check it the user exist in the buffer
sheet (sheet3)
if currentrecord isnot in sheet3 then 'is the current record
is not in the buffer
saveittosheet2(A:G) 'the output sheet add it because
it mean it didnt exist in 06
else 'mean it was in 06 under 06username
search in the sheet 2 for the line of his 06user
saveittosheet2(I:N)
end if
end if
loop

to scan through buffer you will have to use somethign like this , and
same when if it exist to find the 06 user in the sheet2 to know the
line where the 06 user is

Dim isuserexist as integer
Do until A:1 <> ""
If Mid(A:1,1) = mid(currentuserwesearch,1) 'Mid to remove the 06/07
to compare
isuserexist = 1
end if
Loop

Well i think it's pretty clean and mean the same thing as i tought of
your problem
 
Back
Top