Moving Data some duplicated

[scooby51]

Good Morningall,


I am looking for a formula to helpin moving data on a spreadsheet.
Column A contains names, eachname is duplicated over 2 rows.
Column B contains animals, each name has an animal in eachrow.
I need to have in:
Column D – a list of single names from column A
Column E – first animal (column B) attributed to the name incolumn A
Column F – second animal (column B) attributed to the namein column A
_{^{<strike>
</strike>}}

A	B		D	E	F
ab	cat		ab	cat	dog
ab	dog		bc	cow	sheep
bc	cow		cd	bird	fish
bc	sheep		de	rat	goat
cd	bird
cd	fish
de	rat
de	goat

<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>
</tbody>

Any help will be much appreciated.
S

 

[sandy666]

with PowerQuery (Get&Transform)

Name	Animal		Name	Animal.1	Animal.2
ab	cat		ab	cat	dog
ab	dog		bc	cow	sheep
bc	cow		cd	bird	fish
bc	sheep		de	rat	goat
cd	bird
cd	fish
de	rat
de	goat

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    ListDist = Table.AddColumn(Group, "Animal", each List.Distinct(Table.Column([Count],"Animal"))),
    Extract = Table.TransformColumns(ListDist, {"Animal", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Animal", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Animal.1", "Animal.2"})
in
    Split[/SIZE]

 

[Fluff]

Or with formulae

Excel 2013/2016
	A	B	C	D	E	F
1	A	B		D	E	F
2	ab	cat		ab	cat	dog
3	ab	dog		bc	cow	sheep
4	bc	cow		cd	bird	fish
5	bc	sheep		de	rat	goat
6	cd	bird
7	cd	fish
8	de	rat
9	de	goat
Sheet2

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$9),0),0)),"")
E2	=IFERROR(INDEX($B$2:$B$9,MATCH(D2,$A$2:$A$9,0)),"")
F2	=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,ROW($B$2:$B$9)-ROW($B$2)+1/($A$2:$A$9=D2),2)),"")

 

[scooby51]

Thanks both, helpful as ever.
keep it up
S

 

[Fluff]

You're welcome & thanks for the feedback

 

[sandy666]

Welcome and have a nice day