Return a list based off of values in another column

[medeirad]

Hi All,

I'm looking to get a list of values based off of information in another column. See the fictitious table below. In the first column are sequential order #'s and in the second column are the clients' names.

I'd like to pull the list of values in column A based on the client in column B.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Client[/TD]
[/TR]
[TR]
[TD]HDS17-1[/TD]
[TD]CLIENT2[/TD]
[/TR]
[TR]
[TD]HDS17-2[/TD]
[TD]CLIENT1[/TD]
[/TR]
[TR]
[TD]HDS17-3[/TD]
[TD]CLIENT2[/TD]
[/TR]
[TR]
[TD]HDS17-4[/TD]
[TD]CLIENT2[/TD]
[/TR]
[TR]
[TD]HDS17-5[/TD]
[TD]CLIENT1[/TD]
[/TR]
[TR]
[TD]HDS17-6[/TD]
[TD]CLIENT1[/TD]
[/TR]
[TR]
[TD]HDS17-7[/TD]
[TD]CLIENT1[/TD]
[/TR]
[TR]
[TD]HDS17-8[/TD]
[TD]CLIENT2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[medeirad]

In case I scared anyone off all I'm looking for is a formula that will provide list of numbers in column A that have CLIENT1 in Column B

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td] Order #[/td][td] Client[/td][td][/td][td][/td][td][/td][td]CLIENT1[/td][/tr]
[tr][td]

2​

[/td][td] HDS17-1[/td][td] CLIENT2[/td][td][/td][td][/td][td][/td][td]

4​

[/td][/tr]
[tr][td]

3​

[/td][td] HDS17-2[/td][td] CLIENT1[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td] HDS17-3[/td][td] CLIENT2[/td][td][/td][td][/td][td][/td][td]HDS17-2[/td][/tr]
[tr][td]

5​

[/td][td] HDS17-4[/td][td] CLIENT2[/td][td][/td][td][/td][td][/td][td]HDS17-5[/td][/tr]
[tr][td]

6​

[/td][td] HDS17-5[/td][td] CLIENT1[/td][td][/td][td][/td][td][/td][td]HDS17-6[/td][/tr]
[tr][td]

7​

[/td][td] HDS17-6[/td][td] CLIENT1[/td][td][/td][td][/td][td][/td][td]HDS17-7[/td][/tr]
[tr][td]

8​

[/td][td] HDS17-7[/td][td] CLIENT1[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td] HDS17-8[/td][td] CLIENT2[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 just enter:

=COUNTIFS($B$2:$B$9,F$1)

In F4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(F$4:F4)>F$2,"",INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=F$1,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(F$4:F4))))

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Order #[/td][td]Client[/td][td][/td][td]Client1[/td][/tr]

[tr][td]

2​

[/td][td]HDS17-1[/td][td]CLIENT2[/td][td][/td][td]HDS17-2[/td][/tr]

[tr][td]

3​

[/td][td]HDS17-2[/td][td]CLIENT1[/td][td][/td][td]HDS17-5[/td][/tr]

[tr][td]

4​

[/td][td]HDS17-3[/td][td]CLIENT2[/td][td][/td][td]HDS17-6[/td][/tr]

[tr][td]

5​

[/td][td]HDS17-4[/td][td]CLIENT2[/td][td][/td][td]HDS17-7[/td][/tr]

[tr][td]

6​

[/td][td]HDS17-5[/td][td]CLIENT1[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]HDS17-6[/td][td]CLIENT1[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]HDS17-7[/td][td]CLIENT1[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]HDS17-8[/td][td]CLIENT2[/td][td][/td][td][/td][/tr]
[/table]
D1 is the selected client
D2=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$9=$D$1,ROW($A$2:$A$9)),ROWS($A$1:A1))),"")
"Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself."
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.
then copy down as needed

 

[L. Howard]

Give this a try,
where your two lists are in columns A & B,
a drop down in cell D1 with all the clients names as in the list in column B,
this array formula in cell E1.

=IFERROR(INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=$D$1,ROW($B$2:$B$11)-ROW($B$2)+1),ROWS(E$1:E1))),"")

To enter the formula, hold down Ctrl + Shift and hit Enter, Excel will encase the formula in { } 's, do not enter them yourself.
Now pull down as far as needed to 'capture' all A column items. You can have the formula pulled waaay past the last entry shown in the E column and the IFERROR will return "nothing".

Re-select a client name in D1 and the E column list will up date.

If you make changes to the formula, always remember to ARRAY ENTER each time.

You may note that all range references are set to absolute $x$1 except ROWS(E$1:E1))).
Check the formulas further down in E column and see the natural advancement.

Howard