Sort A Dataset as per Another Data Set

M

MA10KUMAR

New Member
Joined
Mar 2, 2008
Messages
8
Hello Excel Champs :),

I have a list of names in one sheet and another set of names (where some of the names in the first list may be missing). Now, I would like to Sort this second data set as per the first & complete set of names.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
If a particular name is not appearing in the second record set, a blank row should be created on its behalf.

For example,

If my first recordset is below:

Sam
David
Kyle
Anne

and my second recordset is below:

Anne
Kyle
Sam

The second column should change to

Sam
< Empty Row >
Kyle
Anne

Is it possible to do so (either by using Macros or not) :confused:

Thanks in advance for your helps....!!!;)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Try this:
Code: 
Sub Reconcile()
    Dim intX As Integer
    Dim intPartialListCount As Integer
    Dim intMatch As Integer
 
    Worksheets("Sheet1").Select
    intPartialListCount = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    For intX = 1 To intPartialListCount
        intMatch = WorksheetFunction.Match(Worksheets("Sheet2").Cells(intX, 1).Value, Worksheets("Sheet1").Columns(1), 0)
        If intMatch > 0 Then
            Cells(intMatch, 2) = Cells(intMatch, 1)
        End If
    Next
End Sub
 
Upvote 0
Hello Phil,

Thank you for your code.

However, I think I was not clear in explaining you my requirement. I want a Macro to Sort Column A in Sheet1 (and not filtering the matching items to Column B) as per Column A in Sheet2.

Thus,for example, if the name in A2 from Sheet2 exists in anywhere in Column A of Sheet1, it has to be brought up to A2 in Sheet1. If the name does not exist, the macro should insert a row.

Hope I am clear. If you have any questions, please let me know.

Once again, Thanks a lot for your coding... Hope you can solve this too !
 
Upvote 0
Which sheet has the full list of names, Sheet1 or Sheet2? Please show an example of how Sheet 1 column A & B and Sheet2 ColumnA&B looks before the desired code runs and and how they should look after the desired code runs. Please have at least 10 names on the sheet that holds the full list of names.
 
Upvote 0
Hello Phil,

Per my above example, Column A in Sheet2 has the complete name list and we have to sort Column A in Sheet1 (which may have just few names) according to Sheet2.

Before Desired Code:

COLUMN A in SHEET 2:

Sam (Manager)
Davidson Kempner (Supervisor)
Sam Abraham (Supervisor)
David A (Team Lead)
Andrew Savior (Team Lead)

David S - Senior Team Member
Kyle Norton - Senior Team Member
Anne Temple - Senior Team Member
Marione Oar - Team Member
George - Team Member
Andrew George - Team Member
George Andrew - Team Member

COLUMN A in SHEET 1:

Sam (Manager)
Davidson Kempner (Supervisor)
David A (Team Lead)
David S - Senior Team Member
Andrew Savior (Team Lead)
Anne Temple - Senior Team Member
Kyle Norton - Senior Team Member
Marione Oar - Team Member
George Andrew - Team Member

After Application of Code:

COLUMN A in SHEET 2:

Sam (Manager)
Davidson Kempner (Supervisor)
Sam Abraham (Supervisor)
David A (Team Lead)
Andrew Savior (Team Lead)

David S - Senior Team Member
Kyle Norton - Senior Team Member
Anne Temple - Senior Team Member
Marione Oar - Team Member
George - Team Member
Andrew George - Team Member
George Andrew - Team Member

COLUMN A in SHEET 1:

Sam (Manager)
< Insert Row & Enter "Davidson Kempner (Supervisor)" >
Sam Abraham (Supervisor)
David A (Team Lead)
Andrew Savior (Team Lead)
<< Insert Empty Row >>
David S - Senior Team Member
< Insert Row & Enter "Kyle Norton - Senior Team Member" >
Anne Temple - Senior Team Member
Marione Oar - Team Member
George - Team Member
< Insert Row & Enter "Andrew George - Team Member" >
George Andrew - Team Member

--------------------

:warning: Hope this information will be helpful to you.

Once again, thanks in advance for your efforts & time.

Have a pleasant day !
 
Upvote 0
try
Code: 
Sub test()
Dim a, i As Long, b()
With Sheets("Sheet2")
    a = Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a, 1), 1 To 1)
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1) : .item(a(i, 1)) = i : Next
    With Sheets("sheet1")
        a = .Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value
    End With
    For i = 1 To UBound(a, 1)
        If .exists(a(i, 1)) Then b(.item(a(i, 1)), 1) = a(i, 1)
    Next
End With
Sheets("sheet1").Range("a2").Resize(UBound(b, 1)).Value = b
End Sub
 
Upvote 0
Your comments in post 5 describe Column A of Sheet1 to look just like Column A Sheet2 once the code is run. I assumed you wanted to differentiate the names that were missing from Sheet1, Column A, so my code colors the missing names red and italicizes them.

