Row and Col relationship

[Hem_excel]

I have an attached excel with list of companies in Rows and Columns

Now, I want to know list of relationships where balance more than zero (for all companies) so that i can assign the team those companies to further work on.

e.g. A13:B13 is one relationship which has a balance of 1,951 which i want to assign to Team member 1, similarly A17:K17 has a balance of 81,891 which i would like to assign to team member 2.. so on and so forth..

Not sure which would be best formula to use to get the desired result..excel attached..

 

[GraH - Guido]

I don't know how to do this easily with formulae.
With Power Query it is simply clicking some buttons. It will depend on your version of Excel (and Operating System) whether or not you can apply this approach.

I did name the range of data (=Sheet1!$A$7:$GI$196) "data".

Paste this below inside a blank query.

let
Source = Excel.CurrentWorkbook(),
data = Source{[Name="data"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(data, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"A"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Value] > 0)
in
#"Filtered Rows"

 

[p45cal]

e.g. A13:B13 is one relationship which has a balance of 1,951

2 questions.
1. In the picture below, I've highlighted in yellow the relationship you describe. In fact that relationship is doubled up in the second yellow highlighted cell. Do you want to ignore one of the yellow cells? (This happens a lot in your table; green cells show another)
2. The blue cells show 2 cases where a company has a relationship with itself; do these need to be listed too (if they're positive, which I realise one of them isn't)?



How many team members have you got? At the moment, if we count only unique pairs of companies and ignore those single-company relationships (there are 11), we have some 150 pairs > 0

 

[Hem_excel]

2 questions.
1. In the picture below, I've highlighted in yellow the relationship you describe. In fact that relationship is doubled up in the second yellow highlighted cell. Do you want to ignore one of the yellow cells? (This happens a lot in your table; green cells show another)
2. The blue cells show 2 cases where a company has a relationship with itself; do these need to be listed too (if they're positive, which I realise one of them isn't)?
View attachment 80509


How many team members have you got? At the moment, if we count only unique pairs of companies and ignore those single-company relationships (there are 11), we have some 150 pairs > 0

Thank you for working on this much appreciated:
To answer your Qs:
1. the one in yellow represents balance of 1,951 in each company's book, hence either 1005:1000 or 1000:1005 will throw same results of 1,951 and is one relationship only (which is same you found in green and will be true for all companies) - Please note that list of companies in first column and row no. 7 are same.
2. correct, relationship with self can be ignored.

I have about 4 team members to whom i would like to assign this.

the idea was to understand and apply if any formula which throws relationship listing and where i can pull the unique balances.

 

[Hem_excel]

I don't know how to do this easily with formulae.
With Power Query it is simply clicking some buttons. It will depend on your version of Excel (and Operating System) whether or not you can apply this approach.

I did name the range of data (=Sheet1!$A$7:$GI$196) "data".

Paste this below inside a blank query.

let
Source = Excel.CurrentWorkbook(),
data = Source{[Name="data"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(data, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"A"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Value] > 0)
in
#"Filtered Rows"

I don't know how to do this easily with formulae.
With Power Query it is simply clicking some buttons. It will depend on your version of Excel (and Operating System) whether or not you can apply this approach.

I did name the range of data (=Sheet1!$A$7:$GI$196) "data".

Paste this below inside a blank query.

let
Source = Excel.CurrentWorkbook(),
data = Source{[Name="data"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(data, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"A"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Value] > 0)
in
#"Filtered Rows"

Thank you..you actually gave me the solution which is exactly i wanted though i need to learn power query to get the similar solution in future.

Many thanks !!

 

[GraH - Guido]

Thanks for the feedback. Power Query is not as difficult as it sounds. Hope you enjoy the learning experience.

 

[p45cal]

In the attached, list of company pairs with duplicates removed and self relationships removed, leaving 150 pairs to be split up among 4 people.