Sorting / organizing matched data

bravo1515

New Member
Joined
Mar 9, 2012
Messages
5
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Is this what you are trying to do?
Copy formula down as needed.


Excel Workbook
AB
1IDScore
210102058
310102134
410102269
5101023
6101024
710102538
810102683
910102774
1010102833
11101029
1210103090
13101031
1410103277
Sheet2
Excel Workbook
AB
1IDScore
210102058
310102134
410102269
510102538
610102683
710102774
810102833
910103090
1010103277
Sheet1
 
Upvote 0
If you're interested in a VBA solution try
Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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