Magic trick 185: dynamic list based on criterias

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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,210
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!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,586
Messages
5,838,230
Members
430,535
Latest member
krazykram

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