populate cells using

A

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:

[TABLE="width: 174"]
<tbody>[TR]
[TD="width: 87"]StudentID[/TD]
[TD="width: 87"]Course[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD]French[/TD]
[/TR]
[TR]
[TD]a2[/TD]
[TD]Math[/TD]
[/TR]
[TR]
[TD]a3[/TD]
[TD]English[/TD]
[/TR]
[TR]
[TD]a4[/TD]
[TD]Science[/TD]
[/TR]
[TR]
[TD]a5[/TD]
[TD]French[/TD]
[/TR]
[TR]
[TD]a6[/TD]
[TD]English[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD]Science[/TD]
[/TR]
[TR]
[TD]a3[/TD]
[TD]French[/TD]
[/TR]
</tbody>[/TABLE]

and T2 is:

[TABLE="width: 435"]
<tbody>[TR]
[TD="width: 87"]StudentID[/TD]
[TD="width: 87"]English[/TD]
[TD="width: 87"]Math[/TD]
[TD="width: 87"]Science[/TD]
[TD="width: 87"]French[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
maybe something like this with PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Count of Course[/td][td=bgcolor:#DDEBF7]Course[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]StudentID[/td][td=bgcolor:#DDEBF7]English[/td][td=bgcolor:#DDEBF7]French[/td][td=bgcolor:#DDEBF7]Math[/td][td=bgcolor:#DDEBF7]Science[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a1[/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a2[/td][td][/td][td][/td][td]
1​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]a3[/td][td]
1​
[/td][td]
1​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]a4[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a5[/td][td][/td][td]
1​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]a6[/td][td]
1​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]StudentID[/TD]
[TD]Course[/TD]
[TD][/TD]
[TD]StudentID[/TD]
[TD]English[/TD]
[TD]Math[/TD]
[TD]Science[/TD]
[TD]French[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a1[/TD]
[TD]French[/TD]
[TD][/TD]
[TD]a1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a2[/TD]
[TD]Math[/TD]
[TD][/TD]
[TD]a2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a3[/TD]
[TD]English[/TD]
[TD][/TD]
[TD]a3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a4[/TD]
[TD]Science[/TD]
[TD][/TD]
[TD]a4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]a5[/TD]
[TD]French[/TD]
[TD][/TD]
[TD]a5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]a6[/TD]
[TD]English[/TD]
[TD][/TD]
[TD]a6[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]a1[/TD]
[TD]Science[/TD]
[TD][/TD]
[TD]a7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]a3[/TD]
[TD]French[/TD]
[TD][/TD]
[TD]a8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


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
sandy666 said:
maybe something like this with PivotTable

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Count of Course[/TD]
[TD="bgcolor: #DDEBF7"]Course[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]StudentID[/TD]
[TD="bgcolor: #DDEBF7"]English[/TD]
[TD="bgcolor: #DDEBF7"]French[/TD]
[TD="bgcolor: #DDEBF7"]Math[/TD]
[TD="bgcolor: #DDEBF7"]Science[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a1[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a2[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a3[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a5[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a6[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thanks sandy666 though this wouldn't work for me here
 
Upvote 0
adamelston said:
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.
Click to expand...
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
tyija1995 said:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]StudentID[/TD]
[TD]Course[/TD]
[TD][/TD]
[TD]StudentID[/TD]
[TD]English[/TD]
[TD]Math[/TD]
[TD]Science[/TD]
[TD]French[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a1[/TD]
[TD]French[/TD]
[TD][/TD]
[TD]a1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a2[/TD]
[TD]Math[/TD]
[TD][/TD]
[TD]a2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a3[/TD]
[TD]English[/TD]
[TD][/TD]
[TD]a3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a4[/TD]
[TD]Science[/TD]
[TD][/TD]
[TD]a4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]a5[/TD]
[TD]French[/TD]
[TD][/TD]
[TD]a5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]a6[/TD]
[TD]English[/TD]
[TD][/TD]
[TD]a6[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]a1[/TD]
[TD]Science[/TD]
[TD][/TD]
[TD]a7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]a3[/TD]
[TD]French[/TD]
[TD][/TD]
[TD]a8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


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
Click to expand...

Thanks very much for this - it is helpful.
A
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
REDUCE with SUBSTITUTE not quite working
Replies
9
Views
238
tigerzen
T
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
393
Alex Blakenburg
A
S
  • Solved
need to remove all data after "n"
Replies
2
Views
460
sksanjeev786
S
J
  • Solved
populating dates of the current month with a formula
Replies
6
Views
348
jbrown021286
J
C
  • Solved
index match excel formula to vba code
Replies
9
Views
330
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,711
Messages
6,174,025
Members
452,542
Latest member
Bricklin

Share this page

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