Combining Range of Text Values Based

tigertown

New Member
Joined
Jun 23, 2016
Messages
2
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

tigertown

New Member
Joined
Jun 23, 2016
Messages
2
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
sitechemical
site1chem1
site2chem9Count of chemicalchemical
site3chem8sitechem1chem2chem3chem4chem5chem6chem7chem8chem9Grand Total
site1chem8site11 1 114
site2chem3site2 11114
site3chem1site311114
site1chem6Grand Total21111112212
site2chem4
site3chem2
site1chem9
site2chem5site11.000061.000111.000131.00014
site3chem7site21.000081.000091.00011.00014
site31.000061.000071.000121.00013
123456789
site11.000141.000131.000111.00006
site21.000141.00011.000091.00008
site31.000131.000121.000071.00006
row 21site1chem9chem8chem6chem1
site2chem9chem5chem4chem3
site3chem8chem7chem2chem1
col Ecol 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>
 

Forum statistics

Threads
1,144,388
Messages
5,724,070
Members
422,533
Latest member
garganod1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top