Condensing data dynamically.

B

bigjonf

New Member
Joined
Nov 19, 2018
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi,
Trying to make my life easier at work. I want to enter stock figures into a spreadsheet which will then come up with a list of items I need to order. However, to copy and paste this into an email to the wine supplier, I would need to get rid of the zero values. Essentially consolidating the two columns (the stock item column and the quantity column) into a shortened list of what we need to order that week. I have manually written in what i have been trying to achieve. (I have been trying to do something with INDEX, but have failed).

Thank you.

Weekly Booze Order.xlsx
ABCDEFGHIJKLM
1Target StockPre-ordersStock CountOrderBin No.What I would like:
21. COLOMBARD La Courtine 2018 V 12120Bin No.115Bin No.2
32. PINOT GRIGIO 2018 24915Bin No.214Bin No.3
43. CHARDONNAY 2018 614614Bin No.33Bin No.5
54h. DR LOOSEN ESTATE RIESLING 2018 [Half bottle] V 660Bin No.4 H1Bin No.6
65. PINOT GRIS Villa Wolf 2017 V 633Bin No.53Bin No.8
76. MACON BLANC Davayé 2018 651Bin No.61Bin No.9
87. SHORELINE 2017 660Bin No.73Bin No.10
98. RIOJA BLANCO 2018 633Bin No.8-3Bin No.10 H
109. BACCHUS BLOCK 2018 651Bin No.92Bin No.12
1110. SANCERRE Chavignol 2018 V 633Bin No.1020Bin No.13
1210. SANCERRE Chavignol 2018 V 69-3Bin No.10 H3Bin No.20
1312. CHABLIS 1er CRU Mont de Milieu 2018 V 642Bin No.123Bin No.23
1413. CHASSAGNE MONTRACHET Château de la Charrière 2015/16 6281420Bin No.131Bin No.34
1514. CHARDONNAY 2018 V 12120Bin No.1412Bin No.35
1615. SAUVIGNON BLANC Stablemate 2019 V 24240Bin No.1512Bin No.36
1716. VIOGNIER Serbal 2018660Bin No.1610Bin No.42
1817. SEMILLON Helmsford Vineyard 2016 660Bin No.171Bin No.44 H
1918. SAUVIGNON BLANC Green Lip 2016 V 660Bin No.182Bin No.50 H
2019h. CHARDONNAY Brightview 2013 [half bottle] V 660Bin No.19 H1Bin No.fino
2120. ZINFANDEL BLUSH ROSÉ 1293Bin No.201Bin No.amont
2221. LES TERRASSES ROSÉ 2018660Bin No.211Bin No.cream
2322. BEAUJOLAIS ROSÉ 2016 V660Bin No.221Bin No.Lyme Grape Gin
2423. MERLOT/SYRAH Les Terrasses 2018 24213Bin No.23
2524. MONTEPULCIANO D’ABRUZZO ModA 2017/18 660Bin No.24
2625. RHONE: DOMAINE DE MONTINE Gourmandises 2018 V 660Bin No.25i.e., only picking up from columns F&G when there is a positive number in Col. F
2726. CHÂTEAU TASSIN 2016 660Bin No.26Excluding any zero values or negative values.
2827. MASSAYA Le Colombier 2018 660Bin No.27
2928h. BEAUJOLAIS-VILLAGES 2016 [half bottle] V 660Bin No.28 H
3029. BROUILLY 2016 [organic] V 660Bin No.29
3130. RIOJA RESERVA 2015 660Bin No.30
3230. RIOJA RESERVA 2015 660Bin No.30 H
3331. CHATEAU LA TOUR DE BY 2010/12 660Bin No.31
3431h. CHATEAU LA TOUR DE BY 2010/13660Bin No.31 H
3532h. COTE DE BEAUNE Ladoix 2016 [half bottle660Bin No.32 H
3633. CHÂTEAUNEUF DU PAPE “Telegramme” 2016 V 660Bin No.33
3734. NUITS ST GEORGES Les Charmottes 2015 651Bin No.34
3835. SHIRAZ/ CABERNET Red Herring 2018 241212Bin No.35
3936. CABERNET/MERLOT 2018 V 241212Bin No.36
4037. MERLOT Stablemate 2018660Bin No.37
4138. CABERNET SAUVIGNON 2016/17 660Bin No.38
4239. MALBEC Altivo Vineyard Selection 2017 12120Bin No.39
4340. PINOTAGE 2019 660Bin No.40
4441. PINOT NOIR The Wire Walker 2018 660Bin No.41
4542. PROSECCO SPUMANTE BRUT NV V 12210Bin No.42
4643. CRÉMANT DE BOURGOGNE BRUT ROSÉ NV 660Bin No.43
4744. GREMILLET BRUT SÉLECTION NV 660Bin No.44
4844. GREMILLET BRUT SÉLECTION NV 651Bin No.44 H
4945. GREMILLET BRUT ROSÉ NV 660Bin No.45
5046. LANGHAM Culver Classic Cuvée BRUT NV 660Bin No.46
5147h. LATE HARVEST RIESLING 2017 [half bottle] 330Bin No.47
5248h. RUTHERGLEN LIQUEUR MUSCAT NV [half bottle330Bin No.48
5349h. SOMERSET POMONA [50cl bottle] 6.60 50cl 330Bin No.49
5450h. CHURCHILL’S LATE BOTTLED VINTAGE PORT 312Bin No.50 H
55Zuletta Fino211Bin No.fino
56Zuletta Amontilldo211Bin No.amont
57Zuletta Cream211Bin No.cream
58Lyme Bay Grapefruit Gin211Bin No.Lyme Grape Gin
Sheet1
Cell Formulas
RangeFormula
F2:F58F2=B2+C2-D2
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
=IF(ROW()-1<=COUNTIF($F$2:$F$58,">0"),INDEX($F$2:$F$58,AGGREGATE(14,6,($F$2:$F$58>0)*(ROW($F$2:$F$58)-1),ROW()-1)),"")
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
Thanks, just done.
 
Upvote 0
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJK
1Target StockPre-ordersStock CountOrderBin No.What I would like:
21. COLOMBARD La Courtine 2018 V 12120Bin No.115Bin No.2
32. PINOT GRIGIO 2018 24915Bin No.214Bin No.3
43. CHARDONNAY 2018 614614Bin No.33Bin No.5
54h. DR LOOSEN ESTATE RIESLING 2018 [Half bottle] V 660Bin No.4 H1Bin No.6
65. PINOT GRIS Villa Wolf 2017 V 633Bin No.53Bin No.8
76. MACON BLANC Davayé 2018 651Bin No.61Bin No.9
87. SHORELINE 2017 660Bin No.73Bin No.10
98. RIOJA BLANCO 2018 633Bin No.8-3Bin No.10 H
109. BACCHUS BLOCK 2018 651Bin No.92Bin No.12
1110. SANCERRE Chavignol 2018 V 633Bin No.1020Bin No.13
1210. SANCERRE Chavignol 2018 V 69-3Bin No.10 H3Bin No.20
1312. CHABLIS 1er CRU Mont de Milieu 2018 V 642Bin No.123Bin No.23
1413. CHASSAGNE MONTRACHET Château de la Charrière 2015/16 6281420Bin No.131Bin No.34
1514. CHARDONNAY 2018 V 12120Bin No.1412Bin No.35
1615. SAUVIGNON BLANC Stablemate 2019 V 24240Bin No.1512Bin No.36
1716. VIOGNIER Serbal 2018660Bin No.1610Bin No.42
1817. SEMILLON Helmsford Vineyard 2016 660Bin No.171Bin No.44 H
1918. SAUVIGNON BLANC Green Lip 2016 V 660Bin No.182Bin No.50 H
2019h. CHARDONNAY Brightview 2013 [half bottle] V 660Bin No.19 H1Bin No.fino
2120. ZINFANDEL BLUSH ROSÉ 1293Bin No.201Bin No.amont
2221. LES TERRASSES ROSÉ 2018660Bin No.211Bin No.cream
2322. BEAUJOLAIS ROSÉ 2016 V660Bin No.221Bin No.Lyme Grape Gin
2423. MERLOT/SYRAH Les Terrasses 2018 24213Bin No.23
2524. MONTEPULCIANO D’ABRUZZO ModA 2017/18 660Bin No.24
2625. RHONE: DOMAINE DE MONTINE Gourmandises 2018 V 660Bin No.25
2726. CHÂTEAU TASSIN 2016 660Bin No.26
2827. MASSAYA Le Colombier 2018 660Bin No.27
2928h. BEAUJOLAIS-VILLAGES 2016 [half bottle] V 660Bin No.28 H
3029. BROUILLY 2016 [organic] V 660Bin No.29
3130. RIOJA RESERVA 2015 660Bin No.30
3230. RIOJA RESERVA 2015 660Bin No.30 H
3331. CHATEAU LA TOUR DE BY 2010/12 660Bin No.31
3431h. CHATEAU LA TOUR DE BY 2010/13660Bin No.31 H
3532h. COTE DE BEAUNE Ladoix 2016 [half bottle660Bin No.32 H
3633. CHÂTEAUNEUF DU PAPE “Telegramme” 2016 V 660Bin No.33
3734. NUITS ST GEORGES Les Charmottes 2015 651Bin No.34
3835. SHIRAZ/ CABERNET Red Herring 2018 241212Bin No.35
3936. CABERNET/MERLOT 2018 V 241212Bin No.36
4037. MERLOT Stablemate 2018660Bin No.37
4138. CABERNET SAUVIGNON 2016/17 660Bin No.38
4239. MALBEC Altivo Vineyard Selection 2017 12120Bin No.39
4340. PINOTAGE 2019 660Bin No.40
4441. PINOT NOIR The Wire Walker 2018 660Bin No.41
4542. PROSECCO SPUMANTE BRUT NV V 12210Bin No.42
4643. CRÉMANT DE BOURGOGNE BRUT ROSÉ NV 660Bin No.43
4744. GREMILLET BRUT SÉLECTION NV 660Bin No.44
4844. GREMILLET BRUT SÉLECTION NV 651Bin No.44 H
4945. GREMILLET BRUT ROSÉ NV 660Bin No.45
5046. LANGHAM Culver Classic Cuvée BRUT NV 660Bin No.46
5147h. LATE HARVEST RIESLING 2017 [half bottle] 330Bin No.47
5248h. RUTHERGLEN LIQUEUR MUSCAT NV [half bottle330Bin No.48
5349h. SOMERSET POMONA [50cl bottle] 6.60 50cl 330Bin No.49
5450h. CHURCHILL’S LATE BOTTLED VINTAGE PORT 312Bin No.50 H
55Zuletta Fino211Bin No.fino
56Zuletta Amontilldo211Bin No.amont
57Zuletta Cream211Bin No.cream
58Lyme Bay Grapefruit Gin211Bin No.Lyme Grape Gin
59
Lists
Cell Formulas
RangeFormula
J2:K23J2=FILTER(F2:G58,F2:F58<>0)
F2:F58F2=B2+C2-D2
Dynamic array formulas.
 
Upvote 0
Solution
mart37 said:
=IF(ROW()-1<=COUNTIF($F$2:$F$58,">0"),INDEX($F$2:$F$58,AGGREGATE(14,6,($F$2:$F$58>0)*(ROW($F$2:$F$58)-1),ROW()-1)),"")
Click to expand...
Fluff said:
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJK
1Target StockPre-ordersStock CountOrderBin No.What I would like:
21. COLOMBARD La Courtine 2018 V 12120Bin No.115Bin No.2
32. PINOT GRIGIO 2018 24915Bin No.214Bin No.3
43. CHARDONNAY 2018 614614Bin No.33Bin No.5
54h. DR LOOSEN ESTATE RIESLING 2018 [Half bottle] V 660Bin No.4 H1Bin No.6
65. PINOT GRIS Villa Wolf 2017 V 633Bin No.53Bin No.8
76. MACON BLANC Davayé 2018 651Bin No.61Bin No.9
87. SHORELINE 2017 660Bin No.73Bin No.10
98. RIOJA BLANCO 2018 633Bin No.8-3Bin No.10 H
109. BACCHUS BLOCK 2018 651Bin No.92Bin No.12
1110. SANCERRE Chavignol 2018 V 633Bin No.1020Bin No.13
1210. SANCERRE Chavignol 2018 V 69-3Bin No.10 H3Bin No.20
1312. CHABLIS 1er CRU Mont de Milieu 2018 V 642Bin No.123Bin No.23
1413. CHASSAGNE MONTRACHET Château de la Charrière 2015/16 6281420Bin No.131Bin No.34
1514. CHARDONNAY 2018 V 12120Bin No.1412Bin No.35
1615. SAUVIGNON BLANC Stablemate 2019 V 24240Bin No.1512Bin No.36
1716. VIOGNIER Serbal 2018660Bin No.1610Bin No.42
1817. SEMILLON Helmsford Vineyard 2016 660Bin No.171Bin No.44 H
1918. SAUVIGNON BLANC Green Lip 2016 V 660Bin No.182Bin No.50 H
2019h. CHARDONNAY Brightview 2013 [half bottle] V 660Bin No.19 H1Bin No.fino
2120. ZINFANDEL BLUSH ROSÉ 1293Bin No.201Bin No.amont
2221. LES TERRASSES ROSÉ 2018660Bin No.211Bin No.cream
2322. BEAUJOLAIS ROSÉ 2016 V660Bin No.221Bin No.Lyme Grape Gin
2423. MERLOT/SYRAH Les Terrasses 2018 24213Bin No.23
2524. MONTEPULCIANO D’ABRUZZO ModA 2017/18 660Bin No.24
2625. RHONE: DOMAINE DE MONTINE Gourmandises 2018 V 660Bin No.25
2726. CHÂTEAU TASSIN 2016 660Bin No.26
2827. MASSAYA Le Colombier 2018 660Bin No.27
2928h. BEAUJOLAIS-VILLAGES 2016 [half bottle] V 660Bin No.28 H
3029. BROUILLY 2016 [organic] V 660Bin No.29
3130. RIOJA RESERVA 2015 660Bin No.30
3230. RIOJA RESERVA 2015 660Bin No.30 H
3331. CHATEAU LA TOUR DE BY 2010/12 660Bin No.31
3431h. CHATEAU LA TOUR DE BY 2010/13660Bin No.31 H
3532h. COTE DE BEAUNE Ladoix 2016 [half bottle660Bin No.32 H
3633. CHÂTEAUNEUF DU PAPE “Telegramme” 2016 V 660Bin No.33
3734. NUITS ST GEORGES Les Charmottes 2015 651Bin No.34
3835. SHIRAZ/ CABERNET Red Herring 2018 241212Bin No.35
3936. CABERNET/MERLOT 2018 V 241212Bin No.36
4037. MERLOT Stablemate 2018660Bin No.37
4138. CABERNET SAUVIGNON 2016/17 660Bin No.38
4239. MALBEC Altivo Vineyard Selection 2017 12120Bin No.39
4340. PINOTAGE 2019 660Bin No.40
4441. PINOT NOIR The Wire Walker 2018 660Bin No.41
4542. PROSECCO SPUMANTE BRUT NV V 12210Bin No.42
4643. CRÉMANT DE BOURGOGNE BRUT ROSÉ NV 660Bin No.43
4744. GREMILLET BRUT SÉLECTION NV 660Bin No.44
4844. GREMILLET BRUT SÉLECTION NV 651Bin No.44 H
4945. GREMILLET BRUT ROSÉ NV 660Bin No.45
5046. LANGHAM Culver Classic Cuvée BRUT NV 660Bin No.46
5147h. LATE HARVEST RIESLING 2017 [half bottle] 330Bin No.47
5248h. RUTHERGLEN LIQUEUR MUSCAT NV [half bottle330Bin No.48
5349h. SOMERSET POMONA [50cl bottle] 6.60 50cl 330Bin No.49
5450h. CHURCHILL’S LATE BOTTLED VINTAGE PORT 312Bin No.50 H
55Zuletta Fino211Bin No.fino
56Zuletta Amontilldo211Bin No.amont
57Zuletta Cream211Bin No.cream
58Lyme Bay Grapefruit Gin211Bin No.Lyme Grape Gin
59
Lists
Cell Formulas
RangeFormula
J2:K23J2=FILTER(F2:G58,F2:F58<>0)
F2:F58F2=B2+C2-D2
Dynamic array formulas.
Click to expand...

Fluff said:
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJK
1Target StockPre-ordersStock CountOrderBin No.What I would like:
21. COLOMBARD La Courtine 2018 V 12120Bin No.115Bin No.2
32. PINOT GRIGIO 2018 24915Bin No.214Bin No.3
43. CHARDONNAY 2018 614614Bin No.33Bin No.5
54h. DR LOOSEN ESTATE RIESLING 2018 [Half bottle] V 660Bin No.4 H1Bin No.6
65. PINOT GRIS Villa Wolf 2017 V 633Bin No.53Bin No.8
76. MACON BLANC Davayé 2018 651Bin No.61Bin No.9
87. SHORELINE 2017 660Bin No.73Bin No.10
98. RIOJA BLANCO 2018 633Bin No.8-3Bin No.10 H
109. BACCHUS BLOCK 2018 651Bin No.92Bin No.12
1110. SANCERRE Chavignol 2018 V 633Bin No.1020Bin No.13
1210. SANCERRE Chavignol 2018 V 69-3Bin No.10 H3Bin No.20
1312. CHABLIS 1er CRU Mont de Milieu 2018 V 642Bin No.123Bin No.23
1413. CHASSAGNE MONTRACHET Château de la Charrière 2015/16 6281420Bin No.131Bin No.34
1514. CHARDONNAY 2018 V 12120Bin No.1412Bin No.35
1615. SAUVIGNON BLANC Stablemate 2019 V 24240Bin No.1512Bin No.36
1716. VIOGNIER Serbal 2018660Bin No.1610Bin No.42
1817. SEMILLON Helmsford Vineyard 2016 660Bin No.171Bin No.44 H
1918. SAUVIGNON BLANC Green Lip 2016 V 660Bin No.182Bin No.50 H
2019h. CHARDONNAY Brightview 2013 [half bottle] V 660Bin No.19 H1Bin No.fino
2120. ZINFANDEL BLUSH ROSÉ 1293Bin No.201Bin No.amont
2221. LES TERRASSES ROSÉ 2018660Bin No.211Bin No.cream
2322. BEAUJOLAIS ROSÉ 2016 V660Bin No.221Bin No.Lyme Grape Gin
2423. MERLOT/SYRAH Les Terrasses 2018 24213Bin No.23
2524. MONTEPULCIANO D’ABRUZZO ModA 2017/18 660Bin No.24
2625. RHONE: DOMAINE DE MONTINE Gourmandises 2018 V 660Bin No.25
2726. CHÂTEAU TASSIN 2016 660Bin No.26
2827. MASSAYA Le Colombier 2018 660Bin No.27
2928h. BEAUJOLAIS-VILLAGES 2016 [half bottle] V 660Bin No.28 H
3029. BROUILLY 2016 [organic] V 660Bin No.29
3130. RIOJA RESERVA 2015 660Bin No.30
3230. RIOJA RESERVA 2015 660Bin No.30 H
3331. CHATEAU LA TOUR DE BY 2010/12 660Bin No.31
3431h. CHATEAU LA TOUR DE BY 2010/13660Bin No.31 H
3532h. COTE DE BEAUNE Ladoix 2016 [half bottle660Bin No.32 H
3633. CHÂTEAUNEUF DU PAPE “Telegramme” 2016 V 660Bin No.33
3734. NUITS ST GEORGES Les Charmottes 2015 651Bin No.34
3835. SHIRAZ/ CABERNET Red Herring 2018 241212Bin No.35
3936. CABERNET/MERLOT 2018 V 241212Bin No.36
4037. MERLOT Stablemate 2018660Bin No.37
4138. CABERNET SAUVIGNON 2016/17 660Bin No.38
4239. MALBEC Altivo Vineyard Selection 2017 12120Bin No.39
4340. PINOTAGE 2019 660Bin No.40
4441. PINOT NOIR The Wire Walker 2018 660Bin No.41
4542. PROSECCO SPUMANTE BRUT NV V 12210Bin No.42
4643. CRÉMANT DE BOURGOGNE BRUT ROSÉ NV 660Bin No.43
4744. GREMILLET BRUT SÉLECTION NV 660Bin No.44
4844. GREMILLET BRUT SÉLECTION NV 651Bin No.44 H
4945. GREMILLET BRUT ROSÉ NV 660Bin No.45
5046. LANGHAM Culver Classic Cuvée BRUT NV 660Bin No.46
5147h. LATE HARVEST RIESLING 2017 [half bottle] 330Bin No.47
5248h. RUTHERGLEN LIQUEUR MUSCAT NV [half bottle330Bin No.48
5349h. SOMERSET POMONA [50cl bottle] 6.60 50cl 330Bin No.49
5450h. CHURCHILL’S LATE BOTTLED VINTAGE PORT 312Bin No.50 H
55Zuletta Fino211Bin No.fino
56Zuletta Amontilldo211Bin No.amont
57Zuletta Cream211Bin No.cream
58Lyme Bay Grapefruit Gin211Bin No.Lyme Grape Gin
59
Lists
Cell Formulas
RangeFormula
J2:K23J2=FILTER(F2:G58,F2:F58<>0)
F2:F58F2=B2+C2-D2
Dynamic array formulas.
Click to expand...
That's perfect, thank you. It does exactly what i wanted!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Deleting rows that are not on a second list
2
Replies
11
Views
809
kadain
K

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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