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







 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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." & "%"
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
How about


Excel 2013/2016
ABCDEFGH
1PO NO.%Lower Value95300
230090Upper Value105303
3301100306
4302102
5303106
630496
730599
830683
Cover
Cell Formulas
RangeFormula
H1=INDEX($A$2:$A$8,AGGREGATE(15,6,ROW($A$2:$A$8)-ROW($A$2)+1/(($B$2:$B$8<$G$1)+($B$2:$B$8>$G$2)),ROWS($1:1)))
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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