Create list of values from one range based on values from another range.

[LGambrell]

I would like to concatenate parts of one range based on the values of one or more relate ranges. I'm using Office 2010 and windows 8.

Specifically, I would like to create two lists of awards, one of awards earned by, and one of awards earned but not yet given to, my scouts. My table has a list of all the possible awards in the first column then the scouts names in following columns. Each scout has two columns, one for awards earned(marked C when they earn it) and one for awards received (marked A when awarded). I have created a formula to do this but it is very messy, long and extremely hard to update if any awards are added or scouts are added after everything is set. Each scout is tracked through 6 different age levels with a possible 100 awards at each level. Each troop can have 30 or sometimes more scouts in it. So, the formula I have made, while it does the job, isn't really viable long term.



This is the formula I'm currently using.

IF(C6="C",(IF(D6<"A",$A6&", ","")),"")&IF(C7="C",(IF(D7<"A",$A7&", ","")),"")&IF(C8="C",(IF(D8<"A",$A8&", ","")),"")&IF(C9="C",(IF(#REF!<"A",$A9&", ","")),"")&IF(C11="C",(IF(D11<"A",$A11&", ","")),"")&IF(C12="C",(IF(D12<"A",$A12&", ","")),"")&IF(C13="C",(IF(D13<"A",$A13&", ","")),"")&IF(C14="C",(IF(D14<"A",$A14&", ","")),"")&IF(C15="C",(IF(D15<"A",$A15&", ","")),"")&IF(C16="C",(IF(D16<"A",$A16&", ","")),"")&IF(C17="C",(IF(D17<"A",$A17&", ","")),"")&IF(C19="C",(IF(D19<"A",$A19&", ","")),"")&IF(C20="C",(IF(D20<"A",$A20&", ","")),"")&IF(C21="C",(IF(D21<"A",$A21&", ","")),"")&IF(C22="C",(IF(D22<"A",$A22&", ","")),"")&IF(C23="C",(IF(D23<"A",$A23&", ","")),"")&IF(C24="C",(IF(D24<"A",$A24&", ","")),"")&IF(C25="C",(IF(D25<"A",$A25&", ","")),"")&IF(C26="C",(IF(D26<"A",$A26&", ","")),"")&IF(C27="C",(IF(D27<"A",$A27&", ","")),"")&IF(C29="C",(IF(D29<"A",$A29&", ","")),"")&IF(C30="C",(IF(D30<"A",$A30&", ","")),"")&IF(C31="C",(IF(D31<"A",$A31&", ","")),"")&IF(C32="C",(IF(D32<"A",$A32&", ","")),"")&IF(C33="C",(IF(D33<"A",$A33&", ","")),"")&IF(C34="C",(IF(D34<"A",$A34&", ","")),"")&IF(C35="C",(IF(D35<"A",$A35&", ","")),"")&IF(C36="C",(IF(D36<"A",$A36&", ","")),"")&IF(C37="C",(IF(D37<"A",$A37&", ","")),"")&IF(C39="C",(IF(D39<"A",$A39&", ","")),"")&IF(C40="C",(IF(D40<"A",$A40&", ","")),"")&IF(C41="C",(IF(D41<"A",$A41&", ","")),"")&IF(C42="C",(IF(D42<"A",$A42&", ","")),"")&IF(C43="C",(IF(D43<"A",$A43&", ","")),"")&IF(C44="C",(IF(D44<"A",$A44&", ","")),"")&IF(C45="C",(IF(D45<"A",$A45&", ","")),"")&IF(C46="C",(IF(D46<"A",$A46&", ","")),"")&IF(C47="C",(IF(D47<"A",$A47&", ","")),"")&IF(C49="C",(IF(D49<"A",$A49&", ","")),"")&IF(C50="C",(IF(D50<"A",$A50&", ","")),"")&IF(C51="C",(IF(D51<"A",$A51&", ","")),"")&IF(C52="C",(IF(D52<"A",$A52&", ","")),"")&IF(C53="C",(IF(D53<"A",$A53&", ","")),"")&IF(C54="C",(IF(D54<"A",$A54&", ","")),"")&IF(C55="C",(IF(D55<"A",$A55&", ","")),"")&IF(C56="C",(IF(D56<"A",$A56&", ","")),"")&IF(C57="C",(IF(D57<"A",$A57&", ","")),"")&IF(C58="C",(IF(D58<"A",$A58&", ","")),"")&IF(C59="C",(IF(D59<"A",$A59&", ","")),"")&IF(C60="C",(IF(D60<"A",$A60&", ","")),"")&IF(C61="C",(IF(D61<"A",$A61&", ","")),"")&IF(C63="C",(IF(D63<"A",$A63&", ","")),"")&IF(C64="C",(IF(D64<"A",$A64&", ","")),"")&IF(C65="C",(IF(D65<"A",$A65&", ","")),"")&IF(C66="C",(IF(D66<"A",$A66&", ","")),"")&IF(C67="C",(IF(D67<"A",$A67&", ","")),"")&IF(C68="C",(IF(D58<"A",$A68&", ","")),"")&IF(C69="C",(IF(D69<"A",$A69&", ","")),"")&IF(C70="C",(IF(D70<"A",$A70&", ","")),"")&IF(C71="C",(IF(D71<"A",$A71&", ","")),"")&IF(C72="C",(IF(D72<"A",$A72&", ","")),"")&IF(C73="C",(IF(D73<"A",$A73&", ","")),"")&IF(C74="C",(IF(D74<"A",$A74&", ","")),"")&IF(C75="C",(IF(D75<"A",$A75&", ","")),"")&IF(C76="C",(IF(D76<"A",$A76&", ","")),"")&IF(C77="C",(IF(D77<"A",$A77&", ","")),"")&IF(C78="C",(IF(D78<"A",$A78&", ","")),"")&IF(C79="C",(IF(D79<"A",$A79&", ","")),"")&IF(C80="C",(IF(D80<"A",$A80&", ","")),"")&IF(C81="C",(IF(D81<"A",$A81&", ","")),"")&IF(C82="C",(IF(D82<"A",$A82&", ","")),"")&IF(C83="C",(IF(D83<"A",$A83&", ","")),"")&IF(C84="C",(IF(D84<"A",$A84&", ","")),"")&IF(C85="C",(IF(D85<"A",$A85&", ","")),"")&IF(C86="C",(IF(D86<"A",$A86&", ","")),"")&IF(C87="C",(IF(D87<"A",$A87&", ","")),"")&IF(C88="C",(IF(D88<"A",$A88&", ","")),"")&IF(C89="C",(IF(D89<"A",$A89&", ","")),"")&IF(C90="C",(IF(D90<"A",$A90&", ","")),"")&IF(C91="C",(IF(D91<"A",$A91&", ","")),"")&IF(C92="C",(IF(D92<"A",$A92&", ","")),"")&IF(C93="C",(IF(D93<"A",$A93&", ","")),"")&IF(C94="C",(IF(D94<"A",$A94&", ","")),"")&IF(C95="C",(IF(D95<"A",$A95&", ","")),"")&IF(C96="C",(IF(D96<"A",$A96&", ","")),"")&IF(C97="C",(IF(D97<"A",$A97&", ","")),"")&IF(C98="C",(IF(D98<"A",$A98&", ","")),"")&IF(C99="C",(IF(D99<"A",$A99&", ","")),"")&IF(C100="C",(IF(D100<"A",$A100&", ","")),"")&IF(C101="C",(IF(D101<"A",$A101&", ","")),"")&IF(C102="C",(IF(D102<"A",$A102&", ","")),"")&IF(C103="C",(IF(D103<"A",$A103&", ","")),"")&IF(C104="C",(IF(D104<"A",$A104&", ","")),"")&IF(C105="C",(IF(D105<"A",$A105&", ","")),"")&IF(C106="C",(IF(D106<"A",$A106&", ","")),"")
[Code end]


If this has to be done with a Macro, which I suspect, I'm not very good with VBA code, please highlight which things I need to change to match my case if you can? 

Related, I would also like to create a list that shows the names of each scout that has earned an award. Same task, opposite direction. 
ie 
=IF(BU11>"",(BU11&", "),"")&IF(BV11>"",(BV11&", "),"")&IF(BW11>"",(BW11&", "),"")&IF(BX11>"",(BX11&", "),"") &IF(BY11>"",(BY11&", "),"") &IF(BZ11>"",BZ11&", ","")&IF(CA11>"",CA11&", ","")&IF(CB11>"",CB11&", ","") &IF(CC11>"",CC11&", ","")&IF(CD11>"",CD11&", ","")&IF(CE11>"",CE11&", ","")&IF(CF11>"",CF11&", ","")&IF(CG11>"",CG11&", ","")&IF(CH11>"",CH11&", ","")&IF(CI11>"",CI11&", ","")&IF(CJ11>"",CJ11&", ","")&IF(CK11>"",CK11&", ","")&IF(CL11>"",CL11&", ","")&IF(CM11>"",CM11&", ","")&IF(CN11>"",CN11&", ","")

Thank you. 
L Gambrell


I have not seen how to add a file here, but I have an example here https://www.dropbox.com/sh/ckz0vn3zx804hdx/AABHqMhdoRTPuz5qMDd6Ygqta 

And I have not been able to get the mrexcell html maker to work. 
<link rel="File-List" href="<a href=" file:="" c:="" users="" linda="" dropbox="" projects="" excell%20help="" formula%20request_files="" filelist.xml"="" target="_blank"><link id="shLink" href="<a href=" file:="" c:="" users="" linda="" dropbox="" projects="" excell%20help="" formula%20request_files="" sheet001.htm"="" target="_blank"><link id="shLink" href="<a href=" file:="" c:="" users="" linda="" dropbox="" projects="" excell%20help="" formula%20request_files="" sheet002.htm"="" target="_blank"><link id="shLink" href="<a href=" file:="" c:="" users="" linda="" dropbox="" projects="" excell%20help="" formula%20request_files="" sheet003.htm"="" target="_blank"><link id="shLink">

 

[LGambrell]

got another html jeani to work...
trying to put info in here...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Award	Scout1		Scout2		Scout3		Scout4		Secondary request, list names of souts that have earned an award	and list those that have earnedbut not received	Exampl below
2	Aw1	C		C				C		Scout 1,2,4	Scout 1,2,4
3	Aw2	C	A			C				scout 1,3	Scout 3
4	Aw3			C	A			C		Scout 2,4	Scout 4
5	Aw4			C		C	A			Scout 2,3	Scout 2
6
7	All Awards Earned	Aw1,Aw2		Aw1,Aw3,Aw4		Aw2,Aw4		Aw1,Aw3		Requesting help in building two formulas to acomplish this more reasonably.
8	Awards To Give	Aw1		Aw1,Aw4		Aw2		Aw1,Aw3
9	Current Formula for "to give"	Aw1,	* There are between 60-100 possable awards at each of the 6 age levels. Troops can have as many as 30 scouts, sometimes more.
10	*two problems. 1) there is always a comma at the end. 2) this process becomes very combersome as it has to be built for as many as 100 awards and then coppies to 30 cells on 6 diffeent spreadsheets.
11	Example -->	#REF!
Sheet1

 

[LGambrell]

bump.... so is this in the wrong place maybe?