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
Paul Black wrote:
> Thanks for the Reply Franck,
>
> I think what is Confusing the Issue is that for EACH List there are 6
> Entries for EACH Student.
>
> For Example in List A ...
>
> Cells B has the Username
> Cells C has the Surname
> Cells D has the Christian Name
> Cells E:G has Other Information
>
> ... and Cells I:N in List B has the Same Titled Information.
>
> There is a Unique Student ID Number being the Username, Lets Say in
> List A & List B.
> 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 ( Year 06 ) will Become Student
> Username 07handg ( Year 07 ).
> 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. That is Why there will be Blank Cells Incorporated
> in BOTH Lists.
> If the Student is in BOTH Years then the 07handg in List B will be
> Directly Next to 06handg in List A etc.
> I Actually want Everybody in BOTH Lists to Appear in the New List.
>
> I Hope this Explains it a Bit Clearer.
> Thanks Again in Advance.
> All the Best.
> Paul
>
> Franck wrote:
> > 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
> >
> >
> > Paul Black wrote:
> > > 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
> > >
> > > Franck wrote:
> > > > 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.
> > > >
> > > >
> > > > acampbell...@yahoo.com wrote:
> > > > > 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.
> > > > >
> > > > >
> > > > > Paul Black wrote:
> > > > > > 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
|
Similar Threads
