Forum Discussion
Return values from some cells if other cells meet criteria
I am trying to return a list of computer names and possibly Windows build version and memory available if build version is less than X and memory is less than X.
So ideally on Sheet 1 (Overview) I would show the following:
So if the computer has less than say 15 gig of ram AND has older than Build # 19042 build number of Windows we would return "Replace" on it.**
If the computer has over 15 gigs of ram and the Build # is older than 19042 we would say "Reimage"
6 Replies
With some assumptions (builds are numbers, etc) you may use Power Query
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Removed Other Columns" = Table.SelectColumns( Source, {"Device Name", "Build", "Memory"}), #"Changed Type" = Table.TransformColumnTypes( #"Removed Other Columns", { {"Device Name", type text}, {"Build", Int64.Type}, {"Memory", Int64.Type} }), #"Added Custom" = Table.AddColumn( #"Changed Type", "Action", each if [Build] < 19042 then if [Memory] < 15000 then "Replace" else "Reimage" else "Nothing to do") in #"Added Custom"Results is
So I want to display data ONLY if the Memory and Build #'s meet the criteria.
So I don't want any "Nothing to do" columns, I only want "Replace" or "Reimage".
That's simple filter in Power Query transformation
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Removed Other Columns" = Table.SelectColumns( Source, {"Device Name", "Build", "Memory"}), #"Changed Type" = Table.TransformColumnTypes( #"Removed Other Columns", { {"Device Name", type text}, {"Build", Int64.Type}, {"Memory", Int64.Type} }), #"Added Custom" = Table.AddColumn( #"Changed Type", "Action", each if [Build] < 19042 then if [Memory] < 15000 then "Replace" else "Reimage" else "Nothing to do"), #"Filtered Rows" = Table.SelectRows( #"Added Custom", each ([Action] <> "Nothing to do")) in #"Filtered Rows"it gives
- So I only want to return values if the "Memory" and "Build" columns meet the criteria.
I don't what lines that say "No Problem".What should the formula return if the build is 19042 or higher? Reimage? If so:
IF(AND(C4<19042,D4<15*1024),"Replace","Reimage")
