# List of unique PO numbers for orders that havent met target

#### turtle81

##### New Member
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

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
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
you can try PowerQuery (Get&Transform)

 Table LV Table UV TableResult Lower Value Upper Value Result 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],
#"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
or with two tables only

Code:
``````[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"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 LU Table Result Lower Value Upper Value Result 95​ 105​ 300​ 303​ Table3 306​ PO NO. % 300​ 90​ 301​ 100​ 302​ 102​ 303​ 106​ 304​ 96​ 305​ 99​ 306​ 83​

Last edited:

Replies
1
Views
45
Replies
10
Views
118
Replies
0
Views
66
Replies
6
Views
237
Replies
6
Views
56