List a range of numbers excluding those in another column

Red over White

Board Regular
Joined
Jul 16, 2011
Messages
141
Office Version
  1. 365
Platform
  1. MacOS
I’m looking to develop a doughnut graph, but first need to get the figures in the right order.

The main area I’m struggling with is Column G. What I’m looking for is a consecutive list of numbers that omits those numbers in Column F (there will be no more than five such numbers in this column).

Should the numbers change in Column F I can alter what is in Columns H to K by macro, but if there is a formula that would be an added bonus!

Thanks

Doughnut 2.xlsx
ABCDEFGHIJ
2
3Example 10.3%121.182%43Example 221.2%
4Example 221.2%219.345%235Example 2119.3%
5Example 30.3%317.939%166Example 1417.9%
6Example 42.3%4  7Example 1 0.3%
7Example 51.1%5  8Example 3 0.3%
8Example 66.6%6  9Example 4 2.3%
9Example 71.5%7 10Example 5 1.1%
10Example 80.6%8 11Example 6 6.6%
11Example 90.5%9 12Example 7 1.5%
12Example 100.6%10 13Example 8 0.6%
13Example 116.9%11 14Example 9 0.5%
14Example 120.7%12 15Example 10 0.6%
15Example 130.5%13 17Example 11 6.9%
16Example 1417.9%14 18Example 12 0.7%
17Example 150.3%15 19Example 13 0.5%
18Example 163.2%16 20Example 15 0.3%
19Example 170.3%17 21Example 16 3.2%
20Example 181.2%18 22Example 17 0.3%
21Example 192.6%19 24Example 18 1.2%
22Example 200.8%20 25Example 19 2.6%
23Example 2119.3%21 26Example 20 0.8%
24Example 220.2%22 27Example 220.2%
25Example 230.9%23 28Example 230.9%
26Example 243.4%24 29Example 243.4%
27Example 250.3%25 30Example 250.3%
28Example 264.4%26 31Example 264.4%
29Example 270.7%27 32Example 270.7%
30Example 280.8%28 33Example 280.8%
3129 
3230 
3331
Exclusion
Cell Formulas
RangeFormula
E3:E8E3=IF(LARGE($B$3:$B$30,D3)>E$1,LARGE($B$3:$B$30,D3),"")
I3:I23I3=IFERROR(IF(INDIRECT("B"&F3)>E$1,INDIRECT("B"&F3),""),"")
H3:H5H3=IF(F3="","",INDIRECT("A"&F3))
H6:H30H6=INDIRECT("A"&SMALL($G$3:$G$27,D3))
J6:J30J6=IFERROR(IF(INDIRECT("B"&G3)<E$1,INDIRECT("B"&G3),""),"")
F3:F32F3=IFERROR(SUM(MATCH(E3,$B$3:$B$30,0)+2),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3Cell Value<=0.15%textNO
 

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