How to Extract list from Database

Beatrice

Board Regular
Joined
Sep 17, 2019
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
I would like to get some help for extracting part of the database to a new selection list.
Capture.JPG

Please see my file as link below:
WeTransfer download file

Many thanks in advance for your help.

Bea
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
See if this works for you.
Formula in cell O7 shows the number of items that meet the criteria.
The formula in cell N10 is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag down the rows as far as you think is needed for your data.
Formula in cell O10 can just be copied across and down as needed.

Book1
ABCDEFGHIJKLMNOPQ
1
2DATABASE
3
4
5
6BRANDCOLORITEMSIZEPRICESTOCK LEVEL
7H & MREDT-SHIRTS39.90FEWPLEASE SELECT : # 0f Items3
8H & MBLUET-SHIRTS39.90FEW
9H & MYELLOW T-SHIRTS39.90HIGHITEMCOLORSIZERef#BRANDPRICESTOCK LEVEL
10H & MGREENT-SHIRTS39.90HIGHT-SHIRTWhiteM14H & M39.90NO
11H & MBROWNT-SHIRTS39.90HIGH31ZARA59.90HIGH
12H & MBLACKT-SHIRTS39.90FEW40MANGO79.90FEW
13H & MWHITET-SHIRTS39.90FEW    
14H & MREDT-SHIRTM39.90FEW    
15H & MBLUET-SHIRTM39.90NO    
16H & MYELLOW T-SHIRTM39.90FEW    
17H & MGREENT-SHIRTM39.90FEW   
18H & MBROWNT-SHIRTM39.90FEW
19H & MBLACKT-SHIRTM39.90FEW
20H & MWHITET-SHIRTM39.90NO
21H & MREDT-SHIRTL39.90HIGH
22H & MBLUET-SHIRTL39.90FEW
23H & MYELLOW T-SHIRTL39.90HIGH
24H & MGREENT-SHIRTL39.90HIGH
25H & MBROWNT-SHIRTL39.90HIGH
26H & MBLACKT-SHIRTL39.90FEW
27H & MWHITET-SHIRTL39.90FEW
28ZARAREDT-SHIRTS59.90NO
29ZARABLUET-SHIRTS59.90FEW
30ZARAYELLOW T-SHIRTS59.90HIGH
31ZARABLACKT-SHIRTS59.90FEW
32ZARAWHITET-SHIRTS59.90FEW
33ZARAREDT-SHIRTM59.90FEW
34ZARABLUET-SHIRTM59.90NO
35ZARAYELLOW T-SHIRTM59.90FEW
36ZARABLACKT-SHIRTM59.90HIGH
37ZARAWHITET-SHIRTM59.90HIGH
38ZARAREDT-SHIRTL59.90FEW
39ZARABLUET-SHIRTL59.90FEW
40ZARAYELLOW T-SHIRTL59.90FEW
41ZARABLACKT-SHIRTL59.90HIGH
42ZARAWHITET-SHIRTL59.90HIGH
43MANGOBLACKT-SHIRTS79.90FEW
44MANGOWHITET-SHIRTS79.90FEW
45MANGOBLACKT-SHIRTM79.90NO
46MANGOWHITET-SHIRTM79.90FEW
47MANGOBLACKT-SHIRTL79.90FEW
48MANGOWHITET-SHIRTL79.90FEW
Sheet1
Cell Formulas
RangeFormula
O7O7=COUNTIFS($D$7:$D$48,$K$10,$C$7:$C$48,$L$10,$E$7:$E$48,$M$10)
O10:Q17O10=IF($N10="","",INDEX($B$7:$G$48,$N10,MATCH(O$9,$B$6:$G$6,0)))
N10:N16N10{=IF(ROWS($N$10:N10)>$O$7,"",SMALL(IF($D$7:$D$48=$K$10,IF($C$7:$C$48=$L$10,IF($E$7:$E$48=$M$10,ROW($B$7:$B$48)-ROW($B$7)+1))),ROWS($O$10:O10)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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