Create Dynamic List - Based On Criteria

[Wobzy]

Hey guys,

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Client01a[/TD]
[TD]Inactive[/TD]
[TD]Client01b
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client01b[/TD]
[TD]Active[/TD]
[TD]Client02c[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Client02a
[/TD]
[TD]Inactive[/TD]
[TD]Client03a[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Client02b[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Client02c[/TD]
[TD]Active[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Client03a[/TD]
[TD]Active[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Im working on a database wherein multiple instances of an entry in A:A need to exist for the sake of historical data.

I want C:C to automatically list only the most current version of the entry, dictated by corresponding B:B value Active.

I'm trying and failing to produce the result using index and match.

Help is appreciated.

 

[iggydarsa]

Put this formula on C1:

=IFERROR(INDEX(A:A, MATCH("Active",B:B, FALSE)), "")

Put this on C2:

=IFERROR(INDEX(A:A, MATCH(C1,A:A, 0)+MATCH("Active", INDIRECT(ADDRESS(MATCH(C1,A:A, 0)+1, 2)  & ":" & "$B$" & ROWS(B:B)),0)), "")

Drag the formula on C2 downwards.

 

[Wobzy]

Put this formula on C1:

=IFERROR(INDEX(A:A, MATCH("Active",B:B, FALSE)), "")

Put this on C2:

=IFERROR(INDEX(A:A, MATCH(C1,A:A, 0)+MATCH("Active", INDIRECT(ADDRESS(MATCH(C1,A:A, 0)+1, 2)  & ":" & "$B$" & ROWS(B:B)),0)), "")

Drag the formula on C2 downwards.

Hey thanks for that.

Yeah C2 is where im having the issue.

In my sheet;
A:A = D:D
B:B = E:E
C:C = K:K

Swapping out the references produced no result for the second formula.

So i created a dummy table using exactly the info i gave in the example.
It worked perfectly.
I inserted columns where required, instantly broke the references.
The only reference that didn't auto update was "$B$"
Updated it manually but to no effect.

Sorry im a bit out of my depth.

 

[iggydarsa]

Have you corrected the parts where says C1 to the proper address?

 

[Aladin Akyurek]

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

A​

[/td][td]

B​

[/td][td]

C​

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

1​

[/td][td]Client01a[/td][td]Inactive[/td][td]

3​

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

2​

[/td][td]Client01b[/td][td]Active[/td][td]Client01b[/td][/tr]
[tr][td]

3​

[/td][td]Client02a[/td][td]Inactive[/td][td]Client02c[/td][/tr]
[tr][td]

4​

[/td][td]Client02b[/td][td]Inactive[/td][td]Client03a[/td][/tr]
[tr][td]

5​

[/td][td]Client02c[/td][td]Active[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]Client03a[/td][td]Active[/td][td][/td][/tr]
[/table]

In C1 just enter:

=COUNTIFS(A1:A6,"?*",B1:B6,"active")

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

=IF(ROWS($C$3:C3)>$C$1,"",INDEX($A$1:$A$6,SMALL(IF($B$1:$B$6="active",IF(1-($A$1:$A$6=""),ROW($A$1:$A$6)-ROW($A$1)+1)),ROWS($C$3:C3))))

 

[Wobzy]

Have you corrected the parts where says C1 to the proper address?

In C1 just enter:

=COUNTIFS(A1:A6,"?*",B1:B6,"active")

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

=IF(ROWS($C$3:C3)>$C$1,"",INDEX($A$1:$A$6,SMALL(IF($B$1:$B$6="active",IF(1-($A$1:$A$6=""),ROW($A$1:$A$6)-ROW($A$1)+1)),ROWS($C$3:C3))))

Hey Guys,

Thanks for the responses.

I did update all references, no result.
For both Iggy and Aladins examples.

I can usually logic my way out of formula i don't fully understand.
But I've been at it for ages I'm just not getting it.

So here's my actual table;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client01a[/TD]
[TD]INACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client01b[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client01b[/TD]
[TD]ACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client02c[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client02a[/TD]
[TD]INACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client03a[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client02b[/TD]
[TD]INACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client04b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client02c[/TD]
[TD]ACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client05a[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client03a[/TD]
[TD]ACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client04a[/TD]
[TD]INACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client04b[/TD]
[TD]ACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client05a[/TD]
[TD]INACTIVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

D & E (formerly A & B) are dynamic and will be updated manually.
K (formerly C) is dynamic and will need to automatically update active values from D.
I cant say how long the list will get.
If referencing the whole column is not ideal, i don't imagine i'll be going past 200.

Thanks again so much guys.

 

[Aladin Akyurek]

Here is the workbook which implements post #5 ...
https://www.dropbox.com/s/gwlloowwt1jx8p3/Wobzy Create Dynamic List - Based On Criteria.xlsx?dl=0

 

[Wobzy]

Here is the workbook which implements post #5 ...
https://www.dropbox.com/s/gwlloowwt1jx8p3/Wobzy Create Dynamic List - Based On Criteria.xlsx?dl=0

Thanks for going out of your way to do that.

But I'm having trouble with updating it to fall in line with my sheet.

My bad i probably shouldn't have abbreviated it down in the example.

 

[Wobzy]

FIXED IT!

Thank you.

 

[Aladin Akyurek]

FIXED IT!

Thank you.

You are welcome.