Range of data copied to cell on different sheet, remove duplicates

**pjbassdc** · 07-21-2011, 11:50 AM

Greetings,

Attached are 3 sheets of this sample workbook. Tech Form, Data, and Log Sheet. I am attempting to copy my selections from the Tech form C19:C26 to the Log sheet in cell J2. The responses need to be truncated to the first word of each cell C19:C26 on the Tech Form and duplicate responses need to be eliminated from the copy to J2 on the log sheet.

Example: C19 SSEPs (Ulnar)
C20 SSEPs (Posterior Tibial)
C21 TceMEP (Upper)
C22 TceMEP (Lower)
C23 TOF
C24 S-EMG (2 extremities)
C25 EEG
C26 Cortical_Mapping

Desired output in this example to J2 on log sheet: SSEPs, TceMEP, TOF, S-EMG, EEG, Cortical_Mapping

The Tech Form drop down responses being worked with are being pulled from the DATA sheet in Column K.

The Range of C19:C26 on the Tech Form is not always fully utilized. Sometimes we may have just a couple of entries. Also the selection order may be different from one user to another.

Anyone in the forum have a solution for this? I am relatively new to excel and I am finding the learning curve fairly steep for the things I am trying to accomplish on this project. Solutions w/ explanation and or assistance on this issue is greatly appreciated.

Thank you,

Patrick

**RichardWinter** · 07-21-2011, 12:30 PM

Patrick,
I only can think of a VBA solution using a Collection for this task.
Not sure whether you want to go this route. If so, let me know and I can put some code together in your example book.

**pjbassdc** · 07-22-2011, 08:26 AM

Richard,

Thank you for your kind offer. I know nothing of VBA coding. If you think that this is the best option, I would love to see what you come up with. If you need more information, please ask.

Thanks again,

Patrick

**RichardWinter** · 07-23-2011, 08:00 AM

Patrick,
I wrote a little UDF (user defined function) for you, which should do what you need.
It's generic and can be used everywhere to perform the task of concatenation of cell values of a range.
The default arguments of the function are set to your requirements but can be changed when required. See the comments in the code for more details.
For splitting the cell values to use only the first element I have used a function provided to the Excel-L community by Duane Hookum.
I have implemented it in the attached sample sheet .

Have fun
Richard

Please Login or Register  to view this content.

**martindwilson** · 07-23-2011, 08:28 AM

like this with formulas
edit changed to allow for blanks also reset last cell on tech form sheet to reduce workbook size

**pjbassdc** · 07-25-2011, 09:42 PM

A very big thank you to Richard and Martin for your input and help crafting a solution to my original post. Both methods work well and will go a long way in helping with my current and future projects.

Cheers,
Patrick