Results 1 to 10 of 10

Thread: How to populate all the rows in one column based on one cell value

  1. #1
    Junior Member
    Join Date
    Jan 2017
    Posts
    2
    Rep Power
    0

    Post How to populate all the rows in one column based on one cell value

    I have a data of two columns. I first column, there is a name of report and in second column, there is a status of that report. For Example, I have 10 rows of data from A1 to B10 as below:

    Column A Column B
    ABC Signed off
    DEF Signed off
    GHI Issue
    JKL Signed off
    MNO Issue
    PQR Issue
    STU Signed off
    VWX Issue
    YZA Issue
    BCD Signed off


    In cell D1, I have a value (either Signed off or Issue). Based on the value in D1, I want all the values from column A corresponding to that value in D1 to be listed in column E.

    question.JPG



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
    https://eileenslounge.com/viewtopic.php?p=316705#p316705
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316280#p316280
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315744#p315744
    https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315680#p315680
    https://eileenslounge.com/viewtopic.php?p=315743#p315743
    https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
    https://eileenslounge.com/viewtopic.php?p=314950#p314950
    https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
    https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
    https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 04-24-2024 at 07:38 PM.

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,429
    Rep Power
    10
    Hi jmishra,
    _1 ) Welcome to the Forum
    _2 ) I think you may have your columns D and E mixed up in your explanation and sample File ?

    _3) To answer your Question:

    I did not know how to do this so i searched the internet ( I Googled )
    I found this article https://newtonexcelbach.wordpress.co...ray-functions/

    I was able to adapt the solutions given there to get what I think may be close to what you want. Here is the results.

    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Will Return the match in column A to B, or False Type here Signed off or Issue Will return the row number where the match is found or False Returns the next smallest number from Array F2:F11 Returns value from column A based on row number Remove error
    2
    a Signed off a Signed off
    1
    1
    a a
    3
    b Issue
    FALSCH
    FALSCH
    3
    c c
    4
    c Signed off c
    3
    4
    d d
    5
    d Signed off d
    4
    7
    g g
    6
    e Issue
    FALSCH
    FALSCH
    9
    i i
    7
    f Issue
    FALSCH
    FALSCH
    #ZAHL!
    #ZAHL!
    8
    g Signed off g
    7
    #ZAHL!
    #ZAHL!
    9
    h Issue
    FALSCH
    FALSCH
    #ZAHL!
    #ZAHL!
    10
    i Signed off i
    9
    #ZAHL!
    #ZAHL!
    11
    j Issue
    FALSCH
    FALSCH
    #ZAHL!
    #ZAHL!
    Worksheet: Sheet1
    _............

    Here are the Formulas
    They are of the CSE type 2 Form, so will need to be Array Enterted.
    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Will Return the match in column A to B, or False Type here Signed off or Issue Will return the row number where the match is found or False Returns the next smallest number from Array F2:F11 Returns value from column A based on row number Remove error
    2
    a Signed off =IF(B2:B11=E2,A2:A11,FALSE) Signed off
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1) =IF(ISERROR(H2:H11),"",H2:H11)
    3
    b Issue
    =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1) =IF(ISERROR(H2:H11),"",H2:H11)
    4
    c Signed off =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1) =IF(ISERROR(H2:H11),"",H2:H11)
    5
    d Signed off =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1) =IF(ISERROR(H2:H11),"",H2:H11)
    6
    e Issue
    =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1) =IF(ISERROR(H2:H11),"",H2:H11)
    7
    f Issue
    =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1)
    =IF(ISERROR(H2:H11),"",H2:H11)
    8
    g Signed off =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1)
    =IF(ISERROR(H2:H11),"",H2:H11)
    9
    h Issue
    =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1)
    =IF(ISERROR(H2:H11),"",H2:H11)
    10
    i Signed off =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1)
    =IF(ISERROR(H2:H11),"",H2:H11)
    11
    j Issue
    =IF(B2:B11=E2,A2:A11,FALSE)
    =IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)
    =SMALL(F2:F11,ROW(F1:F10))
    =INDEX(A2:A11,G2:G11,1)
    =IF(ISERROR(H2:H11),"",H2:H11)
    Worksheet: Sheet1


    If you need more help in adapting to your exact requirement or explanations to the solution then let me know.
    Please let us know how you get on.

    Alan
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,429
    Rep Power
    10

    CSE Ctrl+Shift+Enter Controlled Shifting the Entering of a Formula

    CSE Ctrl+Shift+Enter Controlled Shifting the Entering of a Formula


    So I thought I would explain it for my own amusement in my own way anyway

    The key to understanding these sort of solutions is in understanding how you can get Excel to evaluate a formula more than once using different values from a range or Array
    As far as I know Excel has no Array formulas and does not support Array mathematics.. But it allows you to evaluate formulas in an Array type way. That is to say you can give to most formulas an Array or range of values in place of where you normally give a single value. You can then get that formula to give you as many answers as you have values in the given range. The formula is simply done as many times as you have values in the Array or range using each of the given value sequentially. The answers will be “held” by Excel in an Array of similar dimensions and orientation to those of the range you gave the formula.
    (If your formula has more than one range or Array in it, then Excel will “hold” the answers in an Array equal to the largest dimensions of any Arrays or ranges in the complete formula


    CSE Controlled Shifting of a formula before Entering it
    The process is made unnecessarily complicated IMO by using a weird “CSE” way of entering the formula. But after a while it makes some sense, or you just get used to it. I expect deep down in the workings of Excel for a simple formula there is a lot of complex calculation based on, or using, values in memory held in a position with a specific offset which is related to what one “sees” as a cell position. That is to say where the formula is plays a significant role in the complex calculations. Hitting Enter sets off the calculations in the simple formula case, ( as well as going through the spreadsheet and recalculating all formulas ).
    For some strange reason Excel does not recognise when you include a range or Array instead of a single value. If you write in a formula including a range instead of a single value and just hit Enter then sometimes the formula will work based on an evaluation with the first value in the range or Array. Sometimes it will error. I expect the error may be due to some mixing up of offsets in memory locations caused by actually having the formula in the cell. I believe the process that prevents such an error is to somehow remove the formula from the cell then sequentially put it back in with the next single value from the range or Array given, get the answer, put that in the Array it holds the output in, then somehow remove the formula from the cell then sequentially put it back in with the next single value from the range or Array given, get the answer, put that in the Array it holds the output in, then somehow remove the formula from the cell then sequentially put it back in with the next single value from the range or Array given, get the answer, put that in the Array it holds the output in, then somehow remove the formula from the cell then sequentially put it back in with the next single value from the range or Array given, get the answer, put that in the Array it holds the output in, then ...etc.....
    I suppose you could call that process some sort of Controlled Shifting initially of the formula to get the value offset from some origin point in the given Array or range and then Entering it...


    So that is what you do to enter such a formula and get a result without error:
    Type the formula in then Hit these three keys, Ctrl+Shift+Enter simultaneously.
    That is known as type 1 CSE Formulas. ( Sometimes the word Array is thrown in to confuse and it is called type 1 CSE Array Formulas, or Array Formula Entry etc. These last terms are not really correct. )
    After doing the CSE Entry, you will still only get the first answer out. All the answers will not fit in one cell. Sometimes that may be all you want

    If you want to get all the answers out then you follow a similar logic:
    You need to set aside some range in the spreadsheet to use for the answer. Once again you need to remove the connection of the range to be used to the normal spreadsheet intercept calculation workings. This process is often called something along the lines of CSE type 2 formula Entry. It is almost the same as in CSE type 1.
    You do it like this:
    _ Select the spreadsheet cell area that you want the results in. ( It needs to be the size appropriate to hold your expected evaluated answers )
    SelectOutputArea.JPG http://imgur.com/jq5VRGI
    SelectOutputArea.JPG

    _ Hit F2 or click in the formula Bar
    F2FormulaBar.JPG http://imgur.com/78EXwRu
    F2FormulaBar.JPG

    _ Type in ( or paste in ) your formula, for example =B2:B3*2 http://imgur.com/Yo3y5Oi
    _ TypeInFormula.JPG
    TypeInFormula.JPG

    _ Do the CSE type 2 formula entry
    ( Hit simultaneously the three keys Ctrl+Shift+Enter )


    _ You should see the results of the formula done twice, presented in the “vertical” orientation:
    =B2x2=1x2=2
    =B3x2=2X2=4
    Type2CSE.JPG http://imgur.com/DOh0C7s
    Type2CSE.JPG

    _ Alternatively you can do an immediate evaluation of the formula , or part thereof by using F9
    _a) For Part of Formula
    _ Select ( highlight ) the part of the formula you wish to evaluate
    SelectFormulaPart.JPG http://imgur.com/2iTKEC9
    SelectFormulaPart.JPG

    _ You should see the Array of values from the range B2:B3 shown thus
    {1;2}
    F9PartFormula.JPG http://imgur.com/3MoEfXe
    Remember to Hit Esc or Ctrl+z to return to formula view


    _b) For Full Formula
    _ Select ( highlight ) all of the formula
    SelectFullFormula.JPG http://imgur.com/UiUAGQw

    _ You should see the final results of the formula
    {2;4}
    F9FullFormula.JPG http://imgur.com/7ya9FsE
    Remember to Hit Esc or Ctrl+z to return to formula view

    Summary: To get a formula to evaluate a range or Array of values and return the corresponding multiple answers

    _ The formula is written as in the single values case, but with an Array or range given in place of where a single value is normally given
    _ The appropriate CSE type entry is made of the formula.

    Having done this , Excel takes each value, evaluates the formula as if it had that single value in it, and returns the value in an Array of the same dimensions as the largest dimensions of any given Array or range. The position in the output Array reflects the position in the given Array of that value.

    For the case of the example in this Thread see next post.


    ( Some light hearted notes on CSE stuff:
    https://app.box.com/s/avk6paydbtame1hz7ge5zenh6ll1p35e
    )
    Last edited by DocAElstein; 01-25-2017 at 11:36 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,429
    Rep Power
    10
    Explaining the answer in Post #2


    Take the formula in B2
    =IF(B2:B11=E2,A2:A11,FALSE)
    For the normal formula case this would look like
    =IF(B2=E2,A2,FALSE)
    So IF B2 = E2 , the value from A2 is returned which is "a". Otherwise FALSE is returned
    Because the formula, {IF(B2:B11=E2,A2:A11,FALSE)}, has been entered in the CSE way, (type 2 way for output over the range D2:D11 ), Excel does the formula 10 times. Each time it uses the next value from the range B2:B11 and also the corresponding value from A2:A11 or FALSE as the returned output. Note the values from the same position in each of the ranges, D2:D11 and A2:A11 are used in the calculation. So the formulas evaluated by Excel are:
    IF(B2=E2,A2,FALSE) = a
    IF(B3=E2,A3,FALSE) = FALSE
    IF(B4=E2,A4,FALSE) = c
    etc...

    Consider the formula in F2
    We are using the Row( ) Function. This returns an Array of integer numbers corresponding to the row numbers of a range given.
    So ROW(A1) returns just
    1
    ROW(A2:A11) returns
    2
    3
    4
    5
    or {2;3;4;.....}
    etc...
    Because the formula { IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)}, has been entered in the CSE way, (type 2 way for output over the range F2:F11 ), Excel does the formula evaluation 10 times. Each time it uses the next value from the range B2:B11 and also the corresponding value from the Array given by ROW(A2:A11)
    So the formulas evaluated by Excel are:
    IF(B2=E2,2-1,FALSE) = 1
    IF(B3=E3,3-1,FALSE) = FALSE
    IF(B4=E4,2-1,FALSE) = 3
    etc....

    Consider the formula in G2
    We are using the SMALL Function. This has two arguments
    SMALL({Array}, kth)
    The first argument is an Array. Note: This Array is specific for use in this function. This full Array is used regardless of if the function appears in a normal formula or one that has been given Arrays or ranges where single values are used in normal formulas. If Excel does any evaluations in CSE entered formulas, it still uses the full {Array}each time. Usually K is a single value such as 1, 2 or 3 etc...
    The SMALL Function works as follows: The kth next smallest value in the {Array} is returned.
    For example
    SMALL({7;3;10}, 2) = 7 ' 7 is the second smallest number
    SMALL({7;3;10}, 1) = 3 ' 3 is the first smallest number. In other words the smallest number
    SMALL({7;3;10}, 4) = __ 'This will error as there is no 4th smallest number
    Usually in normal formula usage k is a single number. But we have given it an Array. This is the Array returned by ROW(F1:F10) which is
    1
    2
    3
    or {1;2;3...}
    etc..
    Because the formula { SMALL(F2:F11,ROW(F1:F10))} has been entered in the CSE way, (type 2 way for output over the range G2:G11), Excel does the formula evaluation 10 times. Each time it uses the next value from the Array given by ROW(F1:F10), but considers each time the entire Array of the range F2:F11.
    So the formulas evaluated by Excel are
    SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 1) = 1
    SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 2) = 3
    SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 3) = 4
    SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 4) = 7
    SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 5) = 9
    SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 6) = ' This will error
    etc.

    Consider the formula in H2
    We are using the INDEX Function. , =INDEX( {Array}, row, column ). This returns us the value in its first argument {Array} given at the intersection of its second argument, row, coordinate, and its third argument, column, coordinate.
    ( Note: row and column are that in the Array {Array}, not the row and column number in the worksheet).
    Note also: Array {Array} is specific for use in this function. This full Array is used regardless of if the function appears in a normal formula or one that has been given Arrays or ranges where single values are used in normal formulas. If Excel does any evaluations in CSE entered formulas, it still uses the full {Array}each time.
    {Array} is chosen to be A2:A11 which is
    a
    b
    c
    d
    etc..
    or {a;b;c;d;e;f;g;.....}
    {Array} is in this case a 1 column multi row Array, so column is always = 1
    Normally row would be a single value. But we are using for our CSE type 2 entered formula an Array given by the range G2:G11 which is
    1
    2
    4
    7
    9
    Error
    etc..
    or {1;3;4;7;9;error;error......}
    Because the formula { INDEX(A2:A11,G2:G11,1)} has been entered in the CSE way, (type 2 way for output over the range H2:H11), Excel does the formula 10 times. Each time it uses the next value for row from the Array given by G2:G11, but considers each time the entire Array of the range A2:A11.
    So the formulas evaluated by Excel are
    INDEX({a;b;c;d;e;f;g;.....},1,1) = a
    INDEX({a;b;c;d;e;f;g;.....},3,1) = c
    INDEX({a;b;c;d;e;f;g;.....},4,1) = d
    INDEX({a;b;c;d;e;f;g;.....},7,1) = g
    INDEX({a;b;c;d;e;f;g;.....},9,1) = i
    INDEX({a;b;c;d;e;f;g;.....},1,1) = ' This will error
    etc..

    Consider the formula in I2
    For a normal formula this Formula would look like
    = IF(ISERROR(H2),"",H2)
    So if the value in cell H2 was an error then an empty string would be returned. ( An empty string "" is Excels way of showing no value ). Else If cell H2 does not contain an error then the value in cell H2 is returned.
    Because the formula { IF(ISERROR(H2:H11),"",H2:H11)}, has been entered in the CSE way, (type 2 way for output over the range I2:I11 ), Excel does the formula 10 times. Each time it uses the next value from the range H2:H11
    So the formulas evaluated by Excel are:
    IF(ISERROR(a),"",a)=a
    IF(ISERROR(c),"",c)=c
    IF(ISERROR(d),"",d)=d
    IF(ISERROR(g),"",g)=g
    IF(ISERROR(i),"",i)=i
    IF(ISERROR( 'This cell has an error shown ),"",error)="" ' The returned value appears as nothing to us, that is to say we see nothing in the cell
    IF(ISERROR( 'This cell has an error shown ),"",error)="" ' The returned value appears as nothing to us, that is to say we see nothing in the cell
    etc...


    That's it !

    A final formula can be achieved by firstly substituting the formula from H2 in place of H2:H11 in the final formula, and then substituting the formula in G2 in place of G2:G11, and then substituting the formula in F2 in place of F2:F11. ( The formula in D2 was not used
    Attached Files Attached Files
    Last edited by DocAElstein; 01-25-2017 at 11:38 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    I did it 'my way':

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$E$1" Then Cells(1).CurrentRegion.AutoFilter 2, Target
    End Sub
    or unnecessarily complicated

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$E$1" Then 
          with Cells(1).CurrentRegion
             .AutoFilter 2, Target
             .resize(,1).copy cells(1,5)
             .autofilter
          end with
       end if
    End Sub
    Last edited by snb; 01-26-2017 at 04:58 PM.

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,429
    Rep Power
    10

    We do it any which way we feel, and why not ;)

    Quote Originally Posted by snb View Post
    I did it 'my way':.....
    Hi snb

    In my unconventional self learnt Excel VBA I seem to have missed out on the AutoFilter stuff, But I think I see what is going on here

    Code:
    '  snb did it his way :) http://www.excelfox.com/forum/showthread.php/2141-How-to-populate-all-the-rows-in-one-column-based-on-one-cell-value?p=10040#post10040
    Private Sub Worksheet_Change(ByVal Target As Range) ' Code 1:          Already Dim'ed...*** , like the worksheet of a class worksheet already there, do this is just an instance or like. Checks through to see if these things are there, Kicks in then also when Worksheet is changed      "watcher is already there" http://www.excelforum.com/showthread.php?t=1162925&page=2&p=4522463#post4522463
        If Target.Address = "$E$1" And (Target.Value = "Issue" Or Target.Value = "Signed off") Then ' Target is the reseved name for the Range where the change takes place ...   when that is (just) E Then ...
          With Cells(1).CurrentRegion ' With the Range object starting at A1 that is enclosed by a free row and column..
           .AutoFilter 2, Target.Value ' Autofilter for whole range based on second column being the Target value
           Application.EnableEvents = False ' ### stop code kicking in again
           .Resize(, 1).Copy Destination:=Cells(1, 5) ' Copying of a filtered Range seems to just copy what is seen. Only copying a range of the existing rows, but just the first column. "Shortcut" copy destination bypassing clipboard maybe..        http://www.eileenslounge.com/viewtopic.php?f=27&t=25002#p195791    This line sets the routine off again but for the bigger range it does nothing but including the Target.Value causes error as it retuens then an Array so  ' ###
           Application.EnableEvents = True
           .AutoFilter ' This turns the Filtering off, so we "see all" again
          End With
       End If
    End Sub
        '   I cHaNgEd the cHaNgE above - it got cHaNgEd here automatically... that is because...*** Already Dim'ed ... but comment this out or it error as if you Dimed a variable twice !!°
    '    Private Sub WoRkShEeT_cHaNgE(ByVal Target As Range) ' Code 2: It appears that the autofilter when applied "sees all" even if we do not - so it will always work
    '        If Target.Address = "$E$1" And (Target.Value = "Issue" Or Target.Value = "Signed off") Then Cells(1).CurrentRegion.AutoFilter 2, Target.Value '
    '    End Sub
    _...

    _....and then
    ' I did it in a strings Functional way


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If IsArray(Target.Value) Then                                             ' Need to check that selection is not a Range object of more than one cell, if it is , for example then .Value prooperty willreturn an Array
                                                                ' Then if we have an Array we come here,, do nothing, so go to  End If '_-1
        Else                                                          ' for a single cell value change:-
            If Target.Address = "$E$1" And (Target.Value = "Issue" Or Target.Value = "Signed off") Then
            Dim RngAllcells As Range: Set RngAllcells = Cells ' Cells property returnng a Range object of all cells in object to which it is applied. We are in the Worksheet class module of a Worksheet so Cells unqualified call goes there
            Dim arrIn() As Variant: Let arrIn() = RngAllcells.Item(1).CurrentRegion.Value                                                                   ' Range Item Property applied to top left of Range Object of all worksheet cells ' http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells?p=10012#post10012
            Dim ConcatedWonk() As String                                                           ' Array for string values based on concatenation of first two columns. We do not need a dynamic Array but must dimension it as in next line, because...
             ReDim ConcatedWonk(1 To UBound(arrIn(), 1))                            '                                 ...Must use Re Dim method as Dim declaration only takes values
            Dim Cnt As Long                                                           ' Loop bound variable count
                For Cnt = 1 To UBound(arrIn(), 1) Step 1
                 Let ConcatedWonk(Cnt) = arrIn(Cnt, 1) & "|" & arrIn(Cnt, 2)                                                                         'String built from both column values per row
                Next Cnt
            Dim arrFilt() As String: Let arrFilt() = VBA.Strings.Filter(ConcatedWonk(), Target.Value, True)                                     'Filters True in all member elements of ConcatedWonk() that have the Target value as part of that concatenated string
            Dim arrFiltT() As String                                                                           ' We will both transpose the filtered member element  and chop out the bit in it after and inclusding the "|" which leaves us with our column A value only. Once again the dynamic array is not needed , but we must do it this way to use other than numbers in the sizing.
             ReDim arrFiltT(1 To (UBound(arrFilt()) + 1), 1 To 1)                                                                         ' An Array 2 Dimension 1 column for transposed column A values
                For Cnt = 1 To (UBound(arrFilt()) + 1) Step 1
                 Let arrFiltT(Cnt, 1) = VBA.Strings.Left(arrFilt(Cnt - 1), InStr(1, arrFilt(Cnt - 1), "|") - 1)                                                                        'Each "vertical" member element is given the "horizontal" form the filtered Array but only the string part from left of concatenated string to the point just before the "|"
                Next Cnt
              Let Range("E1").Resize(UBound(arrFiltT(), 1), UBound(arrFiltT(), 2)).Value = arrFiltT()                                                                                       '.value property is applied to a Range object of size of that of the top left of where we want the output resized to suit the dimensions of the Array. The presented field of the memory locations for the value may be assigned directly an Array of values
            Else                                                             ' Targeted somewhere else , so do nothing. Redundant code
            End If
        End If                                                                                          '_-1 had a multi cells selection change
    End Sub
    Last edited by DocAElstein; 01-27-2017 at 05:18 PM. Reason: moved the comments a bit to the right for snb
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  7. #7
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    @Doc

    You missed the one-liner that suffices ?

    I find commented code too much pain to read: I prefer the comments to be separated from the code entirely.
    But: the simpler the code the less commentary is needed (see my one-liner).
    Ergo: prevent the need for comments by 'bare' coding (instead of 'clumsy coding')

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,429
    Rep Power
    10
    Hi snb
    Quote Originally Posted by snb View Post
    You missed the one-liner that suffices ?.)
    No I didn't. it was my favourite : It is in the first code window: - you have to scroll down. - Just like to see the 'comments you mostly have to scroll to thje right, so its up to you if you want to see them or not. There is loads of room there. I find it a great place to store my notes. ( EDIT: I just moved the comments a bit more to the right for you in my code )
    But everyone to their own...
    Quote Originally Posted by snb View Post
    ...
    I find commented code too much pain to read:......
    I thought you would like it
    Last edited by DocAElstein; 01-27-2017 at 11:39 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  9. #9
    Junior Member
    Join Date
    Jan 2017
    Posts
    2
    Rep Power
    0
    Hi DocAElstein, Your solution works perfectly. Your detailed explanation is very helpful and easy to understand.

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,429
    Rep Power
    10
    Quote Originally Posted by jmishra View Post
    Hi DocAElstein, Your solution works perfectly. Your detailed explanation is very helpful and easy to understand.
    Yous very welcome.
    Thanks very much for the feedback
    Alan

    P.S.. attatched to this post is the File with snbs and my codes in it as well
    Attached Files Attached Files
    Last edited by DocAElstein; 02-01-2017 at 06:10 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. populate default values in cell of a csv file
    By dhivya.enjoy in forum Excel Help
    Replies: 2
    Last Post: 10-23-2013, 12:59 PM
  2. Insert blank rows based on cell value
    By muhammad susanto in forum Excel Help
    Replies: 13
    Last Post: 09-11-2013, 06:18 AM
  3. VBA To Delete Rows Based On Value Criteria In A Column
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 08-15-2013, 12:45 PM
  4. Replies: 2
    Last Post: 04-10-2013, 12:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •