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

[jmishra]

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.

1854



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (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?f=27&t=40919&p=316597#p316597)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (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=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (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=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=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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.com/2012/06/16/extracting-selected-data-with-array-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

1Will Return the match in column A to B, or FalseType here Signed off or IssueWill return the row number where the match is found or FalseReturns the next smallest number from Array F2:F11Returns value from column A based on row numberRemove error


2aSigned offaSigned off
1
1aa


3bIssue
FALSCH
FALSCH
3cc


4cSigned offc
3
4dd


5dSigned offd
4
7gg


6eIssue
FALSCH
FALSCH
9ii


7fIssue
FALSCH
FALSCH
#ZAHL!
#ZAHL!


8gSigned offg
7
#ZAHL!
#ZAHL!


9hIssue
FALSCH
FALSCH
#ZAHL!
#ZAHL!


10iSigned offi
9
#ZAHL!
#ZAHL!


11jIssue
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

1Will Return the match in column A to B, or FalseType here Signed off or IssueWill return the row number where the match is found or FalseReturns the next smallest number from Array F2:F11Returns value from column A based on row numberRemove error


2aSigned 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)


3bIssue
=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)


4cSigned 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)


5dSigned 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)


6eIssue
=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)


7fIssue
=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)


8gSigned 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)


9hIssue
=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)


10iSigned 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)


11jIssue
=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

[DocAElstein]

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

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..... :rolleyes:
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...
:rolleyes:

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
1857

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

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

_ 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
1860

_ 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
1861

_ 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 )

[DocAElstein]

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

[snb]

I did it 'my way':


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


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

[DocAElstein]

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 :)


' 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



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

[snb]

@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')

[DocAElstein]

Hi snb

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...
...
I find commented code too much pain to read:......
I thought you would like it ;)

[jmishra]

Hi DocAElstein, Your solution works perfectly. Your detailed explanation is very helpful and easy to understand.

[DocAElstein]

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