Amalgamating data with several lines

[slay0r]

Hi All,

I need to amalgamate some data I've got but my minds gone completely blank. The name is all one person but I need all the types to come into one Cell. I'd normally use concatenate but some people haven't had all the tests so the data isn't consistent. Some have only had 2 parts..I basically need it to be one name and the range of tests they had. Is this simple enough to do?

My data looks like:

Name	Surname	DOB	SEX	Occupation	Location	Type
Generic	Generic 2	20/04/1974	Male	Fitter	Test	HAVS Tier 2
Generic	Generic 2	20/04/1974	Male	Fitter	Test	Audiometry
Generic	Generic 2	20/04/1974	Male	Fitter	Test	Skin
Generic	Generic 2	20/04/1974	Male	Fitter	Test	Respiratory

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks so much!

Kind Regards
Jonathan

 

[Special-K99]

Ok we have the input data, but it's probably best to supply what you expect the data to look like, since I can think of several different types of output based on what you've said.

 

[slay0r]

Hi Mate,

Thanks so much for the quick reply.

I'd like it to be one line as below:

Screening Date	Name	Surname	DOB	SEX	Occupation	Location	Type	Price
19/12/2017	Jonathan	Martin	20/04/1974	Male	Fitter	GENERIC	Hearing Lung Skin HAVS

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

All random data before anyone asks!

 

[Special-K99]

This would be easier using VBA but thats not my area of expertise

 

[slay0r]

Yeah I can do everything until I hit that point. If it was numbers I'd be all over it but cause it's text I haven't got a clue. IF it was consistent I'd just use concatenate but it's not

 

[panyagak]

Assuming you data starts from A1 to E1 & separated by spaces, Try this:
=(A1&" "&B1&" "&C1&" "&D1&" "&E1)

 

[slay0r]

I'm not sure what this would achieve? This would bring together everything in those cells but that's not what I require. It needs to roll up what's in Cell E.

 

[panyagak]

Heed Special-K99's advice@ #2 & supply your issue & expected output:

Seems no one understands your problem hence NO CORRECT ANSWERS COMING IN FROM "SUPRA-EXPERTS HERE!!

 

[Joe4]

Yes, I think we need to see some BEFORE and AFTER data to fully understand what it is you are looking for.