Create Dynamic List - Based On Criteria

[Wobzy]

Hey guys,

	A	B	C
1	Client01a	Inactive	Client01b
2	Client01b	Active	Client02c
3	Client02a	Inactive	Client03a
4	Client02b	Inactive
5	Client02c	Active
6	Client03a	Active

<tbody>
</tbody>

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]

Row\Col	A​	B​	C​
1​	Client01a	Inactive	3​
2​	Client01b	Active	Client01b
3​	Client02a	Inactive	Client02c
4​	Client02b	Inactive	Client03a
5​	Client02c	Active
6​	Client03a	Active

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;

	A	B	C	D	E	F	G	H	I	J	K
1				Client01a	INACTIVE						Client01b
2				Client01b	ACTIVE						Client02c
3				Client02a	INACTIVE						Client03a
4				Client02b	INACTIVE						Client04b
5				Client02c	ACTIVE						Client05a
6				Client03a	ACTIVE
7				Client04a	INACTIVE
8				Client04b	ACTIVE
9				Client05a	INACTIVE

<tbody>
</tbody>

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.