Pull unique values when adjacent cells are "combined."

[brashandcrass]

Want to pull a list of names that are unique from A:B and have it appear in F:G as seen below.

	A	B	C	D	E	F	G
1	First Name	Last Name	Role	Start Up	----------	Unique	Unique
2	George	Aquino	Invested	Initech		George	Aquino
3	George	Aquino	Invested	Acme Corporation		Alban	Dushku
4	Alban	Dushku	Founded	Massive Dynamic		Femi	Folani
5	Alban	Dushku	Exited	Massive Dynamic
6	Femi	Folami	Invested	Soylent Corp
7	Femi	Folami	Invested	Soylent Corp

<tbody>
</tbody>

Thanks!

 

[James006]

Hello again,

In cell G2 ... you are familiar with an Array Formula ...:wink:

=IFERROR(INDEX($A$2:$A$7&" "&$B$2:$B$7,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$7&" "&$B$2:$B$7),0)),"")

Hope this will help

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	First Name	Last Name	Role	Start Up
2	George	Aquino	Invested	Initech
3	George	Aquino	Invested	Acme Corporation
4	Alban	Dushku	Founded	Massive Dynamic
5	Alban	Dushku	Exited	Massive Dynamic
6	Femi	Folami	Invested	Soylent Corp
7	Femi	Folami	Invested	Soylent Corp
Sheet1

1. Name A2:A7 fname via Formulas | Name Manager.
2. Name B2:B7 lname.
3. Name C2:C7 role.
4. Name D2:D7 startup.
5. Define ivec as referring to:

=ROW(Sheet1!$A$2:$D$7)-ROW(INDEX(Sheet1!$A$2:$D$7,1,1))+1

Book1
	A	B	C	D	E
1	First Name	Last Name	Invested	Founded	Exited
2	George	Aquino	2	0	0
3	Alban	Dushku	0	1	1
4	Femi	Folami	1	0	0
5
Sheet2

In A2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(fname,SMALL(IF(FREQUENCY(IF(LEN(fname&"|"&lname)>LEN(fname)+1,MATCH(fname&"|"&lname,fname&"|"&lname,0)),ivec),ivec),ROWS(A$2:A2))),"")

In B2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(lname,SMALL(IF(FREQUENCY(IF(LEN(fname&"|"&lname)>LEN(fname)+1,MATCH(fname&"|"&lname,fname&"|"&lname,0)),ivec),ivec),ROWS(B$2:B2))),"")

In C1 of Sheet2 control+shift+entrer, not just enter, and copy across:

=IFERROR(INDEX(role,SMALL(IF(FREQUENCY(IF(role<>"",MATCH(role,role,0)),ivec),ivec),COLUMNS($C$1:C$1))),"")

In C2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IF($A2="","",SUM(IF(FREQUENCY(IF(fname&"|"&lname=$A2&"|"&$B2,IF(role=C$1,MATCH(startup,startup,0))),ivec),1)))

 

[billandrew]

Great Stuff

Not my Post, however if I may how does the countif work within the Match...

 

[brashandcrass]

Thanks again, James006.

One thing: I'd like for the first and last names to remain in separate cells (cols). How do we achieve that?

Also, since this is just a sample section, will this formula also take into account if the values in the Last Name col are not unique (e.g. the below table)?

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

First Name	Last Name	Role	Start Up		First Name	Last Name
George	Aquino	Invested	Initech		George	Aquino
George	Aquino	Invested	Acme Corporation		Alban	Dushku
Alban	Dushku	Founded	Massive Dynamic		Femi	Folami
Alban	Dushku	Exited	Massive Dynamic		James	Folami
Femi	Folami	Invested	Soylent Corp
Femi	Folami	Invested	Soylent Corp
James	Folami	Founded	Initech
James	Folami	Founded	Initech

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="175"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

Thanks.

 

[Aladin Akyurek]

See post#3...

 

[James006]

Hello,

To answer your first question:

Once you have the Unique Full Names in Column E, you can always split them ...

To get the first name

=LEFT(E2,FIND(" ",E2)-1)

To get the last name

=SUBSTITUTE(E2,F2&" ","")

To answer your second question:

Since the Unique Names have been extracted by combining First Names and Last Names ...there is no risk ...

You can obviously carry out your own tests to be totally sure about this aspect ...:wink:

Hope this will help

Cheers

 

[brashandcrass]

Aladin Akyurek, undoubtedly this approach is way over my head. I appreciate the input and your time but this is unfortunately one of those occasions I cannot sacrifice time to learn how to use Formulas | Name Manager.

 

[brashandcrass]

James006, sometimes I just have to think K.I.S.S. so I did this:

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$7&" "&$B$2:$B$7),0)),"")

and this:

=IFERROR(INDEX($B$2:$B$7,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$7&" "&$B$2:$B$7),0)),"")

Thanks again.

 

[James006]

Glad you could fix your problem ... .wink:

Thanks ... for your Thanks ...