populate cells using

adamelston

New Member
Joined
Jul 22, 2016
Messages
31
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:

StudentIDCourse
a1French
a2Math
a3English
a4Science
a5French
a6English
a1Science
a3French

<tbody>
</tbody>

and T2 is:

StudentIDEnglishMathScienceFrench
a1
a2
a3
a4
a5
a6
a7
a8
a9
a10

<tbody>
</tbody>

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:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
maybe something like this with PivotTable

Count of CourseCourse
StudentIDEnglishFrenchMathScience
a1
1​
1​
a2
1​
a3
1​
1​
a4
1​
a5
1​
a6
1​
 
Upvote 0
ABCDEFGH
1StudentIDCourseStudentIDEnglishMathScienceFrench
2a1Frencha10011
3a2Matha20100
4a3Englisha31001
5a4Sciencea40010
6a5Frencha50001
7a6Englisha61000
8a1Sciencea70000
9a3Frencha80000
10a90000
11a100000

<tbody>
</tbody>


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
 
Upvote 0
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.

Excel Workbook
ABCDE
1StudentIDCourse
2a1French
3a2Math
4a3English
5a4Science
6a5French
7a6English
8a1Science
9a3French
10
11
12StudentIDEnglishMathScienceFrench
13a1,Table2:],Table1:],Table2,]),&quot;x&quot;,&quot;&quot;)]xx
14a2x
15a3xx
16a4x
17a5x
18a6x
19a7
20a8
21a9
22a10
Courses



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

Excel Workbook
ABCDE
1StudentIDCourse
2a1French
3a2Math
4a3English
5a4Science
6a5French
7a6English
8a1Science
9a3French
10
11
12StudentIDEnglishMathScienceFrench
13a1 xx
14a2x
15a3xx
16a4x
17a5x
18a6x
19a7
20a8
21a9
22a10
Courses (2)
 
Upvote 0
ABCDEFGH
1StudentIDCourseStudentIDEnglishMathScienceFrench
2a1Frencha10011
3a2Matha20100
4a3Englisha31001
5a4Sciencea40010
6a5Frencha50001
7a6Englisha61000
8a1Sciencea70000
9a3Frencha80000
10a90000
11a100000

<tbody>
</tbody>


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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top