Condensing Data: Many Blank is the Table, but most lines contain needed data.

[miegorengman]

Let me first say I lack classic excel training. I normally use excel to solve problems as they arise, learning as I go. Thank you to those with training willing to answer these questions.

I am attempting to use Excel to convert some CMUD XML formatting to BlowTorch XML formatting. My ideal result is to have 2 columns, one containing the results from the Alias column and one containing a condensed version of "<Value>" + "between" + "</Value>" or "All in One" columns as applies. When there is more than one line of data it needs to be separated with a semicolon, resulting in my looking for a dynamic solution.

So far I have been able to extract the constituent parts using a table with formulas and now I am hoping to find a way to stitch them back together in a new format.

Each Alias begins with a name, and contains a "<value>" reference to being and "</value>" reference to end said alias. Two good examples are "pip" and "gfb"

pip contains 3 lines, A5:A8s data, which ideally become a Alias and Content column, "pip" the Alias and "open pack;put $1 in pack;close pack" the Content Column.

gfb is a single line, A39:A40, you can see the data is formatted differently.

Here is a link to the doc via google drive: https://drive.google.com/file/d/1yhqeEWMVUIDgK3Xhq7qNZQ969GQ-aEWq/view?usp=sharing

Thanks again.

 

[sandy666]

is that what you want?

