Sir
I have a set of data in excel sheet A as below:
Sheet A
Name of the students Marks in english Marks in maths
A 45 74
B 38 58
C 52 65
D 45 65
E 88 72
F 36 45
G 61 33
H 90 70
J 33 41

Now I have another list(RELATED LIST) showing the names of students alone as below in sheet B as below:
Sheet B
Name of the students
A
E
C
D
B
G
F
I
H
J
K
l
Now I want to compare the two excel sheets A and B in respect of a few selected items selected from table B:
Selected items from Sheet B
Name of the students
A
E
C
K
Now I want to match all the selected items from Table B (in table A) by linking the two tables as in example shown below;
Linked sheet should match ( All the related items must match itself) and appear as below:
Name of the students
(Sheet B) Name of the students
(Sheet A) Marks in English
(Sheet A) Marks in maths
(Sheet A)
A A 45 74
B B 38 58
C C 52 65
D 45 65
E 88 72
F 36 45
G 61 33
H 90 70
J 33 41
K
All the selected items from sheet B must match (with relevant rows) available in sheet A. If required alphabetical sorting has to be done to extract the matched items separately.
Note: I was told that this was possible with ms access by assigning primary key. Is it possible to do the same with
Excel spread sheet( I use ms office 2007).
Please help.
Vks 64

Please click on GO ADVANCED and use the paperclip icon to upload your sample workbook. The workbook should include all your layout sheets, plus a manual mockup of your desired results, an AFTER sheet.

Dear sir,
For the comfort of explaining I have enclosed a real time data as per the actual requirement

In Data-sheet1,End of the data released by the STock exchange are available as released at the end of the every day. The data gets changed everyday

In portfolio- sheet2, list of few shares forming part of the column A of data-sheet1 are selected and placed for monitoring.

Now the data relating to the list of few shares in portfolio-sheet2, are to be tracked from the Data-sheet1, every day on a daily basis. For the purpose the two sheets viz. data sheet1 and portfolio-sheet2 are merged as shown in sheets merged-sheet3

Now requirement is

1) By applying some formulas or otherwise the items selected in portfolio-sheet2 gets matched with the related items of Data-sheet1(ie. items in column titled SYMBOL of portfolio-sheet2 MATCH itself with the items in column titled SYMBOL of data sheet1) as in sheet 4

2) Now sorting the ColumnA , A to Z gets the related rows arranged as in sheet 5

3) the related rows are now extracted and the required information for the items in respect of portfolio-sheet2 as shown in result-sheet6

Hope the results are achievable as in the process naratted above. If not kindly help me getting the above results thro the ways and means possible
Thanking you
vks64

Workbook enclosed

In portfolio-sheet2 put this formula in B2, then copy down and to the right...

=INDEX('data-sheet1'!B:B, MATCH($A2, 'data-sheet1'!$A:$A, 0))

You will have to format column I to be date format.

Thank you sir. I have my requirement met. But I have to copy the formula over the full field to get the result. And it is not returning the result for time column sir. Finally please explain what do mean to say "You will have to format column I to be date format." Thank you once again sir. vks64

After you have entered your formula and copied it down and across all the needed columns, you will see column I has number in it in 40000 range. If you didn't know this already, that's an Excel raw date format.

The Excel raw number for 1/1/2011 is 40544. Each day after that is one number higher. From that raw number, you can instruct Excel to "display" that number in any "date format" you wish.

1/1/2011
January 1, 2011
1-Jan-11

Pretty much any "look" you can think of can be created.

After you've entered your formulas in column I:

1) Highlight column I
2) Press Ctrl-1 to open the cell formatting window
3) Click the NUMBER tab
4) Select the Category: Time, then choose the Type of display you want.

Thank You. It is very useful. I am extremely delighted..
Thank You once again!
vks64