Lookup If Blank Or only have certain value

SColyn

New Member
Joined
May 12, 2016
Messages
17
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 TypeAsset CodeCustomer Plant
648536-050090000045698DZC-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

New Member
Joined
May 12, 2016
Messages
17
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
with Power Query (not formula)

Asset TypeAsset CodeCustomer PlantAsset TypeAsset CodeCustomer PlantAsset TypeCustomer PlantAsset Code.1Asset Code.2
648536-0500
90000045698​
DZC-NCA Rou648536-0500
90000045698​
DZC-NCA Rou648536-0500DZC9000004569890000068725
648536-0500DZC648536-0500
90000068725​
DZC-RTH NPA648582-0100DZC900000789625
648536-0500ZAC648536-0500
90000016792​
ZAC-KLO JHB648536-0500ZAC90000016792
648582-0100
900000789625​
DZC-NCA KZN648536-0500
90000002267​
PKC-SHA 001648536-0500PKC90000002267
648536-0500
90000068911​
TRC-TRK 582648536-0500TRC90000068911
658389-0100
90000052740​
ZAC-PKC DBN658389-0100ZAC90000052740
648582-0100
900000789625​
DZC-NCA KZN

is that what you want?

btw. this is NOT vba!
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,050
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
May 12, 2016
Messages
17
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.
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
I Append tables not Merge

see M-code

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]

Code:
[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]

Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,554
Messages
5,548,719
Members
410,867
Latest member
Dhanas
Top