Ask Experts Questions for FREE Help !
Ask
    vks64's Avatar
    vks64 Posts: 53, Reputation: 1
    Junior Member
     
    #1

    May 18, 2011, 01:38 PM
    Excel spreadsheet
    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
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    May 19, 2011, 03:39 PM

    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.
    vks64's Avatar
    vks64 Posts: 53, Reputation: 1
    Junior Member
     
    #3

    May 21, 2011, 05:49 AM

    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
    Attached Files
  1. File Type: xls matching columns.xls (131.0 KB, 120 views)
  2. vks64's Avatar
    vks64 Posts: 53, Reputation: 1
    Junior Member
     
    #4

    May 22, 2011, 09:51 AM
    Comment on JBeaucaire's post
    Workbook enclosed
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #5

    May 23, 2011, 04:57 PM

    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.
    vks64's Avatar
    vks64 Posts: 53, Reputation: 1
    Junior Member
     
    #6

    May 24, 2011, 07:51 PM
    Comment on JBeaucaire's post
    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
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #7

    May 24, 2011, 10:11 PM

    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.
    vks64's Avatar
    vks64 Posts: 53, Reputation: 1
    Junior Member
     
    #8

    May 26, 2011, 09:39 AM
    Comment on JBeaucaire's post
    Thank You. It is very useful. I am extremely delighted..
    Thank You once again!
    vks64

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel Links to another spreadsheet [ 1 Answers ]

I read a reply from JBeaucaire regarding a same issue. Please forgive me - I'm not familiar with formuals. I tried JBeaucaire's answer step by step and it didn't work. Source spreadsheet name: 2010-2011 Dealer Training Records.xlsx Destination spreadsheet name: March 3-5 Roster, 1.20.11.xlsx...

Displaying formulas in Excel Spreadsheet [ 1 Answers ]

Formula currently appears in the cell rather than formula result value. I want the result value to appear in the cell and the formula to appear only in the formula bar. How do I make this change? I am working in Excel 2003.

Excel Spreadsheet [ 1 Answers ]

I need a macro or something for a website that has a excel spreadsheet. My client has a excel spreadsheet out on a website. They have invoice # on the excel spreadsheet and want to be able to scan the invoice and have it linked to the invoice on the website so clients can see the scanned document....

Excel Spreadsheet [ 2 Answers ]

Does anyone know how I can get an Excel spreadsheet to open at start-up? Thanks!


View more questions Search