Quick excel question

Teledude · 1 Dec 2008 at 09:54

Got a spreadsheet full of names and address's and I want to condense this to single names at each address - any ideas?!

Gilly · 1 Dec 2008 at 10:01

Are the names in multiple columns?

Teledude · 1 Dec 2008 at 10:11

First names in B and Surnames in C.

edit - and not all surnames at the same address match to make it harder

LeJosh · 1 Dec 2008 at 10:25

Can't you centre and merge them all? Or convert it into a database file and work some magic from there. A form maybe then fill it out with forename, surname ,address telephone number etc. etc.

Teledude · 1 Dec 2008 at 10:27

Merge them how?

Participant · 1 Dec 2008 at 10:33

=B1&" "&C1

Teledude · 1 Dec 2008 at 10:43

Robbie G said:
=B1&" "&C1

gives me there full name but doesnt help :confused:

LeJosh · 1 Dec 2008 at 10:46

Teledude said:
Merge them how?

Highlight the two names then centre and merge button on the toolbar. Then alter the addresses where needed.

Royal Fleet · 1 Dec 2008 at 10:55

If you mean that you need to merge the contents of two cells into one then use the concatenate command.

e.g. =Concatenate(A1, A2)

Teledude · 1 Dec 2008 at 11:02

I dont want to merge anything I need to pull out unique address's

Participant · 1 Dec 2008 at 11:09

Teledude said:
Got a spreadsheet full of names and address's and I want to condense this to single names at each address - any ideas?!

Teledude said:
gives me there full name but doesnt help

If my solution doesn't help you "condense this to single names at each address" then I haven't understood your problem. By the looks of it neither has anyone else in this thread, so potentially you could try explaining yourself better :confused:

.

Royal Fleet said:
If you mean that you need to merge the contents of two cells into one then use the concatenate command.

e.g. =Concatenate(A1, A2)

My formula does exactly the same as concatenate, only doesn't require typing a long word

.

Teledude said:
I dont want to merge anything I need to pull out unique address's

So you want to condense the names but not merge them? Now totally confused.

Royal Fleet · 1 Dec 2008 at 12:16

Teledude said:
I dont want to merge anything I need to pull out unique address's

You could use a Pivot table then. Drop Address into the Drop Row Fields here, and the count of it into the Data Items. Will show you which ones are duplicates, or you could just C&P from the Row Fields into a new list.

Nanobot · 1 Dec 2008 at 12:48

Confused too. Can you post a sample here with commas to show current layout as-is and a sample of what you want to achieve. I too thought that single names at each address would be achieved with concatenate. :confused:

e.g.

Before:
Joe,Bloggs,1 Any Street, Anytown, Any Code

After:
?

PS For unique records, try Data > Filter > Advanced... There are options there for unique records in a selection.

gillywibble · 1 Dec 2008 at 13:47

Sort Data?