Magic trick 185: dynamic list based on criterias

[Jimlum]

Hi,

Thanks for the tutorial that can make things so much easier.

I have tired based on the video, and i am only able to get one record. the rest of the records are returned as #num.

Would you be able to advice what can be done to rectify this?

the file can be obtained from:
https://drive.google.com/open?id=0B5Voa2yS1dHkMFVWUDB2dXFQWFU&authuser=0

Thanks for your help
Jimmy

 

[Aladin Akyurek]

Try to post directly on this board a small sample along with the desired results, using an appropriate method: Attachments or https://app.box.com/s/soezox25h3w0q5s4rcyl.

 

[Jimlum]

Hi Aladin,

The data set will be as follows:

Member ID	Member Name	PIC	Period
ID1	Name 1	1	1
ID3	Name 2	2	2
ID6	Name 3	2	2
ID2	Name 4	1	1
ID7	Name 5	1	2
ID8	Name 6	2	1
ID4	Name 7	3	1
ID9	Name 8	1	1

<tbody>
</tbody>

My criteria is to extract all the members who has made transactions in the first period whose PIC is 1.

The expected outcome should be as follows:

Member ID	Member Name	PIC	Period
ID1	Name 1	1	1
ID2	Name 4	1	1
ID9	Name 8	1	1

<tbody>
</tbody>

The sequence of the members should be based on the initial list and no additional sorting of the data is required. I am trying to obtain a dynamic listing so that my criterias for the list can be flexible.

Thanks
Jimmy

 

[Aladin Akyurek]

Thanks for the input and output samples...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Member ID	name	PIC	Period		PIC	1​
2​	12345000​	name-1	1​	1​		Period	1​
3​	12345001​	name-2	1​	1​		Record count	3​
4​	12345002​	name-3	2​	1​		Idx	Member ID	name	PIC	Period
5​	12345003​	name-4	1​	2​		1​	12345000​	name-1	1​	1​
6​	12345004​	name-5	3​	2​		2​	12345001​	name-2	1​	1​
7​	12345005​	name-6	2​	2​		7​	12345006​	name-7	1​	1​
8​	12345006​	name-7	1​	1​
9​

G3, control+shift+enter (cse), not just enter:

=SUM(IF($C$2:$C$8=G$1,IF($D$2:$D$8=G$2,1)))

F5, cse and copy down:

=IF(ROWS($F$5:F5)<=$G$3,SMALL(IF($C$2:$C$8=$G$1,
    IF($D$2:$D$8=$G$2,ROW($C$2:$C$8)-ROW($C$2)+1)),ROWS($F$5:F5)),"")

G5, just enter, copy across, and down:

=IF($F5="","",INDEX($A$2:$D$8,$F5,MATCH(G$4,$A$1:$D$1,0)))

 

[Jimlum]

Hi Aladin,

It work pretty well. Thanks much for your prompt response.

Cheers!!

 

[Aladin Akyurek]

Hi Aladin,

It work pretty well. Thanks much for your prompt response.

Cheers!!

You are welcome.