Posts by ivandgreat

  • Summarized Duplication - 15USD

    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

  • Vlookup Sample

    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
    For Each cell In Sheets("Sheet1").Range("B:B") 
        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)"
Next cell


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

  • Vlookup Sample

    Re: Vlookup Sample


    Thanks for your reply.


    But doing this, for 100Thou rows will take some time in processing that's why I wanted to make it in macro.

  • Vlookup Sample

    Dears,


    I have this code, it is to much slow. How can i make it a faster?


    What should i edit to check only the 2nd to the last end row of column B (1st row is a title row).


    Code
    Sub VlookupValue()
Dim cell as range
For each cell in Sheets("Sheet1").Range("B:B")
    Cells(cell.Row, "G") = Application.Vlookup(cell,Sheets("Sheet2").Range("A:E"),5,FALSE)
Next cell
End Sub


    br,


    ivan

  • 30USD - VBA Create/add rows based on criteria

    Dears,



    Good day! I would like to create a table based on the Network Address on Cell B3 and C3.



    On the sample, item 1, based on cell value on C3 which is /29, only get the 29 and check it on sheet3.



    on sheet3, you'll find the value 29 on col C and @ col D which will tell how many rows will be added on the last row of table @ Sheet2.



    From there you'll see a value of 8 which will be the start and end of the row to be added.



    The first value to be added @ col B will be same as the network address on Cell B3 and C3 at sheet 1 which is "10.131.100.248",
    since the value 29 having a value of "4th" @ col B @ sheet3, it will increment the value by 1 (but will not exceed the value to 255)
    @ 4th value after 3 dot which is the value 248



    then increment by 1 and will not be exceed by 255 until it get the number of rows needed, so it will be



    10.131.100.249
    10.131.100.250
    10.131.100.251
    ...
    ...
    ...
    10.131.100.255



    The first will have a value of "Network" and the last will be "Broadcast" @ col D @ sheet 2.





    on Item 2, it will be the same, and you'll notice it has a value of 23 @ cell C4,



    The no of rows to be added will be 512 based on Sheet 3.



    Then the start value will be based on "3rd" from this value "10.131.102.0", which is 102.



    Then increment by 1, so it will be,



    10.131.102.0
    10.131.102.1
    10.131.102.2
    ...
    ...
    ...
    10.131.102.255 (since the value should not exceed 255, it should go and increment the 3rd value now by 1 and continue increment by 1 again on the 4th value),
    here the number of rows created is 256 row and still need to continue until 512 since it has a value of 512 @ Sheet 3 col D based on the /23.



    to continue..





    10.131.103.0
    10.131.103.1
    10.131.103.2
    ...
    ...
    ...
    10.131.103.255



    The first will have a value of "Network" and the last will be "Broadcast" @ col D @ sheet 2.





    br,



    ivan

  • Separate Unique Values - $20

    Re: Separate Unique Values - $20


    Thanks smc, could you please put a remarks on duplicated values on the adjacent column.


    Count it how many it times exist before deletion.
    If it has 2 put 2, if it is 4 put 4.