Duplicate values issue :)

[Bella12345]

I have an unsolved excel problem of which I would really need help with I believe there are many more experienced, than I am, so hopefully through this site I could reach out to people who could help me?
Thank you already in advance

Background:

Sheet 1 is where all data is gathered on a daily basis. This sheet has a lot of information (relevant) but I would only need a few cells for sheet 2 to make the dataset simpler and easier for everyone to read.
To simplify my problem a lot, I have 3 different factors which are relevant for me from Sheet1, that I would need to export to sheet 2; column1 = "status", column2 = Employee and column3 = Country

My dataset looks like this (example):

Column1 Column2 Column3
Status Employee Country

Won John China
Lost Peter France
Lost Rebecca Germany
Won Lily Spain
Won John Italy

1. I would need to sort out a new dataset for sheet 2 based on the status in sheet1. In sheet 2 I would need three different tables: all the deals that won, all the ongoing deals and all the deals lost.
I have managed to sort out all the deals with this function: =IFERROR(INDEX('Deal pipeline Details'!$E$6:$E$44;SMALL(IF("Won"='Deal pipeline Details'!$B$6:$B$44;ROW('Deal pipeline Details'!$B$6:$B$44)-ROW('Deal pipeline Details'!$B$6)+1);ROW('Deal pipeline Details'!1:1)));"")

Now my dataset for won deals looks like this:
(All deals, that are won, by these people. Also now, if I change the status to "lost" it automatically disappears from this list and appears in the "lost deals" -table)

Column1
Employee
John
Lily
John

This is exactly what I wanted, but my problem is now:
How will I be able to add the matching "country" -column to these deals that have been won by these people?
Preferred outcome would look like this:

Column1 Column2
Employee Country
John China
Lily Spain
John Italy

The problem is, that now "John" is a duplicate. Which means, that "vlookup etc" is not an option, since I would need to have both "country values" for John and in separate rows.
Basically I would need a function, that could identify: "first value for John is "China", then Lily has a value "Spain" and then "John" appears again, so now find the second value for John, which is "Italy".
(It's not a option to remove duplicates since these deals have a lot of information that I will also need, and these can not be mixed together)

Could someone help me with? I know that the description of this problem was very busy, since I'm not able to attach any pictures or sheets to this message. But maybe someone understands, what I mean
P.S. I could use helper columns etc. to give values for these employee names or name these in the org. dataset as f.ex. John1, Lily, John2. But I received feedback, that it's not that esthetic since this excel is visible for many people and for simplicity (and to be perfect) names should be as they are without any additional letters/numbers etc.

Thanks and stay safe

[p24leclerc]

You just have to change the column E in the INDEX function to the column of the country.
Suppose your country column is F, here is the formula you need:

Please Login or Register  to view this content.

P.S. pay attention to the rules. You must enclosed your code and formulas within CODE TAGS which you can easily call from the menu by clicking the # sign.

[CARACALLA]

	A	B	C	D	E	F
1	Won	John	China		John	China
2	Lost	Peter	France		Lily	Spain
3	Lost	Rebecca	Germany		John	Italy
4	Won	Lily	Spain
5	Won	John	Italy

E1=IF(COLUMNS($E$1:E1)>=COLUMNS($A$1:$C$1),"",IFERROR(INDEX(B$1:B$5,AGGREGATE(15,6,ROW($B$1:$B$5)-ROW(B$1)+1/($A$1:$A$5="won"),ROWS($B$1:B1))),""))


copy across and down