Posts by ivandgreat

    Re: Find the 2 column values and search in reverse in same 2 columns


    hi skywiter,


    as per my attached file and shknbk2's code, after i run the code, please filter @ colum TerminalB.


    Here it shows no Duplicate @ row20 but it shows [email protected] @ row 479, same with the other one.


    The duplicate should not show from Col NameA & TerminalA to Col NameB & TerminalB.


    [TABLE="width: 421"]

    [tr]


    [td]

    Item

    [/td]


    [td]

    NameA

    [/td]


    [td]

    TerminalA

    [/td]


    [td]

    NameB

    [/td]


    [td]

    TerminalB

    [/td]


    [td]

    Remarks

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]20[/TD]

    [td]

    2002

    [/td]


    [td]

    D7AB

    [/td]


    [td]

    2191

    [/td]


    [td]

    D7AA

    [/td]


    [td]

    no Duplicate

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]479[/TD]

    [td]

    2191

    [/td]


    [td]

    D7AA

    [/td]


    [td]

    2002

    [/td]


    [td]

    D7AB

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]1023[/TD]

    [td]

    2326

    [/td]


    [td]

    DLOB

    [/td]


    [td]

    4178

    [/td]


    [td]

    DLOA

    [/td]


    [td]

    no Duplicate

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]1027[/TD]

    [td]

    4178

    [/td]


    [td]

    DLOA

    [/td]


    [td]

    2326

    [/td]


    [td]

    DLOB

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [/TABLE]


    br,

    Re: Find the 2 column values and search in reverse in same 2 columns


    Thanks a lot shknbk2.


    I tested it on some other data, it gives an error... some was found "no duplicates" but when i check it has duplicates, and the remarks has found the duplicate.
    Please see below table results.


    [TABLE="width: 421"]

    [tr]


    [td]

    Item

    [/td]


    [td]

    NameA

    [/td]


    [td]

    TerminalA

    [/td]


    [td]

    NameB

    [/td]


    [td]

    TerminalB

    [/td]


    [td]

    Remarks

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]1[/TD]

    [td]

    3000

    [/td]


    [td]

    W07B

    [/td]


    [td]

    4884

    [/td]


    [td]

    W07A

    [/td]


    [td]

    no Duplicate

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]159[/TD]

    [td]

    4884

    [/td]


    [td]

    W07A

    [/td]


    [td]

    3000

    [/td]


    [td]

    W07B

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]79[/TD]

    [td]

    4841

    [/td]


    [td]

    B20B

    [/td]


    [td]

    4840

    [/td]


    [td]

    B20A

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]70[/TD]

    [td]

    4840

    [/td]


    [td]

    B20A

    [/td]


    [td]

    4841

    [/td]


    [td]

    B20B

    [/td]


    [td]

    no Duplicate

    [/td]


    [/tr]


    [/TABLE]


    What if i wanted to transfer the whole rows for duplicates to another sheet and remain the unique rows only.


    br,

    Hello,


    Can anyone help me how to to check in vba the values for two columns (NameA and NameB) and check it on reverse columns (NameB and NameA) and highlights the duplicates if found


    [TABLE="width: 200"]

    [tr]


    [td]

    Item

    [/td]


    [td]

    NameA

    [/td]


    [td]

    TerminalA

    [/td]


    [td]

    NameB

    [/td]


    [td]

    TerminalB

    [/td]


    [td]

    Remarks

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    2000

    [/td]


    [td]

    XXX1

    [/td]


    [td]

    2001

    [/td]


    [td]

    XXX2

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    2002

    [/td]


    [td]

    YYYA

    [/td]


    [td]

    2003

    [/td]


    [td]

    YYYB

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    XYZ22

    [/td]


    [td]

    ABCA

    [/td]


    [td]

    XYZ11

    [/td]


    [td]

    ABCB

    [/td]


    [td]

    no Duplicate

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    2001

    [/td]


    [td]

    XXX2

    [/td]


    [td]

    2000

    [/td]


    [td]

    XXX1

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    2003

    [/td]


    [td]

    YYYB

    [/td]


    [td]

    2002

    [/td]


    [td]

    YYYA

    [/td]


    [td]

    [email protected]

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    2000

    [/td]


    [td]

    ZZZA

    [/td]


    [td]

    2002

    [/td]


    [td]

    ZZZB

    [/td]


    [td]

    no Duplicate

    [/td]


    [/tr]


    [/TABLE]


    br,


    Ivan

    Dears,


    Good day! I would like to make a summary of my table using excel vba.


    From attached file, i have a number of rows which specify the name/number of rooms in col B and at col C mentioned number of occupied rooms.


    If the value in col C, for example show as "Occupied 2" it will take the first 2 rows and make the summary table which is shown on col E to J. if it is "Occupied 8" it will occupy the 8 rows.. and so on.


    if the next rows (after occupying the number of rows mentions) is vacant, it will count the number of vacant until it reach the row with values again.


    This will have a loop until the last rows in col B and create the summary table.


    Thanks in advance.


    br,
    Ivan

    Dears,


    Good day! I want to have a vba that will summarize my table with duplication.


    Attached here is my table.


    It should start to check the data in col A , if it has duplicate or not.


    If found duplicate it will compare first the value in col I (status) then check if any different values in col C to H.
    - If all having the same value in col I then check duplicate in col C to H, if all the same update col J, leave blank the 1st row, then put a remark value on the 2nd row "This is duplicate data"
    - Update col K (Merge), concatenate value in col B, C and I, and add rank number. Leave blank if it is totally the same value on each col I, C to H
    (Please see example row 2 and 3 from example table)


    If no duplicate found, update col I, count the value in col I (Remarks) and update col K (Merge), concatenate value in col B, C and I, and add rank number.
    (Please see example row 8 and 14 from example table)


    Any value found difference after checking/counting the value in col I (Status),
    - If all having the same value in col I then check duplicate in col C to H, Update the col J (Remarks) with only the col that's having different value
    - Update the col K (Merge), concatenate value in col B, C and I, and add rank number.


    Please check sheet 2 for the output in col J (Remarks) and K (Merge)


    br,


    Ivan

    Re: Vlookup Sample


    Quote from gn00588950;713807

    Try not to Vlookup whole Range("A:E") if you really need to perform this action by Marco.


    And change:


    Code
    1. For Each cell In Sheets("Sheet1").Range("B:B")
    2. Cells(cell.Row, "G").Formula = "=Vlookup(" & Cell.Address(False, False) & "," & Sheets("Sheet2").Range("A2", Cells(5, Your Last Used Row Of Column E).Address(False, False) & ",5,False)"
    3. Next cell


    Thanks for your help, gn00588950, But it didn't work. what if the last row is variable?