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.

[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Role[/TD]
[TD]Start Up[/TD]
[TD]----------[/TD]
[TD]Unique[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Initech[/TD]
[TD][/TD]
[TD]George[/TD]
[TD]Aquino[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Acme Corporation[/TD]
[TD][/TD]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Founded[/TD]
[TD]Massive Dynamic[/TD]
[TD][/TD]
[TD]Femi[/TD]
[TD]Folani[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Exited[/TD]
[TD]Massive Dynamic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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>[TABLE="width: 0"]
<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>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Role[/TD]
[TD]Start Up[/TD]
[TD][/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Initech[/TD]
[TD][/TD]
[TD]George[/TD]
[TD]Aquino[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Acme Corporation[/TD]
[TD][/TD]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[/TR]
[TR]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Founded[/TD]
[TD]Massive Dynamic[/TD]
[TD][/TD]
[TD]Femi[/TD]
[TD]Folami[/TD]
[/TR]
[TR]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Exited[/TD]
[TD]Massive Dynamic[/TD]
[TD][/TD]
[TD]James[/TD]
[TD]Folami[/TD]
[/TR]
[TR]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Folami[/TD]
[TD]Founded[/TD]
[TD]Initech[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Folami[/TD]
[TD]Founded[/TD]
[TD]Initech[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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 ...