populate cells using

adamelston

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

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,842
maybe something like this with PivotTable

Count of CourseCourse
StudentIDEnglishFrenchMathScience
a1
1​
1​
a2
1​
a3
1​
1​
a4
1​
a5
1​
a6
1​
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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
 

adamelston

New Member
Joined
Jul 22, 2016
Messages
23
maybe something like this with PivotTable

Count of CourseCourse
StudentIDEnglishFrenchMathScience
a1
1​
1​
a2
1​
a3
1​
1​
a4
1​
a5
1​
a6
1​

<tbody>
</tbody>
Thanks sandy666 though this wouldn't work for me here
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,252
Office Version
365
Platform
Windows
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)
 

adamelston

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

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,223
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top