Posts by ivandgreat

    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.

    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
    1. Sub VlookupValue()
    2. Dim cell as range
    3. For each cell in Sheets("Sheet1").Range("B:B")
    4. Cells(cell.Row, "G") = Application.Vlookup(cell,Sheets("Sheet2").Range("A:E"),5,FALSE)
    5. Next cell
    6. End Sub


    br,


    ivan

    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

    Files

    • IPs.xlsx

      (26.24 kB, downloaded 1 times, last: )

    Good day!


    Please help, i wanted to have a macro that will rename the IDs @ col B that is unique and duplicate.


    The rename will be add if it is 1st, 2nd, 3rd and so on...


    Please see sample file.


    Thanks a lot..


    br,


    ivan

    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.

    Good day!



    Need someone to have a vba that will consolidate my table.



    I have a table column for Site_A(D & E) and Site_B(F & G) which concatenated in column J,



    I wanted to separate the rows from other sheet that have duplicate data, here in column J row 1 and 2,



    that the text value "ER_XMK2437/HDMA - ER_XMK2488_TN4/HDMB" is actually the same as "ER_XMK2488_TN4/HDMB - ER_XMK2437/HDMA"



    it is only name in reverse.



    The output is in sheet2.



    br,



    ivan

    Files