Challenge :) formula or VBA

[DavidG007]

I have an issue that I don't know how to easily resolve. I need to convert the following line of text.

Column 'C1' would typically be this (but will be multiple lengths);
Case Is = "400000","400100","400200","400300","400400","400500","400700","400900"

Column 'D2' would say 'Revenue'

What I need to be able to do is break down Column 'C1' and include the text in Column 'D2'

My final answer would look like this;

Column A Column B
400000 Revenue
400100 Revenue
400200 Revenue


etc etc.

The formula or code would then need to do exactly the same check on subsequent rows as the pattern

I really hope this makes sense and there are a few of you that fancy the challenge

massive thanks for any replies

 

[sandy666]

are you interested only in numbers like "435000","436000","436010","437000" and the word "Revenue" ,
or all words after word Return ?

 

[sandy666]

is that what you want?

Column1	Column2
1	BSdefaultValue
2	BSdefaultValue
3	BSdefaultValue
(Bypass)	"None"
400000	"Revenue"
400100	"Revenue"
400200	"Revenue"
400300	"Revenue"
400400	"Revenue"
400500	"Revenue"
400700	"Revenue"
400900	"Revenue"
400901	"Revenue"
440000	"Revenue"
470000	"Revenue"
491000	"Revenue"
492000	"Revenue"
495000	"Revenue"
431000	"Revenue"
431005	"Revenue"
432000	"Revenue"
432010	"Revenue"
433000	"Revenue"
433010	"Revenue"
434000	"Revenue"
434010	"Revenue"
435000	"Revenue"
436000	"Revenue"
436010	"Revenue"
437000	"Revenue"
500203	"COS_CFR"
500201	"COS_LFR"
500202	"COS_MFR"
500204	"COS_SaaS"
500000	"COS"
500200	"COS"
500900	"COS"
501000	"COS"
520000	"COS"
521000	"COS"
531000	"COS"
532000	"COS"
533000	"COS"
533010	"COS"
534000	"COS"
534010	"COS"
535000	"COS"
536000	"COS"
537000	"COS"
539510	"COS"
658600	"COS"
699999	"ADM"
810100	"ADM"
830500	"ADM"
850000	"ADM"
850500	"ADM"
851000	"ADM"
731000	"Fin_Expense"
810000	"Fin_Expense"
810010	"Fin_Expense"
810015	"Fin_Expense"
810050_1	"Fin_Expense"
810070_1	"Fin_Expense"
810071_1	"Fin_Expense"
820100	"Fin_Expense"
820200	"Fin_Expense"
880830	"Fin_Expense"
800000	"Fin_Income"
810050	"Fin_Income"
860000	"Fin_Income"
869080	"Fin_Income"
880840	"Fin_Income"
830300	"SR_Associates"
869090	"Taxation"
880000	"Taxation"
880100	"Taxation"
880200	"Taxation"
880300	"Taxation"
880400	"Taxation"
880500	"Taxation"

 

[DavidG007]

are you interested only in numbers like "435000","436000","436010","437000" and the word "Revenue" ,
or all words after word Return ?

Hi Sandy,

It would need to show all the words after Return, so 500201 would return COS_LFR

Thanks

 

[DavidG007]

Hi Sandy, Yes, that is spot on

 

[sandy666]

so here is:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Fill = Table.FillUp(Source,{"Column2"}),
    Filter = Table.SelectRows(Fill, each ([Column1] <> null)),
    RemoveBR = Table.RemoveLastN(Filter,1),
    Replace1 = Table.ReplaceValue(RemoveBR,"Case Is = ","",Replacer.ReplaceText,{"Column1"}),
    Extract = Table.TransformColumns(Replace1, {{"Column1", each Text.BeforeDelimiter(_, " '"), type text}}),
    Replace2 = Table.ReplaceValue(Extract,"Return ","",Replacer.ReplaceText,{"Column2"}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Replace2, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Trim = Table.TransformColumns(Split,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}})
in
    Trim[/SIZE]

example file

 

[DavidG007]

Hi Sandy, unfortunately it won't let me open your sample file, i get the following error?

[FONT=&quot]We're sorry. We can't open the workbook in the browser because it uses these unsupported features: [/FONT]

[FONT=&quot]• XML maps[/FONT]

 

[sandy666]

you need to DOWNLOAD file not open. Excel Online doesn't support PowerQuery

you've option there: open in Excel - use it

 

[DavidG007]

Hi Sandy, really sorry but I don't see an option to download or open in excel? if i right click on you link the option doesn't appear.

Sorry for appearing dim, thank you for your help

 

[sandy666]

in FireFox (the best browser! )

or you can try this: excel file

 

[DavidG007]

Hi, this is proving more of a challenge than the original query!, i have just opened firefox to try your steps above but i still don't see the option to 'open in excel'??