Converting data in >20 cells in a column - into one cell

  • Thread starter Thread starter JL
  • Start date Start date
J

JL

I have a database that shows a market number in column A
and the related salespeople in column B. There can be up
to 20 salespeople per market. Each row shows marketID and
salesperson. I need to convert that to one row per market
with all the salespersons' names combined but seperated by
commas in the adjacent cell. (Concatenate won't handle
that many cells.)

Excel also needs to determine when the market changes, and
to only combine the appropriate salespeople in the cell
adjacent to the related market.

234 Bob
234 Sam
234 Mary
237 Sue
237 Joe

becomes

234 Bob, Sam, Mary
237 Sue, Joe

I would really appreciate any help in this matter!

JL
 
I think a pivot table is the way to go.

1. Set up a PT using your list headings Market & Name without any
totals.
Rows : 'Market' and 'Name'
Data : 'Count of Name'
Options : Remove checks from Grand totals Rows/Columns.

2. PT field 'Market' - doubleclick. Set Subtotals to None
PT Field 'Name' - ensure "Show items with no data" is unchecked.

3. With the list supplied this gives me data in cells A3:C7 with
headings in row 2 Market,Name,Total. We ignore the last one.

4. In cell D3 copy/paste the formula
=IF(A3<>"",CONCATENATE(A3," ",B3),CONCATENATE(E2,",",B3))
and copy down.

5. Finally, to extract only the required rows into column E, in cell E3
put formula =IF(A4<>"",D3,""). (NB.This will leave out the bottom data
row, so put something into the row underneath, column A). You could
combine this with the formula above in column D to keep everything in
that column, but let's keep it simple to start with.

6. Column E Copy/Paste Special/Values to another sheet and sort to
remove blanks.

7. You could set up the PT with Names as columns, but this gives
problems with making the string.
 
...
...
Excel also needs to determine when the market changes, and
to only combine the appropriate salespeople in the cell
adjacent to the related market.

234 Bob
234 Sam
234 Mary
237 Sue
237 Joe
Click to expand...

Is this table always sorted on market number?
becomes

234 Bob, Sam, Mary
237 Sue, Joe
Click to expand...

Text manipulation isn't something pivot tables do well. Here's an approach that
uses only worksheet formulas.

Name the original table TBL. For my own simplicity, I'll also assume it's in
A2:B6. I'll assume the results table has G2 as top-left cell.

You'll need an additional column appended to TBL to handle the concatenations.
There's no way around that without add-ins or VBA. Enter the following formulas.

C2:
=B2

C3: [array formula]
=IF(COUNTIF(A$2:A2,A3),OFFSET(C$2,MAX(COUNTIF(A3,A$2:A2)*(ROW(A$2:A2)
-CELL("Row",A$2))),0)&", ","")&B3

Select C3 and fill down into C4:C6. Then enter the following formulas in the
results table.

G2:
=INDEX(TBL,1,1)

H2: [array formula]
=OFFSET(TBL,MAX(COUNTIF(G2,INDEX(TBL,0,1))*(ROW(TBL)-CELL("Row",TBL))),2,1,1)

G3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(G$2:G2,INDEX(TBL,0,1)),0),1)

Select G3 and fill down as far as needed. The formula will evaluate to #N/A when
the distinct market numbers in the first column of TBL have been exhausted. Then
select H2 and fill down to match the formulas in column G.

These formulas *don't* require that TBL is sorted by market number.
 
Back
Top