Lookup If Blank Or only have certain value

S

SColyn

New Member
Joined
May 12, 2016
Messages
19
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
​
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Asset Type[/TD]
[TD]Asset Code[/TD]
[TD]Customer Plant[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000045698[/TD]
[TD]DZC-NCA Rou[/TD]
[/TR]
[TR]
[TD]
648536-0500​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]DZC[/TD]
[/TR]
[TR]
[TD]
648536-0500​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]ZAC[/TD]
[/TR]
[TR]
[TD]648582-0100[/TD]
[TD]
900000789625​
[/TD]
[TD]<strike></strike>
DZC-NCA KZN​
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
​
Sheet 2
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Asset Type<strike></strike>
[/TD]
[TD]Asset Code<strike></strike>
[/TD]
[TD]Customer Plant[/TD]
[/TR]
[TR]
[TD]
648536-0500​
<strike></strike>
[/TD]
[TD]<strike></strike>
90000045698​
<strike></strike>
[/TD]
[TD]<strike></strike>
DZC-NCA Rou​
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<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>
[/TD]
[TD]<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>
[/TD]
[TD]DZC-RTH NPA[/TD]
[/TR]
[TR]
[TD]
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>
[/TD]
[TD]<strike></strike>
90000016792​
[/TD]
[TD]ZAC-KLO JHB[/TD]
[/TR]
[TR]
[TD]
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>
[/TD]
[TD]<strike></strike>
90000002267​
[/TD]
[TD]PKC-SHA 001[/TD]
[/TR]
[TR]
[TD]
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>
[/TD]
[TD]<strike></strike>
90000068911​
[/TD]
[TD]TRC-TRK 582[/TD]
[/TR]
[TR]
[TD]
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>
[/TD]
[TD]<strike></strike>
90000052740​
[/TD]
[TD]ZAC-PKC DBN[/TD]
[/TR]
[TR]
[TD]
648582-0100​
<strike></strike>
[/TD]
[TD]<strike></strike>
900000789625​
<strike></strike>
[/TD]
[TD]<strike></strike>
DZC-NCA KZN​
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
Any assistance would be greatly appreciated.
Susan
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
with Power Query (not formula)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Asset Type[/td][td=bgcolor:#5B9BD5]Asset Code[/td][td=bgcolor:#5B9BD5]Customer Plant[/td][td][/td][td=bgcolor:#5B9BD5]Asset Type[/td][td=bgcolor:#5B9BD5]Asset Code[/td][td=bgcolor:#5B9BD5]Customer Plant[/td][td][/td][td=bgcolor:#70AD47]Asset Type[/td][td=bgcolor:#70AD47]Customer Plant[/td][td=bgcolor:#70AD47]Asset Code.1[/td][td=bgcolor:#70AD47]Asset Code.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]648536-0500[/td][td=bgcolor:#DDEBF7]
90000045698​
[/td][td=bgcolor:#DDEBF7]DZC-NCA Rou[/td][td][/td][td=bgcolor:#DDEBF7]648536-0500[/td][td=bgcolor:#DDEBF7]
90000045698​
[/td][td=bgcolor:#DDEBF7]DZC-NCA Rou[/td][td][/td][td=bgcolor:#E2EFDA]648536-0500[/td][td=bgcolor:#E2EFDA]DZC[/td][td=bgcolor:#E2EFDA]90000045698[/td][td=bgcolor:#E2EFDA]90000068725[/td][/tr]

[tr=bgcolor:#FFFFFF][td]648536-0500[/td][td][/td][td]DZC[/td][td][/td][td]648536-0500[/td][td]
90000068725​
[/td][td]DZC-RTH NPA[/td][td][/td][td]648582-0100[/td][td]DZC[/td][td]900000789625[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]648536-0500[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]ZAC[/td][td][/td][td=bgcolor:#DDEBF7]648536-0500[/td][td=bgcolor:#DDEBF7]
90000016792​
[/td][td=bgcolor:#DDEBF7]ZAC-KLO JHB[/td][td][/td][td=bgcolor:#E2EFDA]648536-0500[/td][td=bgcolor:#E2EFDA]ZAC[/td][td=bgcolor:#E2EFDA]90000016792[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]648582-0100[/td][td]
900000789625​
[/td][td]DZC-NCA KZN[/td][td][/td][td]648536-0500[/td][td]
90000002267​
[/td][td]PKC-SHA 001[/td][td][/td][td]648536-0500[/td][td]PKC[/td][td]90000002267[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]648536-0500[/td][td=bgcolor:#DDEBF7]
90000068911​
[/td][td=bgcolor:#DDEBF7]TRC-TRK 582[/td][td][/td][td=bgcolor:#E2EFDA]648536-0500[/td][td=bgcolor:#E2EFDA]TRC[/td][td=bgcolor:#E2EFDA]90000068911[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]658389-0100[/td][td]
90000052740​
[/td][td]ZAC-PKC DBN[/td][td][/td][td]658389-0100[/td][td]ZAC[/td][td]90000052740[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]648582-0100[/td][td=bgcolor:#DDEBF7]
900000789625​
[/td][td=bgcolor:#DDEBF7]DZC-NCA KZN[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

is that what you want?

btw. this is NOT vba!
 
Last edited:
Upvote 0
SColyn said:
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.
Click to expand...

Maybe if you had responded to the previous reply from Special-K99, you would have had it solved sooner.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Formula for conditional formating - NOT AS EASY AS IT SEEMS
Replies
3
Views
908
Eric W
Eric W
S
  • Locked
  • Question Question
Lookup If Blank Or only have certain value
Replies
2
Views
709
Peter_SSs
Peter_SSs
J
  • Question Question
COUNTIFS Hours and minutes in range
Replies
3
Views
900
CyrusTheVirus
CyrusTheVirus
E
  • Question Question
VBA autofilter 1004 No cells were found
Replies
5
Views
1K
Fluff
Fluff
R
  • Question Question
MAXIF and return another column
2 3
Replies
21
Views
3K
Aryatect
Aryatect

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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