# Magic trick 185: dynamic list based on criterias

#### Jimlum

##### New Member
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:

Jimmy

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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

Last edited:
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:
Rich (BB code):
``````
=SUM(IF(\$C\$2:\$C\$8=G\$1,IF(\$D\$2:\$D\$8=G\$2,1)))
``````

F5, cse and copy down:
Rich (BB code):
``````
=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:
Rich (BB code):
``````
=IF(\$F5="","",INDEX(\$A\$2:\$D\$8,\$F5,MATCH(G\$4,\$A\$1:\$D\$1,0)))
``````

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

Cheers!!

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

Cheers!!

You are welcome.

Replies
1
Views
521
Replies
6
Views
2K
Replies
0
Views
2K
Replies
9
Views
481
Replies
0
Views
314

1,214,746
Messages
6,121,290
Members
449,021
Latest member
bob_messenger

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back