Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Macro application (https://www.askmehelpdesk.com/showthread.php?t=399019)

  • Sep 23, 2009, 02:05 AM
    cool143
    Macro application
    Hi,

    I wanted to create an macro application.

    In 1st sheet, I have 5 columns and 1000 rows (may vary).
    In 2nd sheet, I have 5 columns and 1000 rows (may vary).

    Now I wanted to compare 1st sheet with 2nd sheet (Only one column)

    If cells in sheet1 is not equal to sheet2, copy that entire row to new sheet3.

    Please anyone help...
  • Sep 23, 2009, 08:41 AM
    JBeaucaire

    1) Is 1st sheet named Sheet1?
    2) Is 2nd sheet named Sheet2?
    3) Is 3rd sheet named Sheet3?

    4) You want to compare ALL 5 cells in row1 on sheet1 to ALL 5 cells in row1 of sheet2? Then row2 on sheet1 to row2 on sheet2?

    Or are the 5 cells on Sheet1 Row1 possibly found together somewhere other than row1 on sheet2? Meaning we need to check the entire dataset on sheet2?

    5) Are there headers at the top of your 5 cells?

    If you actually posted a sample sheet, this would be very easy to write. Click GO ADVANCED and use the paperclip icon to post up your workbook.
  • Sep 23, 2009, 08:09 PM
    cool143
    1 Attachment(s)


    Hi

    Please find the sample spread sheet.

    1. I wanted to compare column D (Sheet1) with Column A(sheet2). If the cell is not matching, I wanted to copy the entire row of Sheet1 to sheet3.
    2. When pasting the row in sheet3, I want some columns.
    For eg: From sheet1, Column D,A,C ( that I highlighted in attached sheet - Sheet3)

    Please help on this...
  • Sep 24, 2009, 12:45 AM
    JBeaucaire

    This little macro presumes column AA in sheet 1 is empty for a "filter" to be applied. It will put a formula in that column, then copy the rows that are "x", then remove the helper column.

    Hopefully this will be faster than looping through the rows one at a time and testing the 3 criteria that way.

    Give it a whirl:
    Code:

    Option Explicit

    Sub Differences()
    'JBeaucaire  (9/24/2009)
    Dim LR As Long
    Application.ScreenUpdating = False

    Sheets("Sheet1").Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("AA1") = "Key"
    Range("AA2:AA" & LR).FormulaR1C1 = _
        "=IF(ISNUMBER(MATCH(RC4&""-""&RC1&""-""&RC2&""-""&RC3, INDEX(Sheet2!R2C1:R100C1&""-""&Sheet2!R2C2:R100C2&""-""&Sheet2!R2C3:R100C3&""-""&Sheet2!R2C4:R100C4,0), 0)),"""",""x"")"

    Range("AA1").AutoFilter
    Range("AA1").AutoFilter Field:=1, Criteria1:="x"

    Sheets("Sheet3").Cells.Clear
    Range("D1:D" & LR).Copy Sheets("Sheet3").Range("A1")
    Range("A1:A" & LR).Copy Sheets("Sheet3").Range("B1")
    Range("C1:C" & LR).Copy Sheets("Sheet3").Range("C1")
    Range("AA1").AutoFilter
    Range("AA1:AA" & LR).ClearContents
    Sheets("Sheet3").Activate

    Application.ScreenUpdating = True
    End Sub

    How to use the macro:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet

    The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

  • All times are GMT -7. The time now is 04:58 AM.