Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Sorting Data in Excel

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Sorting Data in Excel

Resolved · Medium Priority · Version 2003

Reply Fri 14 May 2010, 16:32Delegate Lee said...

Lee has attended:
Excel Advanced course
Access Introduction course
Access Intermediate course

Sorting Data in Excel

Hi Guys,

Can you please help with the following question?

I have two columns of data in Excel, the first is a dealer number and the second is the postcodes they are responsible for. Each dealer has multiple entries one for each post code, so the dealer number is listed many times, once for each valid post code.
What I need to achieve is all of the post codes for each dealer to be in one cell split by a comma. For example,

From
Dealer Post Codes
133 ZE1
133 ZE2
133 ZE3

To
Dealer Post Codes
133 ZE1, ZE2, ZE3

I though about a pivot table but have not been able to make it work. Any suggestions?

For upcoming training course dates see: Pricing & availability

Reply Wed 19 May 2010, 13:41Trainer Andrew said...

RE: Sorting Data in Excel

Hi Lee

Thank you for your question. I will check with one of my colleagues to see if it's possible to do this using functions rather than using VBA programming

Kind regards,
Andrew

Reply Fri 28 May 2010, 10:53Delegate Lee said...

RE: Sorting Data in Excel

I have managed to move this problem on a bit, I now have my postcodes in seperate cells by dealer number. My next problem is how to combine up to 55 cells into one with the postcodes seperated by a comma.

I have looked at cocantenate but cannot work out how to automatically add the comma without manually adding one cell at a time to the formula.

Have,

ZE1 ZE2 ZE3 in three cells.

Need

ZE1, ZE2, ZE3 in one cell.

Excel articles

· Creating And Importing Custom Lists In AutoFill
· Business Forecasting With Microsoft Excel
· Correcting Numerical Glitches In Excel
· The Pursuit of Excellence
· Do You Need To Run Excel 2007 In Compatibility Mode?

Please browse our web site to find out more about
2002 excel microsoft training and other Microsoft training courses.

Excel tip:

Conditional formatting for cells that return text , not picked up by Go to command

If you have tried to format all cells containing text even those that display text as a result of a formula you may have had difficulty. As Go to command with constant selected does not pick up formulas that result in text.

Then try this. Select the range the formula cells appear in on your sheet. Format, select Conditional Formatting menu. In the dialog box under Condition 1, select "Formula Is" from drop down. Next to it in the Formula Box, enter the formula =Istext(A1. Click Format button , choose desired formatting settings and click OK. To go ahead and apply the conditional formatting click OK to accept

View all Excel hints and tips