Sorting / organizing matched data

[bravo1515]

I'm not even sure where to begin on this one. Lets say I have 2 columns of matched numerical data (A1 and B1) in tab1 (ID number and score). In tab2 I have a column with all of the data from A1 (A2) plus approximately 10% more rows of data that are not in A1 from the original tab. I also have a blank B2 column in tab 2 that I'm looking to populate without going through and imputing each value manually. I am looking for a method to get all the matched data from B1 to B2 so it corresponds like A1 and B1 columns. Additionally, A2 is sorted and grouped in such a way that I can't simply sort largest to smallest and copy paste. The additional rows in A2 that do not have a corresponding B value would just be left blank.

Any help would be greatly appreciated.

Thanks

 

[AhoyNC]

Is this what you are trying to do?
Copy formula down as needed.

Excel Workbook
	A	B
1	ID	Score
2	101020	58
3	101021	34
4	101022	69
5	101023
6	101024
7	101025	38
8	101026	83
9	101027	74
10	101028	33
11	101029
12	101030	90
13	101031
14	101032	77
Sheet2

Excel Workbook
	A	B
1	ID	Score
2	101020	58
3	101021	34
4	101022	69
5	101025	38
6	101026	83
7	101027	74
8	101028	33
9	101030	90
10	101032	77
Sheet1

 

[bravo1515]

I'm not sure where the error is but it is not returning any values at all.


 

[Fluff]

If you're interested in a VBA solution try

Sub MatchData()

   Dim Cl As Range
   Dim Sht1 As Worksheet
   Dim Sht2 As Worksheet
   
   Set Sht1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set Sht2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Sht1.Range("A2", Sht1.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Sht2.Range("A2", Sht2.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

Changing the sheet names in red to suit

 

[AhoyNC]

To see what the error is drop the IFERROR part of my formula above.
So for the example above.
=INDEX(Sheet1!$B$2:$B$10,MATCH(A2,Sheet1!$A$2:$A$10,0))
Also change ranges to match your actual data.

 

[bravo1515]

Thanks for the response. I'm not sure how that highlights the error. Now I get the #N/A error down the entire column. I have changed the range to 12000 instead of 10 as that's how many lines I have.

 

[AhoyNC]

The #N/A tells us that the MATCH function is not finding a match.
-Could be that in one sheet the ID's are numeric and are text in the other sheet.
-There might be either leading or trailing spaces in one set of data.

Find a value on sheet 1 that should match a value on sheet 2.
Say A2 should match cell A9 on sheet1

Enter A2=sheet1!A9 in a cell. If it comes back FALSE it means they are different for some reason.

 

[bravo1515]

Bingo! Thanks for the help.