Index match two text values from same column but using same match criteria

[chector]

Hi

I have some data. Data has thousands of rows. One column lists the organisation name and the second column list the partner organisations most have 1 to 1 relationships., but some orgs have multiple partners. How do i have one row per organisation. but have the Partner organisations in separate columns , no organisation in my data has more than 4 partner organisations. Ideally i would like the data in the format Organisation, Partner 1, Partner 2, Partner 3, Partner 4. I have mocked up below a little table of what the data currently looks like(obviously there are more than 5 rows of data in the actual raw data file).

Organisation	Partner
WHO	A
World Bank	B
UNICEF	B
UNICEF	D
UNICEF	E

 

[sandy666]

with Power Query

Organisation	Partner		Organisation	Partner.1	Partner.2	Partner.3
WHO	A		WHO	A
World Bank	B		World Bank	B
UNICEF	B		UNICEF	B	D	E
UNICEF	D
UNICEF	E

// 
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Organisation"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Partner", each [Count][Partner]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Partner", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Partner", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Partner.1", "Partner.2", "Partner.3"})
in
    #"Split Column by Delimiter"

 

[chector]

potentially very dumb question..... How would i do it in power query, ive never used it before

 

[chector]

thanks, just the code you posted

 

[chector]

is it posssible to do using formulas

 

[sandy666]

1. update profile about Excel version (Account details)
2. select range then Ctrl+T
3. Data - New Query - From Other Sources - Blank Query
4. Advanced Editor - Replace code there with code copied from the post
5. Be sure the table name is the same as in the code (here: Table1)
it works with your example

is it posssible to do using formulas

maybe someone else

 

[Ron99]

You would need to add 2 columns to your data, like shown below

column A Formula ="PARTNER"&COUNTIF($C$2:C2,C2)
column B Formula =C2&A2

Then here is your output

Column H2 formula =IF(ISNA(VLOOKUP($G2&H$1,$B$2:$D$6,3,0)),"",VLOOKUP($G2&H$1,$B$2:$D$6,3,0))

Careful when you use the count formula in column A as you said you have many rows, I suggest you just don't drag the formula in one go, make it in batches as it may take time.

Hope this helps!

 

[Peter_SSs]

1. update profile about Excel version (Account details)

I agree with sandy666. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.