Return values from some cells if other cells meet criteria

New Contributor

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.


Lookup-Reimage-Replace.jpg
So ideally on Sheet 1 (Overview) I would show the following:

FailedWin10ReplaceReimage.jpg


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

@dlhtox74 

 

=IF(C4<19042,IF(D4<15*1024,"Replace","Reimage"),"No Problem")

@dlhtox74 

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

image.png

@Sergei Baklan 

 

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".

 

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".

@dlhtox74 

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

image.png

@dlhtox74 

What should the formula return if the build is 19042 or higher? Reimage? If so:

 

IF(AND(C4<19042,D4<15*1024),"Replace","Reimage")