Results 1 to 5 of 5

Thread: Excel Matching

  1. #1
    New Lounger
    Join Date
    Nov 2009
    Location
    Naperville, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have Excel 2000 and I need to match the numbers in column B with the numbers in Column A. Once a match is found, then move the cells in columns B & C down to the matched row.

    E.g.: In the attached spreadsheet, cell B1 matches cell A5. I need to insert cells B1:C4 down to row B5.

    I would do this manually, but I have hundreds of matches like this to make.

    Is there a formula to do this or do I need a macro?

    Thanks in advance for the help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this macro. Make a backup copy of your workbook first!

    Code:
    Sub MatchAndShift()
     Dim r1 As Long
     Dim r2 As Long
     Dim c As Range
     r2 = 1
     Do While Not Cells(r2, 2) = ""
      Set c = Range("A:A").Find(What:=Cells(r2, 2), LookAt:=xlWhole)
      If Not c Is Nothing Then
       r1 = c.Row
       If r1 > r2 Then
    	Range(Cells(r2, 2), Cells(r1 - 1, 3)).Insert Shift:=xlShiftDown
    	r2 = r1
       End If
      End If
      r2 = r2 + 1
     Loop
    End Sub

  3. #3
    New Lounger
    Join Date
    Nov 2009
    Location
    Naperville, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HansV;

    Thank you very much. You helped me with this issue several years ago.

    The macro worked great, but I have expanded the range. I need to shift the values in columns B:H down to match the cell in Column A that corresponds to the cell in column B. And only those columns because I have data in other columns after H (I – V) that I need to hold to the original value in row A.

    I couldn’t figure out how to manipulate your macro to do this myself, so I am asking you again for help.

    A new sample sheet is attached.

    Thanks in advance for your help.

    Dave
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It's not very difficult. The line that shifts cells down is currently

    Range(Cells(r2, 2), Cells(r1 - 1, 3)).Insert Shift:=xlShiftDown

    Cells(r2, 2) is in column 2 (i.e. B) and Cells(r1 - 1, 3) is in column 3 (i.e. C). So the above line shifts cells in a range from column B to column C.

    You now want to shift cells in a range from column B to column H instead of C. Column H is the 8th column. So you only need to change the 3 to 8:

    Range(Cells(r2, 2), Cells(r1 - 1, 8)).Insert Shift:=xlShiftDown

    The rest of the macro can remain unchanged.

  5. #5
    New Lounger
    Join Date
    Nov 2009
    Location
    Naperville, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Worked great Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •