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:

[TABLE="width: 1291"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Surname[/TD]
[TD]DOB[/TD]
[TD]SEX[/TD]
[TD]Occupation[/TD]
[TD]Location[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Generic[/TD]
[TD]Generic 2[/TD]
[TD="align: right"]20/04/1974[/TD]
[TD]Male[/TD]
[TD]Fitter[/TD]
[TD]Test[/TD]
[TD]HAVS Tier 2 [/TD]
[/TR]
[TR]
[TD]Generic[/TD]
[TD]Generic 2[/TD]
[TD="align: right"]20/04/1974[/TD]
[TD]Male[/TD]
[TD]Fitter[/TD]
[TD]Test[/TD]
[TD]Audiometry[/TD]
[/TR]
[TR]
[TD]Generic[/TD]
[TD]Generic 2[/TD]
[TD="align: right"]20/04/1974[/TD]
[TD]Male[/TD]
[TD]Fitter[/TD]
[TD]Test[/TD]
[TD]Skin[/TD]
[/TR]
[TR]
[TD]Generic[/TD]
[TD]Generic 2[/TD]
[TD="align: right"]20/04/1974[/TD]
[TD]Male[/TD]
[TD]Fitter[/TD]
[TD]Test[/TD]
[TD]Respiratory
[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 1488"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Screening Date[/TD]
[TD]Name[/TD]
[TD]Surname[/TD]
[TD]DOB[/TD]
[TD]SEX[/TD]
[TD]Occupation[/TD]
[TD]Location[/TD]
[TD]Type[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2017[/TD]
[TD]Jonathan [/TD]
[TD]Martin[/TD]
[TD="align: right"]20/04/1974[/TD]
[TD]Male[/TD]
[TD]Fitter[/TD]
[TD]GENERIC [/TD]
[TD]Hearing Lung Skin HAVS [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.