Pulling data from multiple sheets

[missionastar]

Hello all. I'm new to this so this may be a very basic question. I'm putting together College information, so i have multiple spreadsheets, like this

Institution	ED Acceptance Rate	RD Acceptance Rate (2)	Maximum Percent of Class Filled from ED (1)
Bates College	46%	10%	81%
Dartmouth College	26%	7%	51%
Claremont McKenna College	35%	10%	71%
Northwestern University	25%	7%	58%
Carleton College	60%	19%	51%

ANd then another sheet like this

	Cost	UG	Int	%Int
Cornell University	$76,258	14,976	1,553	10%
Dartmouth College	$77,152	4,365	428	10%
Harvard University	$73,800	6,760	814	12%
Northwestern University	$78,654	8,186	841	10%
Princeton University	$74,150	5,328	638	12%
Stanford University	$74,570	6,911	747	11%

And I want to combine the sheets with a master sheet with all the data about each university
I'm guessing it's quite easy - how do I accomplish this?
Thanks

 

[mumps]

It would be helpful if you could post a manually created master to show the end result. I noticed that not all the institutions in the first sheet exist in the second sheet. Also, are all the sheets in the same workbook? Please clarify in detail.

 

[missionastar]

Thanks @mumps - so there are 2 lists with scraped data. There will be some issues with the institution lists as they are different (one has about 400 universities, the other about 300). I've done my best to preserve names across both but there will be some data cleaning to do later where I've got the same institution with slightly different names (St. Mary's vs Saint Marys etc)
The end goal would be to have something like this, with the Columns from Sheet 1 and the Columns from Sheet 2 on the same Master sheet

ED Acceptance Rate	RD Acceptance Rate (2)	Maximum Percent of Class Filled from ED (1)		UG	Int	%Int
Bates College	46%​	10%​	81%​	$76,258	14,976​	1,553​	10%​
Dartmouth College	26%​	7%​	51%​	$77,152	4,365​	428​	10%​
Claremont McKenna College	35%​	10%​	71%​	$73,800	6,760​	814​	12%​
Northwestern University	25%​	7%​	58%​	$78,654	8,186​	841​	10%​
Carleton College	60%​	19%​	51%​	$74,150	5,328​	638​	12%​

 

[mumps]

I'm a little confused. In the example you posted in Post #3, the cost for Claremont McKenna College is $73,800. However, this cost is associated with Harvard University in the data you posted in Post #1. The problem is the same for Bates College and Cornell University, Princeton University and Carleton College. Do you not want the data in the two sheets to match? Also, in your latest post, Stanford University and Princeton University are missing.

 

[missionastar]

Hi @mumps , thank you for your help!

To clarify - the information in the first post contained the first few data points from 2 much longer lists with several hundred universities. Although the information in each list is similar, the lists do not contain all the same institutions (places that don't offer ED, for example, are not present in the first list).

I just copied some random data into the second post to show what the final table should look like in principle - and yes, this meant copying and pasting the cost info associated with the wrong colleges, which caused the confusion - I also messed up the headings, which doesn't help - it was just to give an idea of the form of the end result .

Essentially I want a master sheet with all universities in the first column, all the ED information from the first spreadsheet in the next 5 columns (if available), and the cost information form the second spreadsheet in the subsequent columns.

 

[mumps]

Try this macro. Make sure that your workbook has three sheets. Change the sheet names (in red) to suit your needs.

Sub PullData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS1 As Worksheet, srcWS2 As Worksheet, desWS As Worksheet, i As Long, v1 As Variant, v2 As Variant, dic As Object
    Set srcWS1 = Sheets("Sheet1")
    Set srcWS2 = Sheets("Sheet2")
    Set desWS = Sheets("Sheet3")
    With desWS
        .Range("A1").Resize(, 8).Value = Array("Institution", "ED Acceptance Rate", "RD Acceptance Rate (2)", "Maximum Percent of Class Filled from ED (1)", "UG", "Int", "%Int")
        srcWS1.UsedRange.Offset(1).Copy .Range("A2")
    End With
    v1 = srcWS1.Range("A2", srcWS1.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = srcWS2.Range("A2", srcWS2.Range("A" & Rows.Count).End(xlUp)).Resize(, 5).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(v1, 1)
        dic.Add v1(i, 1), i + 1
    Next i
    For i = 1 To UBound(v2, 1)
        If dic.Exists(v2(i, 1)) Then
            desWS.Range("E" & dic(v2(i, 1))).Resize(, 4) = Array(v2(i, 2), v2(i, 3), v2(i, 4), v2(i, 5))
        Else
            With desWS
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = v2(i, 1)
                LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                .Range("E" & LastRow).Resize(, 4) = Array(v2(i, 2), v2(i, 3), v2(i, 4), v2(i, 5))
            End With
        End If
    Next i
    desWS.Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

 

[missionastar]

Wow! This looks amazing! Thank you @mumps so much for taking the time to put this together. I look forward to putting it into practice and will report back with progress. I am hugely grateful for your efforts.

 

[mumps]

You are very welcome.