Lookup If Blank Or only have certain value

[SColyn]

Good day,

I have a sheet with Asset Type code, Customer plant and Asset Number. The Asset Type code indicates the configuration on the machines and is not a unique number. The Customer plant is also not unique but Asset Number is unique. There can be numerous Asset numbers for one Asset Type code. I would like to include an Asset Number in the sheet horizontally, I have a Master data sheet with all our Asset numbers, Customer Plant and Asset Type code.​

The problem I am facing is, I only want to include the Asset number if the Asset Number field is blank and the Customer Plant field does not have a complete location. The locations which are incomplete with only have 3 letters (ZAC / SAC / IRC / EGC / PKC etc.)

I have tried some of the formulas but cannot get the result I am looking for.

Please assist.
Sheet 1
​

Asset Type	Asset Code	Customer Plant
648536-0500	90000045698	DZC-NCA Rou
648536-0500​ <strike></strike>	<strike></strike>	DZC
648536-0500​ <strike></strike>	<strike></strike>	ZAC
648582-0100	900000789625​	<strike></strike> DZC-NCA KZN​

<tbody>
</tbody>

<strike></strike>
​

Sheet 2

Asset Type<strike></strike>	Asset Code<strike></strike>	Customer Plant
648536-0500​ <strike></strike>	<strike></strike> 90000045698​ <strike></strike>	<strike></strike> DZC-NCA Rou​ <strike></strike>
<strike></strike> 648536-0500​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> 90000068725​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	DZC-RTH NPA
648536-0500​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> 90000016792​	ZAC-KLO JHB
648536-0500​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> 90000002267​	PKC-SHA 001
648536-0500​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> 90000068911​	TRC-TRK 582
658389-0100​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> 90000052740​	ZAC-PKC DBN
648582-0100​ <strike></strike>	<strike></strike> 900000789625​ <strike></strike>	<strike></strike> DZC-NCA KZN​ <strike></strike>

<tbody>
</tbody>

<strike></strike>
Any assistance would be greatly appreciated.
Susan

 

[SColyn]

Is there any one else who can help me with this. i really need a solution and I am sure there is a formula for this.

 

[sandy666]

with Power Query (not formula)

Asset Type	Asset Code	Customer Plant		Asset Type	Asset Code	Customer Plant		Asset Type	Customer Plant	Asset Code.1	Asset Code.2
648536-0500	90000045698​	DZC-NCA Rou		648536-0500	90000045698​	DZC-NCA Rou		648536-0500	DZC	90000045698	90000068725
648536-0500		DZC		648536-0500	90000068725​	DZC-RTH NPA		648582-0100	DZC	900000789625
648536-0500		ZAC		648536-0500	90000016792​	ZAC-KLO JHB		648536-0500	ZAC	90000016792
648582-0100	900000789625​	DZC-NCA KZN		648536-0500	90000002267​	PKC-SHA 001		648536-0500	PKC	90000002267
				648536-0500	90000068911​	TRC-TRK 582		648536-0500	TRC	90000068911
				658389-0100	90000052740​	ZAC-PKC DBN		658389-0100	ZAC	90000052740
				648582-0100	900000789625​	DZC-NCA KZN

is that what you want?

btw. this is NOT vba!

 

[jasonb75]

Is there any one else who can help me with this. i really need a solution and I am sure there is a formula for this.

Maybe if you had responded to the previous reply from Special-K99, you would have had it solved sooner.

 

[SColyn]

Hi,

Yes, that is the result I am looking for.

I know a little bit about Power Query. Please let me know which columns you selected when you merged the tables.

Thank you so much for your help.

 

[sandy666]

I Append tables not Merge

see M-code

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Asset Code", type text}}),
    Extract3 = Table.TransformColumns(Type, {{"Customer Plant", each Text.Start(_, 3), type text}})
in
    Extract3[/SIZE]

[SIZE=1]// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Asset Type", type text}, {"Asset Code", type text}, {"Customer Plant", type text}}),
    Extract3 = Table.TransformColumns(Type, {{"Customer Plant", each Text.Start(_, 3), type text}})
in
    Extract3[/SIZE]

[SIZE=1]// Append1
let
    Source = Table.Combine({Table1, Table2}),
    Filter = Table.SelectRows(Source, each ([Asset Code] <> null)),
    RD = Table.Distinct(Filter),
    Group = Table.Group(RD, {"Asset Type", "Customer Plant"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Asset Code", each List.Distinct(Table.Column([Count],"Asset Code"))),
    Extract = Table.TransformColumns(List, {"Asset Code", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    Split = Table.SplitColumn(Extract, "Asset Code", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Asset Code.1", "Asset Code.2"})
in
    Split[/SIZE]

it can be done with one Query but for clarity there are three