List of unique PO numbers for orders that havent met target

turtle81

New Member
Joined
Feb 19, 2019
Messages
9
I have a list of order numbers in column A, in column B I have a list of corresponding %s (from a formula)

I want to create a list of all the PO numbers that haven’t hit between 95 & 105 (for example, these would be in a cell so I could change them) basically a list of all the PO numbers that haven’t hit target


Lower Value
Upper Value
95.00
105.00

<tbody>
</tbody>


A
B
PO NO.
%
300
90
301
100
302
102
303
106
304
96
305
99
306
83

<tbody>
</tbody>




So the"list" would be,
300
303
306


as all these havent hit target







 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
Hi Turtle,

in Column C you can use an IF statement nesting an OR condition to check if the number is either less than 95 or greater than 105, if so then add the PO No to the cell, otherwise make it blank.

Then you can use a TEXTJOIN formula to gather all the PO's in to one cell.

E.g. try this formula in cell C2:
IF(OR(B2<95,B2>105),A2,"")

Drag that down to C8,

Then in Cell C10 try:
TEXTJOIN(",",TRUE,C2:C8)

Which will then return "300,303,306" based on your example.

I have assumed here that the data is entered in Cells A2:B8 with headers in A1 & B1 as "Po No." & "%"
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,778
you can try PowerQuery (Get&Transform)

Table LVTable UVTableResult
Lower ValueUpper ValueResult
95​
100​
300​
302​
Table3
303​
PO NO.%
306​
300​
90​
301​
100​
302​
102​
303​
106​
304​
96​
305​
99​
306​
83​

load all table into PowerQuery Editor
then for TableSource use M-code:
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "LV", each LV),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "UV", each UV),
    #"Expanded LV" = Table.ExpandTableColumn(#"Added Custom1", "LV", {"Lower Value"}, {"Lower Value"}),
    #"Expanded UV" = Table.ExpandTableColumn(#"Expanded LV", "UV", {"Upper Value"}, {"Upper Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded UV", "Result", each if [#"%"] < [Lower Value] then [#"PO NO."] else if [#"%"] > [Upper Value] then [#"PO NO."] else ""),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Result] <> "")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Result"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Result", Order.Ascending}})
in
    #"Sorted Rows"[/SIZE]
if you change any value in LV or/and UV then use Ctrl+Alt+F5 or right click on green table and choose Refresh
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,778
or with two tables only

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "LU", each LU),
    #"Expanded LU" = Table.ExpandTableColumn(#"Added Custom", "LU", {"Lower Value", "Upper Value"}, {"Lower Value", "Upper Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded LU", "Result", each if [#"%"] < [Lower Value] then [#"PO NO."] else if [#"%"] > [Upper Value] then [#"PO NO."] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Result] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Result", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Result"})
in
    #"Removed Other Columns"[/SIZE]

Table LUTable Result
Lower ValueUpper ValueResult
95​
105​
300​
303​
Table3
306​
PO NO.%
300​
90​
301​
100​
302​
102​
303​
106​
304​
96​
305​
99​
306​
83​
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,457
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">PO NO.</td><td style=";">%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Lower Value</td><td style="text-align: right;;">95</td><td style="text-align: right;;">300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">300</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Upper Value</td><td style="text-align: right;;">105</td><td style="text-align: right;;">303</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">301</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">306</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">302</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">303</td><td style="text-align: right;;">106</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">304</td><td style="text-align: right;;">96</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">305</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">306</td><td style="text-align: right;;">83</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Cover</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H1</th><td style="text-align:left">=INDEX(<font color="Blue">$A$2:$A$8,AGGREGATE(<font color="Red">15,6,ROW(<font color="Green">$A$2:$A$8</font>)-ROW(<font color="Green">$A$2</font>)+1/(<font color="Green">(<font color="Purple">$B$2:$B$8<$G$1</font>)+(<font color="Purple">$B$2:$B$8>$G$2</font>)</font>),ROWS(<font color="Green">$1:1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,410
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top