Posts by sekar_r24

    Hi.,


    The five groups corresponds to the Column F and not I & J. If you filter Column F as per the image, you can get the groupings.


    I & J i had indicated the name of the laminates. These laminates combine together form a group. Like (Laminate 01 & Laminate 02 ) or ( Laminate 2 & Laminate 02), etc.,


    Hope it will be clear now..

    hi Carim.,


    Thanks!.


    Attached here with the excel file with the details.


    manually we are doing as filter, text filter of 21091 HGL , so it filters out the 21091 HGL- 21091 HGL & Offwhite - 21091 HGL Categories. Then it has been copied to next sheets.


    Like wise - 10858 SF


    the code will be varies for each project, hence it has to be accessed from the array of Column Value, which has choosen,

    Hi Carim,


    Thanks for the help. will tryout as advised.

    little bit brief.

    Concept: the panel will be pasted by laminates on both sides ( either same laminates or two different laminates).

    if both laminates (i.e color (21091 HGL -21091 HGL) (except off white - off white combination) are same or the one laminate is of color with combination of Off white( I.e 21091 HGL - Off white),both are grouped in the same new sheet.

    Like that each unique color ( 10858 SF - 10858 SF) & (10858 SF - OFF WHITE) has to be grouped in the new sheet.

    off white - off white combination alone grouped in another new sheet.


    Like this output i am looking out.


    Basically the unique color to be grouped together. (21091 - 21091 & 21091 - offwhite)


    for that i am trying to access the my array variable, convert to text to column, get the unique values, and autofilter based on the unique values and copy them to the sheet. is it correct way to do that?




    .

    hi.,


    I have column with multiple unique values (which will be dynamic, not standard, as the user might enter different values) like following.


    Offwhite - 21091 HGL

    Off White - Off White

    Offwhite - 10858 SF

    10858 SF - 10858 SF

    WPC.


    ex: finish code1 (off white) - finish code2 (21091 HGL)

    finish code2 (21091 HGL) - finish code2 (21091 HGL)

    finish code1 (off white) - finish code3 (10858 SF)

    finish code3 (10858 SF) - finish code3 (10858 SF)


    I would like to filter out as separate ( finishcode1 & finish code2, finish code2 & finish code)

    Offwhite - 21091 HGL

    Off White - Off White


    and the following as separately.


    Offwhite - 10858 SF

    10858 SF - 10858 SF


    and WPC as separately.




    The current code filters, copy the filtered value, creates new sheet and paste the values for the each value in the array of values.


    How to find solution for the above scenario.


    sample.xlsx

    Hi.,


    How to find the number of rows in specified column of data range. i can able to find the total number of rows from the starting of A1 to the end of the datarange. But my data range starts from b4: d12. It returns 12 and not as 9. how to solve?


    Code
    1. lRow = Cells.Find(What:="*", _
    2. After:=Range("A1"), _
    3. LookAt:=xlPart, _
    4. LookIn:=xlFormulas, _
    5. SearchOrder:=xlByRows, _
    6. SearchDirection:=xlPrevious, _
    7. MatchCase:=False).Row

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    01. I am having range of data, would like to find the value greater than in the column P (say 3) of the particular cell, and insert the row above or below (number (say 3) times - 1) (=3-1), and copy & paste the entire row, in the inserted rows and the change the value of the P column for the pasted values as 1. Also change the value of three into one.



    02. Also the values in the column Q has to be separated, with the delimiter comma (, ) and the same has to be pasted in the Q column of the inserted rows, with each unique value.


    Where the occurrence is more than, the above things has to be happened.


    Attached here with the sample data and expected output.


    Sample Data


    Could anyone help in this regard.


    thanks

    Files

    • sample01.xlsx

      (13.4 kB, downloaded 67 times, last: )

    Hi


    changing the value of ls to the row value work for the n rows . How to assign the variable 'ls' to get the number of rows from the starting cell F3 to the last row in with suitable datatype.

    Thanks jolivanes.

    Last thing, if there are 'n' number of rows means, how to incorporate in the code.

    i tried inserted the following code


    Code
    1. lr = Cells(Rows.count, 6).End(xlUp).Row 'counting the no of rows
    2. codeArr = Range("F3:I" & lr).Value ' assigning the no of rows.
    3. lengthArr = Range("J3:K" & lr).Value

    but seems it has to be tweaked! could you pls help