Combining Range of Text Values Based

[tigertown]

Hello,
I am having an issue with text and a lack of VBA knowledge. For some reason the "my computer" window won't load (I'm not computer savvy at all)so I can't access my screen shot which I am very sorry about, but here is the issue: I am organizing the number of chemicals found at a particular site ID with site ID being it's own column. The problem is that the database I got the values from creates a new row with the same site ID for each chemical found at the site. So in column D (site ID) I may have 4rows of site GA046 because there were 4 chemicals found there while below there are 20 rows of the same site ID with 20 corresponding chemicals in the chemical column. I wish to combine all the text values in the chemical column based on their the site ID they share. I would rather not concatenate because it's over 1000 rows but if I knew a function that group all the chemicals found at a site in a new cell based on the fact that they share a site ID, it would be a big help. Again, sorry I don't have a picture!
Thank you!

 

[oldbrewer]

copy A1:T10 and paste here in the reply box

 

[tigertown]

textbox20 textbox24 textbox22
10/1/2011 All Georgia

intStateID textbox71 Code intActivityTypeLID intDocumentStatusLID intCoopAgreementLID dtmDistributed bitHODIncluded bitEIIncluded intDocumentIssueLID textbox205 textbox186 CASID bitDriver
GA Site GA0210020046 HC Final Release Georgia 9/21/2015 N N C 410 C/C/Oth ; C/P/Oth 1,2-DICHLOROETHANE N
GA Site GA0210020046 HC Final Release Georgia 9/21/2015 N N C 410 C/C/Oth ; C/P/Oth 1,3-BUTADIENE N
GA Site GA0210020046 HC Final Release Georgia 9/21/2015 N N C 410 C/C/Oth ; C/P/Oth BENZENE N
GA Site GA0210020046 HC Final Release Georgia 9/21/2015 N N C 410 C/C/Oth ; C/P/Oth CARBON TETRACHLORIDE N
GA Site GA0210020046 HC Final Release Georgia 9/21/2015 N N C 410 C/C/Oth ; C/P/Oth CHLOROFORM N
GA Site GA0210020046 HC Final Release Georgia 9/21/2015 N N C 410 C/C/Oth ; C/P/Oth TRICHLOROETHYLENE N
GA Site GAD099303182 PHA Final Release Georgia 4/16/2014 N N C 210 C/F/Rec MERCURY Y

 

[oldbrewer]

site	chemical
site1	chem1
site2	chem9			Count of chemical	chemical
site3	chem8			site	chem1	chem2	chem3	chem4	chem5	chem6	chem7	chem8	chem9	Grand Total
site1	chem8			site1	1					1		1	1	4
site2	chem3			site2			1	1	1				1	4
site3	chem1			site3	1	1					1	1		4
site1	chem6			Grand Total	2	1	1	1	1	1	1	2	2	12
site2	chem4
site3	chem2
site1	chem9
site2	chem5			site1	1.00006					1.00011		1.00013	1.00014
site3	chem7			site2			1.00008	1.00009	1.0001				1.00014
				site3	1.00006	1.00007					1.00012	1.00013
					1	2	3	4	5	6	7	8	9
				site1	1.00014	1.00013	1.00011	1.00006
				site2	1.00014	1.0001	1.00009	1.00008
				site3	1.00013	1.00012	1.00007	1.00006
		row 21		site1	chem9	chem8	chem6	chem1
				site2	chem9	chem5	chem4	chem3
				site3	chem8	chem7	chem2	chem1
				col E									col N
				sledge hammer to crack nut….
				pivot table to find the chemicals
				1's converted to ascending values by using column number
				numbers arranged in descending order
				offset match used to convert back to chemical name
				not sure what next maybe name each row of chemicals eg row 21 F21:N21 named as "site1chemicals"

<colgroup><col span="4"><col><col span="9"><col><col></colgroup><tbody>
</tbody>