Search in multi condition

TH123

New Member
Joined
Nov 15, 2022
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have data base as below

Book1
ABC
1OrderNumberColorDescriptionApproval date
24701740244Multi1-Jan
34701740244,4701740244Yellow,Blue3-Jan
Sheet1


And would like to get result following last column as below. Could you pls support if any Excel formula works in this case?

Book1
FGH
1OrderNumberColorDescriptionApproval date - result s/b
24701740244Blue3-Jan
34701740244Yellow3-Jan
44701740244Multi1-Jan
Sheet1
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Powerquery will do this for you.


Quick Steps to create
  1. Highlight your data
  2. Go to Data/From Table/Range
  3. Highlight your OrderNumber, split column by comma,
    1. Advanced option: Split Into Rows
  4. Do the same for the Color Description
  5. Close and load to a table and put it where ever you want

Here is the mCode

Power Query:
// Table4
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"OrderNumber", type text}}, "en-US"), {{"OrderNumber", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "OrderNumber"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"ColorDesc", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ColorDesc")
in
    #"Split Column by Delimiter1"
 
Upvote 0
Powerquery will do this for you.


Quick Steps to create
  1. Highlight your data
  2. Go to Data/From Table/Range
  3. Highlight your OrderNumber, split column by comma,
    1. Advanced option: Split Into Rows
  4. Do the same for the Color Description
  5. Close and load to a table and put it where ever you want

Here is the mCode

Power Query:
// Table4
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"OrderNumber", type text}}, "en-US"), {{"OrderNumber", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "OrderNumber"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"ColorDesc", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ColorDesc")
in
    #"Split Column by Delimiter1"
Thank you. Seems my question was not clear enough. I'd like to look up/ search from data based to exist chart and result s/b same as column H. Could you pls advise if any excel formula can help in this case?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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