(PowerQuery required)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Alias[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]pip[/td][td=bgcolor:#E2EFDA]open pack;put $1 in pack;close pack[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gp[/td][td]get $1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]gfp[/td][td=bgcolor:#E2EFDA]get $1 from pack[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gw[/td][td]get $1;;wear $1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]gfpw[/td][td=bgcolor:#E2EFDA]wear $1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gr[/td][td]retire $1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ht[/td][td=bgcolor:#E2EFDA]hunger;;thirst[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gfb[/td][td]get $1 from bin[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]pib[/td][td=bgcolor:#E2EFDA]put $1 in bin[/td][/tr]

[tr=bgcolor:#FFFFFF][td]pic[/td][td]put $1 in cart[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]gfc[/td][td=bgcolor:#E2EFDA]get $1 from cart[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ep[/td][td]ex pack[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]em[/td][td=bgcolor:#E2EFDA]enter mill[/td][/tr]

[tr=bgcolor:#FFFFFF][td]exb[/td][td]ex bulletin board[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]dp[/td][td=bgcolor:#E2EFDA]draw pole[/td][/tr]

[tr=bgcolor:#FFFFFF][td]rp[/td][td]retire pole[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]gwa[/td][td=bgcolor:#E2EFDA]gff $1;;piw $1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gff[/td][td]get {(floor)} $1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]sl[/td][td=bgcolor:#E2EFDA]sniff;;listen[/td][/tr]

[tr=bgcolor:#FFFFFF][td]mt[/td][td]get tape from belt;measure $1;put tape in belt[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]drd[/td][td=bgcolor:#E2EFDA]draw rusty dagger[/td][/tr]

[tr=bgcolor:#FFFFFF][td]exa[/td][td]examine armoire 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]opa[/td][td=bgcolor:#E2EFDA]open armoire 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]cla[/td][td]close armoire 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]pia[/td][td=bgcolor:#E2EFDA]put $1 in armoire 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gfw[/td][td]get $1 from wagon[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]piw[/td][td=bgcolor:#E2EFDA]put $1 in wagon[/td][/tr]

[tr=bgcolor:#FFFFFF][td]exw[/td][td]ex wagon[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]gca[/td][td=bgcolor:#E2EFDA]get {(}floor{)} $1;;put $1 in cart[/td][/tr]

[tr=bgcolor:#FFFFFF][td]dln[/td][td]say delln ($-1)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]sellb[/td][td=bgcolor:#E2EFDA]sell $1 square feet of board[/td][/tr]

[tr=bgcolor:#FFFFFF][td]bc[/td][td]butcher corpse[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]prs[/td][td=bgcolor:#E2EFDA]prospect for[/td][/tr]

[tr=bgcolor:#FFFFFF][td]exd[/td][td]ex delln[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]dcal[/td][td=bgcolor:#E2EFDA]#send say delln {(}set combat allocation = left hand,b,$1,d,$2,s,$3,a,$4,n,300{)}[/td][/tr]

[tr=bgcolor:#FFFFFF][td]dcar[/td][td]#send say delln (set combat allocation = right hand,b,$1,d,$2,s,$3,a,$4,n,300)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]sca[/td][td=bgcolor:#E2EFDA]show combat allocation:all[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gfa[/td][td]get $1 from armoire[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]lf[/td][td=bgcolor:#E2EFDA]get box from belt;light $1;put box in belt[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gb[/td][td]put $1 in bag[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ds[/td][td=bgcolor:#E2EFDA]draw staff[/td][/tr]

[tr=bgcolor:#FFFFFF][td]wart[/td][td]open wartbag;gff $1;put $1 in wartbag;close wartbag[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]sbag[/td][td=bgcolor:#E2EFDA]put $1 in delln's saddlebag[/td][/tr]

[tr=bgcolor:#FFFFFF][td]exs[/td][td]ex delln's saddlebag[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]woff[/td][td=bgcolor:#E2EFDA]set who to off[/td][/tr]

[tr=bgcolor:#FFFFFF][td]wdesc[/td][td]set who to desc[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]scp[/td][td=bgcolor:#E2EFDA]get scope from pack;open scope;survey far;close scope;put scope in pack[/td][/tr]

[tr=bgcolor:#FFFFFF][td]sblt[/td][td]put $1 in belt[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]blt[/td][td=bgcolor:#E2EFDA]get $1 from belt[/td][/tr]

[tr=bgcolor:#FFFFFF][td]encsl[/td][td]enter castle[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ency[/td][td=bgcolor:#E2EFDA]enter canyon[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ents[/td][td]enter nets[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]rs[/td][td=bgcolor:#E2EFDA]retire staff[/td][/tr]

[tr=bgcolor:#FFFFFF][td]sc[/td][td]skin corpse[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]enb[/td][td=bgcolor:#E2EFDA]enter building[/td][/tr]

[tr=bgcolor:#FFFFFF][td]env[/td][td]enter village[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]opnsc[/td][td=bgcolor:#E2EFDA]open scroll case[/td][/tr]

[tr=bgcolor:#FFFFFF][td]clsc[/td][td]close scroll case[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]exsc[/td][td=bgcolor:#E2EFDA]ex scroll case[/td][/tr]

[tr=bgcolor:#FFFFFF][td]dw[/td][td]draw wand[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]rw[/td][td=bgcolor:#E2EFDA]retire wand[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ac[/td][td]activate circle[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]laugh[/td][td=bgcolor:#E2EFDA]emote laughs deep in his chest.[/td][/tr]

[tr=bgcolor:#FFFFFF][td]pb[/td][td]pet bait[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]cab[/td][td=bgcolor:#E2EFDA]put $1 in cabinet[/td][/tr]

[tr=bgcolor:#FFFFFF][td]opc[/td][td]open chest[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]clc[/td][td=bgcolor:#E2EFDA]close chest[/td][/tr]

[tr=bgcolor:#FFFFFF][td]gs[/td][td]put $1 in sack;close pack[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]scrp[/td][td=bgcolor:#E2EFDA]open scrip;get $1 from scrip;close scrip[/td][/tr]

[tr=bgcolor:#FFFFFF][td]sscrp[/td][td]put $1 in scrip[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]gsb[/td][td=bgcolor:#E2EFDA]put $1 in saddlebag[/td][/tr]

[tr=bgcolor:#FFFFFF][td]slh[/td][td]store left hand[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]rlh[/td][td=bgcolor:#E2EFDA]retrieve left hand[/td][/tr]
[/table]

 

[miegorengman]

Thank you sandy666, that is exactly where I need to get. Thank you clarifying you used power query, looking that up now.

I am also hoping to learn from this, may I ask how you arrived at this final result?

 

[sandy666]

may I ask how you arrived at this final result?

sure,

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Removed Top Rows" = Table.Skip(Source,3),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","",null,Replacer.ReplaceValue,{"Alias"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Alias"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Filled Down","",null,Replacer.ReplaceValue,{"<Value>", "between", "</Value>", "All in One"}),
    #"Removed Duplicates" = Table.Distinct(#"Replaced Value1", {"<Value>", "between", "</Value>", "All in One"}),
    #"Removed Top Rows1" = Table.Skip(#"Removed Duplicates",1),
    #"Grouped Rows" = Table.Group(#"Removed Top Rows1", {"Alias"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Distinct(Table.Column([Count],"<Value>"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Distinct(Table.Column([Count],"between"))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Distinct(Table.Column([Count],"</Value>"))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each List.Distinct(Table.Column([Count],"All in One"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Custom.2", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Custom.3", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values3",{"Count"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Custom", "Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Added Custom4" = Table.AddColumn(#"Merged Columns", "Custom", each Text.Trim([Merged],";")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom4",{"Merged"})
in
    #"Removed Columns1"[/SIZE]

probably it can be done easier/shorter/faster way but I am lazy and if I did it and it works I wont back to this again

here is your example file

 

[miegorengman]

many thanks

 

[sandy666]

You are welcome

treat it as an example and try make your own way