Thread: populate cells using Thanks:  2 Post #5330448 (1)Post #5330494 (1) Likes:  1 Post #5330502 (1)

1. 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,

2. 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

3. 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

4. Re: populate cells using

Originally Posted by sandy666
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. Re: 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.
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

 A B C D E 1 StudentID Course 2 a1 French 3 a2 Math 4 a3 English 5 a4 Science 6 a5 French 7 a6 English 8 a1 Science 9 a3 French 10 11 12 StudentID English Math Science French 13 a1 x x 14 a2 x 15 a3 x x 16 a4 x 17 a5 x 18 a6 x 19 a7 20 a8 21 a9 22 a10

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)

 A B C D E 1 StudentID Course 2 a1 French 3 a2 Math 4 a3 English 5 a4 Science 6 a5 French 7 a6 English 8 a1 Science 9 a3 French 10 11 12 StudentID English Math Science French 13 a1 x x 14 a2 x 15 a3 x x 16 a4 x 17 a5 x 18 a6 x 19 a7 20 a8 21 a9 22 a10

 Cell Formula B13 =IF(COUNTIFS(\$A\$2:\$A\$9,\$A13,\$B\$2:\$B\$9,B\$12),"x","")

Excel tables to the web >> Excel Jeanie HTML 4

6. Re: populate cells using

Originally Posted by tyija1995
 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. Re: populate cells using

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