populate cells using
Results 1 to 7 of 7

Thread: populate cells using

  1. #1
    New Member
    Join Date
    Jul 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default populate cells using

    Hi, I've been trying unsuccessfully to populate a table (T2) with info from a table (T1), perhaps using an 'x' or 'y' to show which student is on which course. T1 is:

    StudentID Course
    a1 French
    a2 Math
    a3 English
    a4 Science
    a5 French
    a6 English
    a1 Science
    a3 French

    and T2 is:

    StudentID English Math Science French
    a1
    a2
    a3
    a4
    a5
    a6
    a7
    a8
    a9
    a10

    I've been trying index/match but I am not sure how to deal with two criteria, I would be very grateful for any help.

    Thanks,
    Last edited by adamelston; Aug 23rd, 2019 at 03:41 AM.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,657
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: populate cells using

    maybe something like this with PivotTable

    Count of Course Course
    StudentID English French Math Science
    a1
    1
    1
    a2
    1
    a3
    1
    1
    a4
    1
    a5
    1
    a6
    1
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    647
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: populate cells using

    A B C D E F G H
    1 StudentID Course StudentID English Math Science French
    2 a1 French a1 0 0 1 1
    3 a2 Math a2 0 1 0 0
    4 a3 English a3 1 0 0 1
    5 a4 Science a4 0 0 1 0
    6 a5 French a5 0 0 0 1
    7 a6 English a6 1 0 0 0
    8 a1 Science a7 0 0 0 0
    9 a3 French a8 0 0 0 0
    10 a9 0 0 0 0
    11 a10 0 0 0 0


    Where E2 is an array formula - copy in to the entire table after entering without E2 selected (it will error if you have E2 selected when pasting -"You cant change part of an array")

    --ISERR(INDEX($A$1:$B$9,MATCH(1,($D2=$A$1:$A$9)*(E$1=$B$1:$B$9),0)))

    Then you can CF values = 0 as ;;; so they are hidden perhaps so it only shows the 1's
    √-1 2³ ∑ π
    …And it was delicious!

  4. #4
    New Member
    Join Date
    Jul 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: populate cells using

    Quote Originally Posted by sandy666 View Post
    maybe something like this with PivotTable

    Count of Course Course
    StudentID English French Math Science
    a1
    1
    1
    a2
    1
    a3
    1
    1
    a4
    1
    a5
    1
    a6
    1
    Thanks sandy666 though this wouldn't work for me here

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: populate cells using

    Quote Originally Posted by adamelston View Post
    Hi, I've been trying unsuccessfully to populate a table (T2) with info from a table (T1), perhaps using an 'x' or 'y' to show which student is on which course.
    If the tables are formal Excel tables (Insert ribbon tab -> Table -> My table has headers) called Table1 and Table2 then try this formula copied across the top row of Table 2. You may need to then go to each of those cells press F2 to edit then Enter to confirm.

    Courses

    ABCDE
    1StudentIDCourse
    2a1French
    3a2Math
    4a3English
    5a4Science
    6a5French
    7a6English
    8a1Science
    9a3French
    10
    11
    12StudentIDEnglishMathScienceFrench
    13a1 xx
    14a2 x
    15a3x x
    16a4 x
    17a5 x
    18a6x
    19a7
    20a8
    21a9
    22a10

    Spreadsheet Formulas
    CellFormula
    B13=IF(COUNTIFS(Table1[[StudentID]:[StudentID]],Table2[@[StudentID]:[StudentID]],Table1[[Course]:[Course]],Table2[[#Headers],[English]]),"x","")


    Excel tables to the web >> Excel Jeanie HTML 4


    If the 'Tables' are just normal Excel ranges, then try this, copied across and down.

    Courses (2)

    ABCDE
    1StudentIDCourse
    2a1French
    3a2Math
    4a3English
    5a4Science
    6a5French
    7a6English
    8a1Science
    9a3French
    10
    11
    12StudentIDEnglishMathScienceFrench
    13a1 xx
    14a2 x
    15a3x x
    16a4 x
    17a5 x
    18a6x
    19a7
    20a8
    21a9
    22a10

    Spreadsheet Formulas
    CellFormula
    B13=IF(COUNTIFS($A$2:$A$9,$A13,$B$2:$B$9,B$12),"x","")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Jul 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: populate cells using

    Quote Originally Posted by tyija1995 View Post
    A B C D E F G H
    1 StudentID Course StudentID English Math Science French
    2 a1 French a1 0 0 1 1
    3 a2 Math a2 0 1 0 0
    4 a3 English a3 1 0 0 1
    5 a4 Science a4 0 0 1 0
    6 a5 French a5 0 0 0 1
    7 a6 English a6 1 0 0 0
    8 a1 Science a7 0 0 0 0
    9 a3 French a8 0 0 0 0
    10 a9 0 0 0 0
    11 a10 0 0 0 0


    Where E2 is an array formula - copy in to the entire table after entering without E2 selected (it will error if you have E2 selected when pasting -"You cant change part of an array")

    --ISERR(INDEX($A$1:$B$9,MATCH(1,($D2=$A$1:$A$9)*(E$1=$B$1:$B$9),0)))

    Then you can CF values = 0 as ;;; so they are hidden perhaps so it only shows the 1's
    Thanks very much for this - it is helpful.
    A

  7. #7
    New Member
    Join Date
    Jul 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: populate cells using

    That is a great help, I will use this method, thank you very much!
    A

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •