Using a formula that populate specific list based on list of conditions

zinah

Active Member
Joined
Nov 28, 2018
Messages
353
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have below sample table and what I need is to populate demographics based on the count of each factor.
I put the expected results based on the result of factor count of AAA, which is in this example are 4 that is based on the formula that count AAA based on selected filters from drop down list. The table of Factors and their count is a dynamic table that depends on selected items from Column A (USA, Salse, etc...). Can anyone help?
As you can notice, the data are in different columns and not specific sequence and in my case below, I need to get the result of 2 cells in one cell (first & last name) and CustomerKey after column Status (Y/N)

Test_Filter FIle.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1CustomerKeyFirstNameLastNameCountryStatus (Y/N)BirthDateMaritalStatusTeamFactors
211000JONYANGUSAYes4/8/1966MSalesAAA
311001EUGENEHUANGUKYes########SSalesBBB
4USA11002RUBENTORRESUSAYes########MHRAAA
511003CHRISTYZHUUSAYes########SHRBBB
6Sales11004ELIZABETHJOHNSONUSAYes8/8/1968SSalesBBB
711005JULIORUIZUAEYes8/5/1965SSalesHHH
8Include11007MARCOMEHTAUSAYes5/9/1964MHRBBB
911008ROBINVERHOFFUKYes7/7/1964SHRBBB
10Yes11009SHANNONCARLSONUSAYes4/1/1964SSalesBBB
11FactorsCount of each factor11010JACQUELYNSUAREZUSAYes2/6/1964SSalesAAA
12AAA411011CURTISLUUKYes########MHRBBB
13BBB311012LAURENWALKERUKNo########MHRHHH
14HHH111013IANJENKINSUSAYes8/6/1968MSalesAAA
1511014SYDNEYBENNETTUKYes5/9/1968SSalesBBB
1611015CHLOEYOUNGUSAYes########SHRAAA
1711016WYATTHILLUSANo########MHRAAA
1811017SHANNONWANGUKYes########SSalesBBB
19Expected Result11018CLARENCERAIUAEYes########SSalesHHH
20FirstName LastNameCountryStatus (Y/N)CustomerKeyBirthDateMaritalStatusTeamFactors11019LUKELALUKYes3/7/1978SHRAAA
21JON YANGUSAYes110004/8/1966MSalesAAA11020JORDANKINGUKYes########SHRAAA
22JACQUELYN SUAREZUSAYes110102/6/1964SSalesAAA11021DESTINYWILSONUAEYes9/3/1978SSalesHHH
23IAN JENKINSUSAYes110138/6/1968MSalesAAA11022ETHANZHANGUKYes########MSalesAAA
24CHLOE GARCIAUSAYes1103711/27/1977SSalesAAA11023SETHEDWARDSUKYes########MHRBBB
2511024RUSSELLXIEUAEYes########MHRHHH
2611025ALEJANDROBECKUAEYes########MSalesHHH
2711026HAROLDSAIUSAYes4/3/1946SSalesBBB
2811027JESSIEZHAOUKNo########MHRHHH
2911028JILLJIMENEZUSANo########MHRAAA
3011029JIMMYMORENOUKYes########MSalesBBB
3111030BETHANYYUANUAEYes########MSalesHHH
3211031THERESARAMOSUKYes########MHRHHH
3311032DENISESTONEUKNo########MHRBBB
3411033JAIMENATHUKYes########MSalesHHH
3511034EBONYGONZALEZUAEYes########MHRHHH
3611035WENDYDOMINGUEZUKNo########MHRBBB
3711036JENNIFERRUSSELLUSANo########MHRHHH
3811037CHLOEGARCIAUSAYes########SSalesAAA
3911038DIANAHERNANDEZUSAYes########MSalesHHH
4011039MARCMARTINUAEYes########MSalesHHH
4111040JESSEMURPHYUSANo8/1/1977MSalesAAA
4211041AMANDACARTERUSAYes########MSalesBBB
4311042MEGANSANCHEZUAEYes########MSalesHHH
4411043NATHANSIMMONSUSANo########MSalesAAA
4511044ADAMFLORESUSANo########MSalesBBB
4611045LEONARDNARAUAEYes########SSalesBBB
Sheet1
Cell Formulas
RangeFormula
D12:D14D12=COUNTIFS($AG:$AG,C12,$R:$R,$A$4,$AA:$AA,$A$6,$DT:$DT,$A$8,$S:$S,$A$10)
Cells with Data Validation
CellAllowCriteria
A10ListYes,No
A8ListInclude,Exclude
A6ListSales,HR
A4ListUSA,UK,UAE
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
How about
+Fluff 1.xlsm
ABCDEFGHIJKLNORSXYAAAG
1CustomerKeyFirstNameLastNameCountryStatus (Y/N)BirthDateMaritalStatusTeamFactors
211000JONYANGUSAYes08/04/1966MSalesAAA
311001EUGENEHUANGUKYes14/05/1965SSalesBBB
4USA11002RUBENTORRESUSAYes12/08/1965MHRAAA
511003CHRISTYZHUUSAYes15/02/1968SHRBBB
6Sales11004ELIZABETHJOHNSONUSAYes08/08/1968SSalesBBB
711005JULIORUIZUAEYes05/08/1965SSalesHHH
8Include11007MARCOMEHTAUSAYes09/05/1964MHRBBB
911008ROBINVERHOFFUKYes07/07/1964SHRBBB
10Yes11009SHANNONCARLSONUSAYes01/04/1964SSalesBBB
11FactorsCount of each factor11010JACQUELYNSUAREZUSAYes06/02/1964SSalesAAA
12AAA411011CURTISLUUKYes04/11/1963MHRBBB
13BBB311012LAURENWALKERUKNo18/01/1968MHRHHH
14HHH111013IANJENKINSUSAYes06/08/1968MSalesAAA
1511014SYDNEYBENNETTUKYes09/05/1968SSalesBBB
1611015CHLOEYOUNGUSAYes27/02/1979SHRAAA
1711016WYATTHILLUSANo28/04/1979MHRAAA
1811017SHANNONWANGUKYes26/06/1944SSalesBBB
1911018CLARENCERAIUAEYes09/10/1944SSalesHHH
20FirstNameLastNameCountryStatus (Y/N)CustomerKeyBirthDateMaritalStatusTeamFactors11019LUKELALUKYes07/03/1978SHRAAA
21JONYANGUSAYes1100008/04/1966MSalesAAA11020JORDANKINGUKYes20/09/1978SHRAAA
22ELIZABETHJOHNSONUSAYes1100408/08/1968SSalesBBB11021DESTINYWILSONUAEYes03/09/1978SSalesHHH
23SHANNONCARLSONUSAYes1100901/04/1964SSalesBBB11022ETHANZHANGUKYes12/10/1978MSalesAAA
24JACQUELYNSUAREZUSAYes1101006/02/1964SSalesAAA11023SETHEDWARDSUKYes11/10/1978MHRBBB
25IANJENKINSUSAYes1101306/08/1968MSalesAAA11024RUSSELLXIEUAEYes17/09/1978MHRHHH
26HAROLDSAIUSAYes1102603/04/1946SSalesBBB11025ALEJANDROBECKUAEYes23/12/1945MSalesHHH
27CHLOEGARCIAUSAYes1103727/11/1977SSalesAAA11026HAROLDSAIUSAYes03/04/1946SSalesBBB
28DIANAHERNANDEZUSAYes1103823/03/1948MSalesHHH11027JESSIEZHAOUKNo07/12/1946MHRHHH
2911028JILLJIMENEZUSANo11/04/1946MHRAAA
3011029JIMMYMORENOUKYes21/12/1946MSalesBBB
3111030BETHANYYUANUAEYes22/02/1947MSalesHHH
3211031THERESARAMOSUKYes22/08/1947MHRHHH
3311032DENISESTONEUKNo11/06/1947MHRBBB
3411033JAIMENATHUKYes23/09/1947MSalesHHH
3511034EBONYGONZALEZUAEYes19/06/1947MHRHHH
3611035WENDYDOMINGUEZUKNo24/02/1948MHRBBB
3711036JENNIFERRUSSELLUSANo18/12/1978MHRHHH
3811037CHLOEGARCIAUSAYes27/11/1977SSalesAAA
3911038DIANAHERNANDEZUSAYes23/03/1948MSalesHHH
4011039MARCMARTINUAEYes17/12/1948MSalesHHH
4111040JESSEMURPHYUSANo01/08/1977MSalesAAA
4211041AMANDACARTERUSAYes16/10/1977MSalesBBB
4311042MEGANSANCHEZUAEYes13/06/1977MSalesHHH
4411043NATHANSIMMONSUSANo24/02/1976MSalesAAA
4511044ADAMFLORESUSANo24/05/1949MSalesBBB
4611045LEONARDNARAUAEYes19/05/1950SSalesBBB
Main
Cell Formulas
RangeFormula
D12:D14D12=COUNTIFS($AG:$AG,C12,$R:$R,$A$4,$AA:$AA,$A$6,$DT:$DT,$A$8,$S:$S,$A$10)
B21:J28B21=SORTBY(FILTER(FILTER(L1:DT100,(R1:R100=A4)*(AA1:AA100=A6)*(S1:S100=A10)*(DT1:DT100=A8)),ISNUMBER(MATCH(L1:DT1,B20:J20,0))),MATCH(FILTER(L1:DT1,ISNUMBER(MATCH(L1:DT1,B20:J20,0))),B20:J20,0))
Dynamic array formulas.
 
Upvote 0
Solution
How about
+Fluff 1.xlsm
ABCDEFGHIJKLNORSXYAAAG
1CustomerKeyFirstNameLastNameCountryStatus (Y/N)BirthDateMaritalStatusTeamFactors
211000JONYANGUSAYes08/04/1966MSalesAAA
311001EUGENEHUANGUKYes14/05/1965SSalesBBB
4USA11002RUBENTORRESUSAYes12/08/1965MHRAAA
511003CHRISTYZHUUSAYes15/02/1968SHRBBB
6Sales11004ELIZABETHJOHNSONUSAYes08/08/1968SSalesBBB
711005JULIORUIZUAEYes05/08/1965SSalesHHH
8Include11007MARCOMEHTAUSAYes09/05/1964MHRBBB
911008ROBINVERHOFFUKYes07/07/1964SHRBBB
10Yes11009SHANNONCARLSONUSAYes01/04/1964SSalesBBB
11FactorsCount of each factor11010JACQUELYNSUAREZUSAYes06/02/1964SSalesAAA
12AAA411011CURTISLUUKYes04/11/1963MHRBBB
13BBB311012LAURENWALKERUKNo18/01/1968MHRHHH
14HHH111013IANJENKINSUSAYes06/08/1968MSalesAAA
1511014SYDNEYBENNETTUKYes09/05/1968SSalesBBB
1611015CHLOEYOUNGUSAYes27/02/1979SHRAAA
1711016WYATTHILLUSANo28/04/1979MHRAAA
1811017SHANNONWANGUKYes26/06/1944SSalesBBB
1911018CLARENCERAIUAEYes09/10/1944SSalesHHH
20FirstNameLastNameCountryStatus (Y/N)CustomerKeyBirthDateMaritalStatusTeamFactors11019LUKELALUKYes07/03/1978SHRAAA
21JONYANGUSAYes1100008/04/1966MSalesAAA11020JORDANKINGUKYes20/09/1978SHRAAA
22ELIZABETHJOHNSONUSAYes1100408/08/1968SSalesBBB11021DESTINYWILSONUAEYes03/09/1978SSalesHHH
23SHANNONCARLSONUSAYes1100901/04/1964SSalesBBB11022ETHANZHANGUKYes12/10/1978MSalesAAA
24JACQUELYNSUAREZUSAYes1101006/02/1964SSalesAAA11023SETHEDWARDSUKYes11/10/1978MHRBBB
25IANJENKINSUSAYes1101306/08/1968MSalesAAA11024RUSSELLXIEUAEYes17/09/1978MHRHHH
26HAROLDSAIUSAYes1102603/04/1946SSalesBBB11025ALEJANDROBECKUAEYes23/12/1945MSalesHHH
27CHLOEGARCIAUSAYes1103727/11/1977SSalesAAA11026HAROLDSAIUSAYes03/04/1946SSalesBBB
28DIANAHERNANDEZUSAYes1103823/03/1948MSalesHHH11027JESSIEZHAOUKNo07/12/1946MHRHHH
2911028JILLJIMENEZUSANo11/04/1946MHRAAA
3011029JIMMYMORENOUKYes21/12/1946MSalesBBB
3111030BETHANYYUANUAEYes22/02/1947MSalesHHH
3211031THERESARAMOSUKYes22/08/1947MHRHHH
3311032DENISESTONEUKNo11/06/1947MHRBBB
3411033JAIMENATHUKYes23/09/1947MSalesHHH
3511034EBONYGONZALEZUAEYes19/06/1947MHRHHH
3611035WENDYDOMINGUEZUKNo24/02/1948MHRBBB
3711036JENNIFERRUSSELLUSANo18/12/1978MHRHHH
3811037CHLOEGARCIAUSAYes27/11/1977SSalesAAA
3911038DIANAHERNANDEZUSAYes23/03/1948MSalesHHH
4011039MARCMARTINUAEYes17/12/1948MSalesHHH
4111040JESSEMURPHYUSANo01/08/1977MSalesAAA
4211041AMANDACARTERUSAYes16/10/1977MSalesBBB
4311042MEGANSANCHEZUAEYes13/06/1977MSalesHHH
4411043NATHANSIMMONSUSANo24/02/1976MSalesAAA
4511044ADAMFLORESUSANo24/05/1949MSalesBBB
4611045LEONARDNARAUAEYes19/05/1950SSalesBBB
Main
Cell Formulas
RangeFormula
D12:D14D12=COUNTIFS($AG:$AG,C12,$R:$R,$A$4,$AA:$AA,$A$6,$DT:$DT,$A$8,$S:$S,$A$10)
B21:J28B21=SORTBY(FILTER(FILTER(L1:DT100,(R1:R100=A4)*(AA1:AA100=A6)*(S1:S100=A10)*(DT1:DT100=A8)),ISNUMBER(MATCH(L1:DT1,B20:J20,0))),MATCH(FILTER(L1:DT1,ISNUMBER(MATCH(L1:DT1,B20:J20,0))),B20:J20,0))
Dynamic array formulas.
As usual you're the first to suggest a great array formula solution, thank you for that. Your suggestion works and I even understood the methdology behind your formula and added another condition to filter the result(as showing in formula below), thank you so much :)

Excel Formula:
=IF(D13<>0,SORTBY(FILTER(FILTER(O1:DW100,(AJ1:AJ100=C13)*(U1:U100=A4)*(AD1:AD100=A6)*(V1:V100=A10)*(DW1:DW100=A8)),ISNUMBER(MATCH(O1:DW1,B20:J20,0))),MATCH(FILTER(O1:DW1,ISNUMBER(MATCH(O1:DW1,B20:J20,0))),B20:J20,0)),"")
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top