List of unique PO numbers for orders that havent met target

T

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


[TABLE="width: 500"]
<tbody>[TR]
[TD]Lower Value
[/TD]
[TD]Upper Value
[/TD]
[/TR]
[TR]
[TD]95.00
[/TD]
[TD]105.00
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]PO NO.
[/TD]
[TD]%
[/TD]
[/TR]
[TR]
[TD]300
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]301
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]302
[/TD]
[TD]102
[/TD]
[/TR]
[TR]
[TD]303
[/TD]
[TD]106
[/TD]
[/TR]
[TR]
[TD]304
[/TD]
[TD]96
[/TD]
[/TR]
[TR]
[TD]305
[/TD]
[TD]99
[/TD]
[/TR]
[TR]
[TD]306
[/TD]
[TD]83
[/TD]
[/TR]
</tbody>[/TABLE]




So the"list" would be,
300
303
306

as all these havent hit target







 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Table LV[/td][td]Table UV[/td][td][/td][td]TableResult[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Lower Value[/td][td=bgcolor:#5B9BD5]Upper Value[/td][td][/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
95​
[/td][td=bgcolor:#DDEBF7]
100​
[/td][td][/td][td=bgcolor:#E2EFDA]
300​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]
302​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Table3[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]
303​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]PO NO.[/td][td=bgcolor:#5B9BD5]%[/td][td][/td][td]
306​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
300​
[/td][td=bgcolor:#DDEBF7]
90​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
301​
[/td][td]
100​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
302​
[/td][td=bgcolor:#DDEBF7]
102​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
303​
[/td][td]
106​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
304​
[/td][td=bgcolor:#DDEBF7]
96​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
305​
[/td][td]
99​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
306​
[/td][td=bgcolor:#DDEBF7]
83​
[/td][td][/td][td][/td][/tr]
[/table]

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="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Table LU[/td][td][/td][td][/td][td]Table Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Lower Value[/td][td=bgcolor:#5B9BD5]Upper Value[/td][td][/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
95​
[/td][td=bgcolor:#DDEBF7]
105​
[/td][td][/td][td=bgcolor:#E2EFDA]
300​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]
303​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Table3[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]
306​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]PO NO.[/td][td=bgcolor:#5B9BD5]%[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
300​
[/td][td=bgcolor:#DDEBF7]
90​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
301​
[/td][td]
100​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
302​
[/td][td=bgcolor:#DDEBF7]
102​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
303​
[/td][td]
106​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
304​
[/td][td=bgcolor:#DDEBF7]
96​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
305​
[/td][td]
99​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
306​
[/td][td=bgcolor:#DDEBF7]
83​
[/td][td][/td][td][/td][/tr]
[/table]
 
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
You must log in or register to reply here.

Similar threads

J
  • Solved
how to random all numbers
Replies
7
Views
280
Fluff
Fluff
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
297
Cquake
C
D
  • Question Question
Duplicate cells not formatting problem.
Replies
6
Views
278
etaf
etaf
M
  • Solved
Help with segregating Excel Email List
Replies
3
Views
4K
Peter_SSs
Peter_SSs
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
209
StephenCrump
S

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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