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]

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