Code: 
Sub Reconcile()
    Dim intX As Integer
    Dim intFullListCount As Integer
    Dim intMatch As Integer
 
    Worksheets("Sheet1").Columns("A:B").ClearFormats
    Worksheets("Sheet1").Columns("B").Clear
    Worksheets("Sheet1").Select
    intFullListCount = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    For intX = 1 To intFullListCount
        If Sheets("Sheet2").Cells(intX, 1) = "" Then
            Sheets("Sheet1").Cells(intX, 2).Value = ""
        Else
            On Error Resume Next
            intMatch = WorksheetFunction.Match(Worksheets("Sheet2").Cells(intX, 1).Value, Worksheets("Sheet1").Columns(1), 0)
            If Err.Number = 1004 Then 'Match not found, copy missing name and format
                Sheets("Sheet1").Cells(intX, 2).Value = Worksheets("Sheet2").Cells(intX, 1).Value
                Sheets("Sheet1").Cells(intX, 2).Font.ColorIndex = 3
                Sheets("Sheet1").Cells(intX, 2).Font.Italic = True
            Else 'Match found, copy name
                Sheets("Sheet1").Cells(intX, 2).Value = Worksheets("Sheet1").Cells(intMatch, 1).Value
            End If
            On Error GoTo 0
        End If
    Next
 
    Worksheets("Sheet1").Select
    Range("B1:B" & Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row).Select
    Selection.Cut
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub
 
Upvote 0
Seiya & Phil,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Fantastic code by both of you. Thanks a lot !!!<o:p></o:p>
<o:p></o:p>
However, the below requirements should also be met:<o:p></o:p>
<o:p></o:p>
1. If the names in Sheet1 start from 2nd Row and in Sheet2 if it is starting from Row 11, the macro should sort the data in Sheet1 from Row2 but it is sorting data in Sheet1 from Row11. This should not happen.<o:p></o:p>
<o:p></o:p>
2. In Sheet1, not only column A should be sorted, but till column Z the data set should be sorted as per Column A.<o:p></o:p>
<o:p></o:p>
Below is the basic layout of Sheet1:<o:p></o:p>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 3.9pt; BORDER-COLLAPSE: collapse; mso-table-layout-alt: fixed; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 border=0><TBODY><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 0"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 325.8pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt" vAlign=top width=434 colSpan=4>
Blackstone Team<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 325.8pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=434 colSpan=4>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:date Month="3" Day="31" Year="2008">March 31, 2008</st1:date><o:p></o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>Name & Title<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>
ID<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
Head 1<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
Head 2<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>Sam (Manager)<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>MFU102632<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>Davidson Kempner (Supervisor)<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>MFU102638<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>David A (Team Lead)<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>#DAVAXX<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>David S - Senior Team Member<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>MFU007801<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>Andrew Savior (Team Lead)<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>MFU102444<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>Anne Temple - Senior Team Member<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>MFU101858<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 10"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>Kyle Norton - Senior Team Member<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>MFU000001<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 11"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>Marione Oar - Team Member<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>MFU001658<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR><TR style="HEIGHT: 10.1pt; mso-yfti-irow: 12; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 150.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt" vAlign=top width=201>George Andrew - Team Member<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59.4pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=79>#GEOAND<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 52.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=70>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 63.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 10.1pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" vAlign=top width=84>
<o:p> </o:p>​
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
I will download names list (in Sheet2) from an application and therefore, I will not be sure where the names of our team starts and where it ends. Therefore, I need to select a particular range in Sheet2.<o:p></o:p>
<o:p></o:p>
Is it possible to ask the user to select a particular range in Sheet1 & Sheet2?
 
Upvote 0
try
Rich (BB code): 
Sub test()
Dim a, i As Long, ii As Long, b()
With Sheets("Sheet2")
    a = Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a, 1), 1 To 26)
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1) : .item(a(i, 1)) = i : Next
    With Sheets("sheet1")
        a = .Range("a11", .Range("a" & Rows.Count).End(xlUp))  & _
             .Resize(, UBound(b, 2)).Value
    End With
    For i = 1 To UBound(a, 1)
        If .exists(a(i, 1)) Then
            For ii = 1 To UBound(b, 2)
                b(.item(a(i, 1)), ii) = a(i, ii)
            Next
        End If
    Next
End With
Sheets("sheet1").Range("a11").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

Change 26, if you want to change the number of columns to be sorted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Automatically add & delete rows in Table B based on data in Table A
Replies
1
Views
516
Fluff
Fluff
S
  • Solved
Hyperlink formula to row after last used row in table
Replies
1
Views
396
sspatriots
S
R
  • Question Question
Search a range for a specific color of rows, relocate those cells to another sheet, return previous sheet formatting to blank and sort secondary sheet
Replies
4
Views
347
Royalbloodi
R
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
340
STARL0RD
S
X
  • Question Question
VBA for extracting specific data from PDF Docs
Replies
3
Views
606
xcellrodeo
X

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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