Searching set of rows and multi columns for a value

[vibhu]

My problem is as follows:
1) A set of sample data A1 to E15 as shown in attached file is there, in an Excel file. Data starts from cell A1 and extends to E15. Results are to be furnished in Columns L to P.
2) Names of Team 1 figure in Column B and Team 2 under Column C, for the game sequence played in a tournament.
3) Team 1 (in Column B) plays a game with Team 2 (in Column C) and it can happen at some stage of play sequence that Team 2 (Column B) plays with Team 1 (Column C); Hence the names gets interchanged in the two columns.
4) The Column D and E furnishes the match results of the teams under Column B and C respectively, at games play sequence number;
5) We need to find the cells in multi columns (here from two columns B and C) for the unique values sorted alphabetically and stored as values in Column K which match to the random values in B and C;
6) The corresponding result for the team is to be picked by the results wherever the team names figures and in the order of games played.
e. g. 'JApan' team name figures in cells C5, B9 and B14. The corresponding results of team 'Japan' are in cells E5, D9 and D14 which are 'Lost', 'Lost', 'Won'.
7) Results to be put under Columns L to P in front of the unique team names in Column K.
8) The results needs to be given as results in reverse with a '-' in the fourth result (since Japan in the data given do not play the fourth game.
9) The maximum number of results that needs to be shown are for last five games played.
10) The results to update automatically and display only the last five results for the team played with the result of the most recent game played shown first in Column L; the result of the game played before that under Column M and so on [For Five columns only].

Please furnish the Solution along with the formulae used (NO MACROS)

 

[HongRu]

How about Power Query ?
Try.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Play Sequence", Int64.Type}, {"Team 1", type text}, {"Team 2", type text}, {"Result Team 1", type text}, {"Result Team 2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Team 1", "Result Team 1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Team 2", "Result Team 2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.2"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns2", {{"Merged.2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Merged.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.2.1", "Merged.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.2.1", type text}, {"Merged.2.2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Merged.2.1", Order.Ascending}, {"Play Sequence", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Merged.2.1"}, {{"Count", each _, type table [Play Sequence=nullable number, Merged.2.1=nullable text, Merged.2.2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Merged.2.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter3",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Split Column by Delimiter3")),
    MyCountCol=Table.ColumnCount(#"Replaced Value"),
    MyCountRow=Table.RowCount(#"Replaced Value"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged.2.1"}, "Attribute", "Value"),
    #"Sorted Rows1" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Descending}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Sorted Rows1",MyCountRow*(MyCountCol-6)),
    #"Pivoted Column" = Table.Pivot(#"Removed Bottom Rows", List.Distinct(#"Removed Bottom Rows"[Attribute]), "Attribute", "Value"),
    #"Sorted Rows2" = Table.Sort(#"Pivoted Column",{{"Merged.2.1", Order.Ascending}})
in
    #"Sorted Rows2"

 

[HongRu]

Take some fixes.

Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Play Sequence", Int64.Type}, {"Team 1", type text}, {"Team 2", type text}, {"Result Team 1", type text}, {"Result Team 2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Team 1", "Result Team 1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Team 2", "Result Team 2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.2"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns2", {{"Merged.2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Merged.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.2.1", "Merged.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.2.1", type text}, {"Merged.2.2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Merged.2.1", Order.Ascending}, {"Play Sequence", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Merged.2.1"}, {{"Count", each _, type table [Play Sequence=nullable number, Merged.2.1=nullable text, Merged.2.2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Merged.2.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter2" = Table.TransformColumns(#"Removed Columns", {{"Custom", each Text.AfterDelimiter(_, ",", {4, RelativePosition.FromEnd}), type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter3",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Split Column by Delimiter3")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged.2.1"}, "Attribute", "Value"),
    #"Sorted Rows1" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Descending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows1", List.Distinct(#"Sorted Rows1"[Attribute]), "Attribute", "Value"),
    #"Sorted Rows2" = Table.Sort(#"Pivoted Column",{{"Merged.2.1", Order.Ascending}}),
    Result = Table.RenameColumns(#"Sorted Rows2",{{"Merged.2.1", "Team"}})
in
    Result

 

[vibhu]

Take some fixes.

Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Play Sequence", Int64.Type}, {"Team 1", type text}, {"Team 2", type text}, {"Result Team 1", type text}, {"Result Team 2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Team 1", "Result Team 1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Team 2", "Result Team 2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.2"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns2", {{"Merged.2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Merged.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.2.1", "Merged.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.2.1", type text}, {"Merged.2.2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Merged.2.1", Order.Ascending}, {"Play Sequence", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Merged.2.1"}, {{"Count", each _, type table [Play Sequence=nullable number, Merged.2.1=nullable text, Merged.2.2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Merged.2.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter2" = Table.TransformColumns(#"Removed Columns", {{"Custom", each Text.AfterDelimiter(_, ",", {4, RelativePosition.FromEnd}), type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter3",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Split Column by Delimiter3")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged.2.1"}, "Attribute", "Value"),
    #"Sorted Rows1" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Descending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows1", List.Distinct(#"Sorted Rows1"[Attribute]), "Attribute", "Value"),
    #"Sorted Rows2" = Table.Sort(#"Pivoted Column",{{"Merged.2.1", Order.Ascending}}),
    Result = Table.RenameColumns(#"Sorted Rows2",{{"Merged.2.1", "Team"}})
in
    Result

Thanks HongRu. However, I am not a coder or possess enough macros knowledge. Is there a way out using standard excel functions such as index, match, vlookup, sort, transpose, etc. etc.

 

[Fluff]

Hi & welcome to MrExcel.

Can you please post your sample data rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[HongRu]

Thanks HongRu. However, I am not a coder or possess enough macros knowledge. Is there a way out using standard excel functions such as index, match, vlookup, sort, transpose, etc. etc.

Maybe you can follow step by step.

Step1. Select you data, and press "Ctrl+T" to reform into Table. You should make the Table's name as "Table2" from the tab "Design".
Step2. Select tab "Data" => "From Table/Range". (Or press "Alt A P T" in sequence.) Then Power Query should be showed.
Step3. Select tab "Home" => "Advanced Editor". (Or press "Alt H Q" in sequence.)
Step4. Clear all code in Advanced Editor.
Step5. Copy and paste my code above in #3. Then press "Done".
Step6. Select tab "Home" => "Close & Load". (Or press "Alt H C" in sequence and select the "Close & Load".)
Done.

PS.
1. Your table's name should exactly equal to "Table2" as my code's 2rd line shows.
2. Your table's columns names should exactly equal to "Play Sequence", "Team 1", "Team 2", "Result Team 1", "Result Team 2" as my code's 3rd line shows.

HTH