Creating unique list

[RicardoCubed]

I have a table that i need to mine out involvement by property.
Like to be able to enter ID into cell A19 and automaticaly generate list of those involved if % is greater than zero.
Help please.

	A	B	C	D	E	F
1	This is the table
2		Jason	John	Nick	Frank	Beth
3	ID	Wally	Wayne	Toto	Cook	Teacher
4
5
6	01	0.00%	0.0%	0.0%	0.0%	0.0%
7	02	0.34%	0.4%	0.5%	0.4%	0.0%
8	03	0.85%	1.0%	1.1%	0.9%	0.0%
9	04	0.41%	0.5%	0.5%	0.4%	0.0%
10	05	3.79%	4.4%	5.1%	4.1%	0.0%
11	06	0.00%	0.0%	0.0%	0.0%	0.0%
12	07	2.23%	2.6%	3.0%	2.4%	0.0%
13	08	0.71%	0.8%	1.0%	0.8%	0.0%
14	09	0.95%	1.1%	0.0%	1.0%	0.0%
15
16	The below is what I am looking to be able to create.
17
18	Prop ID
19	09	Jason Wally	0.95%
20		John Wayne	1.10%
21		Frank Cook	1.00%

<colgroup><col style="width: 42pt;" span="2" width="56"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 3242;" width="76"> <col style="width: 42pt;" span="4" width="56"> <tbody>
</tbody>

 

[Peter_SSs]

For that layout, try these formulas. These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied down.

Excel Workbook
	A	B	C	D	E	F
1
2		Jason	John	Nick	Frank	Beth
3	ID	Wally	Wayne	Toto	Cook	Teacher
4
5
6	1	0.00%	0.00%	0.00%	0.00%	0.00%
7	2	0.34%	0.40%	0.50%	0.40%	0.00%
8	3	0.85%	1.00%	1.10%	0.90%	0.00%
9	4	0.41%	0.50%	0.50%	0.40%	0.00%
10	5	3.79%	4.40%	5.10%	4.10%	0.00%
11	6	0.00%	0.00%	0.00%	0.00%	0.00%
12	7	2.23%	2.60%	3.00%	2.40%	0.00%
13	8	0.71%	0.80%	1.00%	0.80%	0.00%
14	9	0.95%	1.10%	0.00%	1.00%	0.00%
15
16
17
18	Prop ID
19	9	Jason Wally	0.95%
20		John Wayne	0.95%
21		Frank Cook	0.95%
22
List

 

[RicardoCubed]

Peter. Simply amazing! Wow!!! thank you so much!!!!! Have a great day.

 

[Peter_SSs]

Glad it helped, thanks for letting us know.

 

[RicardoCubed]

Peter. Thanks again for helping me on this. I have a follow up question. I am trying to use the same formula but referencing the data which is contained in 2 seperate sheets in the same file.
Not having success - is there a "structural" reason why your formula which worked perfectly cannot be translated to cross sheets?
Thanks for any thoughts.
Rick

 

[Peter_SSs]

I am trying to use the same formula but referencing the data which is contained in 2 seperate sheets in the same file.
Not having success - is there a "structural" reason why your formula which worked perfectly cannot be translated to cross sheets?

No, here it is. I've named the sheet with the main table as 'Original Data'. You'll need to change that in the formulas to match your name of that sheet.

Excel Workbook
	A	B	C	D	E	F
1
2		Jason	John	Nick	Frank	Beth
3	ID	Wally	Wayne	Toto	Cook	Teacher
4
5
6	1	0.00%	0.00%	0.00%	0.00%	0.00%
7	2	0.34%	0.40%	0.50%	0.40%	0.00%
8	3	0.85%	1.00%	1.10%	0.90%	0.00%
9	4	0.41%	0.50%	0.50%	0.40%	0.00%
10	5	3.79%	4.40%	5.10%	4.10%	0.00%
11	6	0.00%	0.00%	0.00%	0.00%	0.00%
12	7	2.23%	2.60%	3.00%	2.40%	0.00%
13	8	0.71%	0.80%	1.00%	0.80%	0.00%
14	9	0.95%	1.10%	0.00%	1.00%	0.00%
15
Original Data

Excel Workbook
	A	B	C
1
2	Prop ID
3	9	Jason Wally	0.95%
4		John Wayne	0.95%
5		Frank Cook	0.95%
6
List

 

[RicardoCubed]

Peter. Thanks again for your time and help. One error/situation I am having and maybe you too was in list created the forumla for cell C3 is returning the same percentage interest and not "finding" the right answer. Look at Frank Cook % which should be 1.00%. I tried to track through the logic and fix without bothering you but my skills are no where near yours.
May I trouble you for help once again????
Thanks.

 

[Peter_SSs]

Sorry, my mistake. It was wrong in post #2 as well.
I was originally using a helper cell (A20 in post #2 and A4 in post #6) and when I changed away from the helper cell, missed that correction in the column C formulas.



The C3 formula for this layout should be

=IF(B3="","",INDEX('Original Data'!B$6:F$14,MATCH(A$3,'Original Data'!A$6:A$14,0),SMALL(IF(INDEX('Original Data'!$B$6:$F$14,MATCH(A$3,'Original Data'!A$6:A$14,0),0)>0,COLUMN('Original Data'!$B$2:$F$2)-COLUMN('Original Data'!$B2)+1),ROWS(B$3:B3))))

Still confirmed with Ctrl+Shift+Enter

 

[RicardoCubed]

peter. Sorry to trouble you AGAIN. But I have struggled with the C3 foruma for the past hour plus and I am getting an error.

While my spreadsheet construction is different I get the first row results correct but when I copy down its a problem.

The below gives me the correct jason Waserman % Allocation but when copied down doesnt work.

=IF(E7="","",INDEX(Allocation!F$8:BR$86,MATCH(E$6,B$7:B$53,0),SMALL(IF(INDEX(Allocation!$F$8:$BR$86,A$7,0)>0,COLUMN(Allocation!$F$4:$BR$4)-COLUMN(Allocation!$F4)+1),ROWS(E$7:E7))))

<tbody>
</tbody><colgroup><col></colgroup>

AA	% Allocation
Jason Wasserman - Ppty	0.225965831
Thomas Sullivan	#N/A
So then I tried this adjsted formula for Thomas Sullivan and this did work. Help Again????? Thanks =IF(E8="","",INDEX(Allocation!F$8:BR$86,MATCH(E$6,Allocation!F$8:BR$86,0),SMALL(IF(INDEX(Allocation!F$8:BR$86,MATCH(E$6,Allocation!F$8:BR$86,0),0)>0,COLUMN(Allocation!$F$4:$BR$4)-COLUMN(Allocation!$F4)+1),ROWS(Sheet2!E$7:E8)))) <tbody> </tbody><colgroup><col></colgroup>

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Peter_SSs]

Bit hard without knowing what ranges various things are actually in, but try this for the first percentage cell & copy down after C+S+E entry.

=IF(E7="","",INDEX(Allocation!F$8:BR$86,MATCH(E$6,Allocation!B$8:B$86,0),SMALL(IF(INDEX(Allocation!F$8:BR$86,MATCH(E$6,Allocation!B$8:B$86,0),0)>0,COLUMN(Allocation!$F$4:$BR$4)-COLUMN(Allocation!$F$4)+1),ROWS(E$7:E7))))