Sorting

[Khalil Handal]

Hi,
I have a workbook with 14 sheets related to students.
Sheet "personal" has personal information like address, date of birth...
Sheet "marks" has the different marks for that student.
The names of the students are copied from sheet "personal" (range A1458)
to sheet "Marks" Joined in range B14:B58. The names are also copied to other
sheets. This is done to avoide retyping the names another time in each
sheet.

When I sort the names alphabeticly in sheet "Personal" (sort for the whole
line), automaticly the names are changed in sheet "Marks" which result in
having the name with the WROMG information related to that name.
I think that this is related to my BAD design originaly!

Is there a way to solve this???

 

[Dave Peterson]

Put a unique value in each worksheet--studend id or unique student name (no
duplicate Mary Jones's).

Then use =vlookup() to return the values you need returned.

 

[Khalil Handal]

Hi,
Not Clear to me!!

A unique value for each student might be for instance like "first initials
and number starting 1" (MJ1, MT2...) that would be in colomn A for example.
How would this work with vlookup() with THE WHOLE LINE in the other
worksheet (Marks) for instance???
Do you mean instead of the :
=Personal!B14&" "&Personal!C14&" "&Personal!D14&" "&Personal!E14
in the "Marks" sheet use the vlookup() ?????

Can you explain more?

 

[Dave Peterson]

I mean that each person has to have a unique identifier--maybe a number, maybe a
name.

Khilil A. Handal
vs
Khilil B. Handal
vs
Khilil Ax. Handal

Then you can put that in each worksheet in column A and use

=vlookup(a2,sheet2!a:f,2,false)
to bring back the data from column B of sheet2.

You may have to rearrange your "sending" worksheets so that they look like
tables, too.

In fact, I'd put all my data on one worksheet. Then use data|filter|autofilter
to show the rows that I want.

 

[Dave Peterson]

I don't open attachments.

Wasn't I clear when I said that your data had to be more tabular to use
=vlookup() effectively?

 

[Khalil Handal]

Hi,
Putting the Data in one sheet will solve lot of problems in spite of the
fact that I will have about 150 colomns that can be hidden or shown each
part alone according to my needs.

It's Ok for the attachement! I though it will give you a clear idea when you
see it.

Thanks for the help.

 

[Dave Peterson]

ps. This is text only newsgroup. Binary attachments (and posts in HTML/rich
text) are frowned upon most most readers.

 

[Khalil Handal]

Thanks for the note, Here it is in text:
In work sheet personal:

Full Name Birth date
N° First Father Grand Father Family Full Name National Place of Birth
day month year
2 Anna Joseph Peter Carlson Barbara Sam Charly Noah British City B 14
1 1999
3 Barbara Sam Charly Noah Marry George Joseph Sabbath Canadian City C
15 6 1992
1 Marry George Joseph Sabbath Marry George Joseph Sabbath Canadian
City A 14 6 1993
4 Sara Robert Antony Bally Sara Robert Antony Bally Canadian City D 28
9 1996



step 1
Select lines 14 to 17 sort according to colomn F no header


In sheet Marks:
Grade 7 Birth Date Religion English
Sem I Sem II Sem I Sem II
N° Full Name Nationality Place of Birth dd mm yyyy Mark 1 Mark 2 Mark
3 Mark 1 Mark 2 Mark 3 Mark 1 Mark 2 Mark 3 Mark 1 Mark 2 Mark 3
1 Anna Joseph Peter Carlson British City B 14 1 1999 69 62 72 50 60
55 85 90 66 50 70
2 Barbara Sam Charly Noah Canadian City C 15 6 1992 82 60
3 Marry George Joseph Sabbath Canadian City A 14 6 1993 70
60
4 Sara Robert Antony Bally Canadian City D 28 9 1996 65 45



Before sorting Sheet Personal:
Marry George Joseph Sabbath Canadian City A 14 6 1993 69 62 72 50 60
55 85 90 66 50 70
Anna Joseph Peter Carlson British City B 14 1 1999 82 60


After Sorting Sheet Personal:
Anna Joseph Peter Carlson British City B 14 1 1999 69 62 72 50 60 55
85 90 66 50 70
Marry George Joseph Sabbath Canadian City A 14 6 1993 70 60

Marks of Marry became for Anna after the sorting of Sheet Personal
which is not true.


Hope this works.
Khalil

 

[Dave Peterson]

To make =vlookup() work, you're going to have to make your data fit into a
table.

Column A will be the key (unique)
column B:whatever will hold the data that you want to return.

 

[Khalil Handal]

Hi,
At first I had N/A (Not avilable).
Data is in table. I will work now accordig to to your advice.
Thanks for the help.

 

[Dave Peterson]

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html

Hi,
At first I had N/A (Not avilable).
Data is in table. I will work now accordig to to your advice.
Thanks for the help.

 

[Khalil Handal]

Hi,
Thank you very much for the link and for Debra for writing it.
It makes it more clear to me.