Moving A Row Of Data Into One Cell

[bobcobb]

Hey everyone at Excel Forum. I just started a new project at work and need a little bit of help.

Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.

Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).

The commas are not necessary, but is there any way to get this done other than manually entering them?

Thanks in advance for any help.

[ChemistB]

If the zips are in columns B:F starting in row 2, In G2
=B2 &", " &C2 &", " & D2&", " & E2 & &", " & F2

If there are not always the same number of zip codes, best to skip the commas and do something like
= TRIM(B2 &" " & C2 &" " & D2 &" " & E2 &" " & F2)
You can copy and Paste Special > Values to remove the formulas
Does that work for you?

[bobcobb]

Hey Chemist,

Yeah, that works, but that's pretty much like the Concatenate formula, where I'll need to copy every cell number and letter anyway. This will help in the long run as I can simply pull that formula down for the rest of the worksheet, but this is about 80 numbers, is there anyway to mass add them, or do I have to add each cell one by one into that formula?

[bobcobb]

I just tried this and I think it works. Is there something I can do to still pull down this formula for other sales reps that don't have as many zip codes and not end up with a bunch of commas in the cell?

[ChemistB]

You can use a UDF (User defined function) which uses VBA (Visual basic for Applications). Here's one by Tigeravatar.
How to enter it
1. Copy the code, In your workbook, hit ALT F11 (to open vba editor)
2. Insert> Module in the editor
3. Paste the code into the white text box.
4. Close the editor
How to use it
1. Has the format = ConcatAll(range, delimiter) for example = ConcatAll(B2:F2, ", ")
2. Macros need to be enabled for it to work. Sheet must be saved as an xls or an xlsm workbook

Please Login or Register  to view this content.

Questions?

[ChemistB]

Re; Post #4
You'll note in my second formula in Post #2, I didn't include the commas. The TRIM will remove the extra spaces. If you want the commas, after you paste special > values, you can select the column and CNTRL H replace space with ,space.

[bobcobb]

Thank you so much, Chemist. This was a big help.