Creating a consolidated set of pairs on Excel

Question

I am working with an Excel file having import and export data between different countries for various shipping products, which looks pretty much like this:

Raw data

The goal is to create a consolidated state pair that have a trade relation between them. So the final list for the above example should look something like this:

Result goal

What would be the best way to go about this?

How do you arrive at 4 for Japan/China? Is this a bi-directional count? — teylyn, Nov 08 '20 at 01:21

score 1 · Answer 1 · answered Nov 08 '20 at 02:37

1

Try below formula-

=COUNTIFS(A:A,E4,B:B,F4)+COUNTIFS(A:A,F4,B:B,E4)

answered Nov 08 '20 at 02:37

Harun24hr

30,391
4
21
36

score 0 · Answer 2 · answered Nov 08 '20 at 00:58

0

So assume your data is in a&b in the first screenshot, put this formula in column c =a1&b1 then hit enter, then drag the bottom right hand corner down so the formula works for all cells in that column. Then copy the column and paste it as values.

Then in column d, use the formula =countifs(c:c,c1) and drag it down in the same way. Now paste column d as values.

Finally, on the data tab, remove duplicates based on column c and then delete column c.

answered Nov 08 '20 at 00:58

Hooded 0ne

881
1
3
10

It would be easier to do that with a pivot table, but it does not produce what the question is asking for. – teylyn Nov 08 '20 at 01:28

score 0 · Answer 3 · answered Nov 08 '20 at 01:51

0

You can do this really easy with Pivot Tables. Just take Field Importing Country into rows section and also values section (make sure it's counting) and field Exporting Countryto rows section.

Choose Tabular design, deactivate subtotals and activate Repeat labels. This is what you get:

answered Nov 08 '20 at 01:51

Foxfire And Burns And Burns

10,665
2
17
38

2

I thought so, too, but the OP wants to count the relationships, so it's 3x Japan/China plus 1 in reverse for China/Japan = 4 for any relationship count between China and Japan. – teylyn Nov 08 '20 at 02:12

Ron Rosenfeld · Answer 4 · 2020-11-08T02:59:06.977

You can do this with Power Query, available in Windows Excel 2010+ and O365

Sort each row horizontally
Group by the two resultant country rows, aggregating by Count

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Importing Country", type text}, {"Exporting Country", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Country", each List.Sort({[Importing Country],[Exporting Country]})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Importing Country", "Exporting Country"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Country", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Country", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Country.1", "Country.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Country.1", type text}, {"Country.2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Country.1", "Country.2"}, {{"Occurrences", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Creating a consolidated set of pairs on Excel

4 Answers4