Magic trick 185: dynamic list based on criterias

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Jimlum

New Member
Joined
May 30, 2015
Messages
3
Hi Aladin,

The data set will be as follows:

Member IDMember NamePICPeriod
ID1Name 111
ID3Name 222
ID6Name 322
ID2Name 411
ID7Name 512
ID8Name 621
ID4Name 731
ID9Name 811

<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 IDMember NamePICPeriod
ID1Name 111
ID2Name 411
ID9Name 811


<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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Thanks for the input and output samples...

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Member IDnamePICPeriodPIC
1
2​
12345000
name-1
1
1
Period
1
3​
12345001
name-2
1
1
Record count
3
4​
12345002
name-3
2
1
IdxMember IDnamePICPeriod
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)))
 

Jimlum

New Member
Joined
May 30, 2015
Messages
3
Hi Aladin,

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

Cheers!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,684
Messages
5,637,790
Members
416,982
Latest member
lisam77

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top