idea to create a 3rd table from other 2 tables

[lezawang]

fHi
I have a file which has 2 sheets. The first sheet has table like this

student no	name	email
1	smith, john	john.smith@hello.com
22	clark,mary	mary.clark@hello.com

<tbody>
</tbody>

The second sheet has table like this

last name	first name	student No	class code	full name
smith	john	1	abc-123	smith, john
smith	john	1	xyz-111	smith, john
smith	john	1	aaa-120	smith, john
clark	mary	22	abc-123	clark,mary
clark	mary	22	hhh-000	clark,mary

<tbody>
</tbody>

Now what I wan to do, is to create a 3rd sheet which is going to look like this

student no	student name	email address	courses
1	smith, john	john.smith@hello.com	abc-123
1			xyz-111
1			aaa-123
22	clark, mary	mary,clark@hello.com	abc-123

<tbody>
</tbody>

Any idea how to create the 3rd table? I was thinking of Pivot table. I want a solution but not VBA as I am not familiar with it yet. Thanks a lot.

 

[dUBBINS]

=VLOOKUP("SMITH, JOHN",B2:C100,2,FALSE)

Replace Smith, John with the first cell reference where this list truly starts. Like B2 (in your example).

Replace B2:C100 with the range that encompasses both the names and email addresses.

Replace the 2 with whatever column the email addresses are in. The first column in this count is the first one in your range. In my example formula, Column B = 1 and Column C = 2.

This will pull in your email addresses for each name in your list. Drag down as far as needed.

False just makes the formula look for an exact match. Do not change this.

 

[azumi]

Hope this works

https://simpan.ugm.ac.id/s/2a39e142a4c912be1111b0ae635b13c1

 

[lezawang]

Thanks for your reply. Where should I put this Vlookup() function? and why you are passing one name as an argument?

=VLOOKUP("SMITH, JOHN",B2:C100,2,FALSE)

Replace Smith, John with the first cell reference where this list truly starts. Like B2 (in your example).

Replace B2:C100 with the range that encompasses both the names and email addresses.

Replace the 2 with whatever column the email addresses are in. The first column in this count is the first one in your range. In my example formula, Column B = 1 and Column C = 2.

This will pull in your email addresses for each name in your list. Drag down as far as needed.

False just makes the formula look for an exact match. Do not change this.

 

[hiker95]

lezawang,

Here is a macro solution for you to consider.

You can change the worksheet names in the macro.

Sample raw data worksheets:

Excel 2007
	A	B	C
1	student no	name	email
2	1	smith, john	john.smith@hello.com
3	22	clark,mary	mary.clark@hello.com
4
Sheet1

Excel 2007
	A	B	C	D	E
1	last name	first name	student No	class code	full name
2	smith	john	1	abc-123	smith, john
3	smith	john	1	xyz-111	smith, john
4	smith	john	1	aaa-120	smith, john
5	clark	mary	22	abc-123	clark,mary
6	clark	mary	22	hhh-000	clark,mary
7
Sheet2

And, after the macro in a new worksheet:

Excel 2007
	A	B	C	D
1	student no	student name	email address	courses
2	1	smith, john	john.smith@hello.com	abc-123
3	1			xyz-111
4	1			aaa-120
5	22	clark,mary	mary.clark@hello.com	abc-123
6	22			hhh-000
7
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub lezawang()
' hiker95, 01/28/2017, ME988082
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim lr As Long, r As Long, n As Long, sn As Range
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
Set w2 = Sheets("Sheet2")   '<-- you can change the sheet name here
If Not Evaluate("ISREF(Sheet3!A1)") Then Worksheets.Add(After:=w2).Name = "Sheet3"
Set w3 = Sheets("Sheet3")
w3.UsedRange.Clear
w3.Range("A1").Resize(, 4).Value = Array("student no", "student name", "email address", "courses")
With w2
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  w3.Range("A2").Resize(lr - 1).Value = .Range("C2:C" & lr).Value
  w3.Range("D2").Resize(lr - 1).Value = .Range("D2:D" & lr).Value
End With
With w3
  For r = 2 To lr
    n = Application.CountIf(.Columns(1), .Cells(r, 1).Value)
    Set sn = w1.Columns(1).Find(.Cells(r, 1).Value, LookAt:=xlWhole)
    If Not sn Is Nothing Then
      .Cells(r, 2).Resize(, 2).Value = w1.Cells(sn.Row, 2).Resize(, 2).Value
    End If
    r = r + n - 1
  Next r
  .Columns(1).Resize(, 4).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the lezawang macro.

 

[azumi]

Did you try post #3?