Compile Sheets Contents

**ashpuk** · 10-06-2008, 08:56 AM

Hi there,

I am a new user to excel and have searched the forum for an answer..

Have tried a few things but can't get it to work properly. I have attached the file I am working with because it is much easier to understand if you just look at it!

Basically, I have a single sheet with three columns:

Name, Post Code, Tenant

There are varying instances of 'Name' and each one has a different 'Tenant'.

What I need to do is merge the contents of the Tenants cells so that for each different 'Name' there is only 1 instance and then for Tenants all the different cells are merged i.e. "Tenant 1, Tenant2, Tenant3...etc"

I hope this makes sense, as I mentioned above it will be a lot clearer if you have a quick look at the attached spreadsheet.

Thanks for any light you may be able to shed on the subject...

Ash

**MickG** · 10-06-2008, 09:22 AM

Hi, Do you mean you want to merge all the tenants relating to a single "Name" in one cell, or do you want to place them in single cells in the row, relating to that tenant.
In essence transpose each individual "Name" in its own row with all its Tenants
What about the "Post Codes" ???
Regards Mick

**ashpuk** · 10-06-2008, 09:37 AM

Hi Mick, thanks for your reply,

I want to get the list so that there is only one instance of each "Name".

At the moment a "Name" may be repeated 5 times if there are 5 different "Tenants" associated with that name.

EG:

In my attached file, 28 East Retail Park is repeated 9 times for each different retailer on the park.

I want to compile all the tenants so that 28 East Retail Park is only listed once and then the tenants cell displays "JJBSportsSuperHealth, CometSuperstore, VacantNew, AlliedCarpets..etc"

The Post Code is the same for each retail park so this should not really be an issue.

Hope this makes sense? - Please let me know if not,

Thanks

**StephenR** · 10-06-2008, 10:25 AM

You could do something half-decent with a pivot table, but here is some code:

Please Login or Register  to view this content.

**MickG** · 10-06-2008, 10:30 AM

Hi, Please find Attached your file returned:-
I've copied the Data to Sheet (2) and Transposed it in sheet (3)
See Macro Button on sheet (2)
Regards Mick

**ashpuk** · 10-06-2008, 10:41 AM

Mate, that is amazing

I quick question though - is there anyway we can get all the tenants into 1 cell and separate them by a comma? Also how would you keep the postcode column?

I have had a look at the VB Code but dont really understand it

Otherwise that is spot on - THANKS!!

**StephenR** · 10-06-2008, 10:49 AM

Are you saying the code is spot on or you don't understand it so you haven't tried it?

**ashpuk** · 10-06-2008, 10:54 AM

Sorry that reply was to MickG,

Have just tried your code and that also works great too. Only problem is that it does not include the postcodes in the results? Think that is my fault because of confusing post before..

What would you add to your code to include postcodes? cheers

**MickG** · 10-06-2008, 10:56 AM

Hi Try this, File format as Before.
Regards Mick

**StephenR** · 10-06-2008, 10:57 AM

Just change line below as follows:

Please Login or Register  to view this content.

**ashpuk** · 10-06-2008, 11:05 AM

Steve - sorry to be a pain, just changed the line of code as you suggested and it outputs the postcode in the same cell as the scheme - I need the postcode to be in a separate cell I'm afraid.

Thanks so much for ur help!

**ashpuk** · 10-06-2008, 11:06 AM

Sorry mick, just got onto your response too. It works great but when I try and do it for the full list it says out of memory. Hopefully if steve comes back to me it should be sorted now anyway!

Thanks so much for your help it is massively appreciated!

**StephenR** · 10-06-2008, 11:20 AM

Try this:

Please Login or Register  to view this content.

**ashpuk** · 10-06-2008, 11:24 AM

cheers mate that is perfect

**ashpuk** · 10-06-2008, 11:35 AM

Hi Sorry to reopen the thread but I have just realised that i cannot export the data that SteveR's code generates. I get error message saying:
'The information cannot be pasted because the Copy area and the paste area are not the same size and shape'.

Whats going on?!

Thanks