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>}}
[TABLE="width: 384"]
<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ab[/TD]
[TD="bgcolor: transparent"]cat[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ab[/TD]
[TD="bgcolor: transparent"]cat [/TD]
[TD="bgcolor: transparent"]dog[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ab[/TD]
[TD="bgcolor: transparent"]dog[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]bc[/TD]
[TD="bgcolor: transparent"]cow[/TD]
[TD="bgcolor: transparent"]sheep[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]bc[/TD]
[TD="bgcolor: transparent"]cow[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]cd[/TD]
[TD="bgcolor: transparent"]bird[/TD]
[TD="bgcolor: transparent"]fish[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]bc[/TD]
[TD="bgcolor: transparent"]sheep[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]de[/TD]
[TD="bgcolor: transparent"]rat[/TD]
[TD="bgcolor: transparent"]goat[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]cd[/TD]
[TD="bgcolor: transparent"]bird[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]cd[/TD]
[TD="bgcolor: transparent"]fish[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]de[/TD]
[TD="bgcolor: transparent"]rat[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]de[/TD]
[TD="bgcolor: transparent"]goat[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Any help will be much appreciated.
S

 

[sandy666]

with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Animal[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Animal.1[/td][td=bgcolor:#70AD47]Animal.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ab[/td][td=bgcolor:#DDEBF7]cat[/td][td][/td][td=bgcolor:#E2EFDA]ab[/td][td=bgcolor:#E2EFDA]cat[/td][td=bgcolor:#E2EFDA]dog[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ab[/td][td]dog[/td][td][/td][td]bc[/td][td]cow[/td][td]sheep[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]bc[/td][td=bgcolor:#DDEBF7]cow[/td][td][/td][td=bgcolor:#E2EFDA]cd[/td][td=bgcolor:#E2EFDA]bird[/td][td=bgcolor:#E2EFDA]fish[/td][/tr]

[tr=bgcolor:#FFFFFF][td]bc[/td][td]sheep[/td][td][/td][td]de[/td][td]rat[/td][td]goat[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]cd[/td][td=bgcolor:#DDEBF7]bird[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]cd[/td][td]fish[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]de[/td][td=bgcolor:#DDEBF7]rat[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]de[/td][td]goat[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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