0

Using the example from this question:

Excel - Merge rows with common values and concatenate the differences in one column

How can I change:

Customer Name   |   NEW YORK    |   ALBANY 
Customer Name   |   NEW YORK    |   CLINTON    
Customer Name   |   NEW YORK    |   COLUMBIA
Customer Name   |   NEW YORK    |   DELAWARE
Customer Name   |   NEW YORK    |   DUTCHESS  
Customer Name   |   VERMONT |   BENNINGTON  
Customer Name   |   VERMONT |   CALEDONIA
Customer Name   |   VERMONT |   CHITTENDEN
Customer Name   |   VERMONT |   ESSEX
Customer Name   |   VERMONT |   FRANKLIN

to this:

Customer Name   |   VERMONT |   BENNINGTON,CALEDONIA,CHITTENDEN,ESSEX,FRANKLIN
Customer Name   |   NEW YORK    |   ALBANY,CLINTON,COLUMBIA,DELAWARE,DUTCHESS

where | denotes a cell. The answer given in the question above was for a macro. I need to create a manageable template and most people do not know how to manage macros. Thus, I need a formula to do this. Can anyone help out?

Community
  • 1
  • 1
rphello101
  • 1,671
  • 5
  • 31
  • 59

2 Answers2

2

If your data is sorted by state, you can use a formula column and a helper column to make it simple. First column, you concatenate the states

in D2:

=if(B2=B1,D1&C2&",",C2&",")

in the second column, you can put a filter that tells you if the list is finished

=if(B2=B1,"","State Complete")

You can filter on the State Complete value and get your results.

If you're trying to go a lot more fancy than that, you'll need macros or user-defined functions.

nutsch
  • 5,922
  • 2
  • 20
  • 35
  • This is an elegant solution, but unfortunately the information I have is not sorted (and can't be). I will indeed need a fancy solution... – rphello101 Apr 17 '14 at 22:34
0

Similar but a singe formula (and a 'wheeze'), assuming ALBANY is in C2, etc: 

=IF(B1=B2,D1&","&C2,C2)&IF(B2<>B3,".","")

The full stop (period) is to identify the last of each set (which are assumed to be sorted and for ColumnC TRIMmed if necessary - also assumes no full stops in Column C).

A formula will not delete rows so either filter to select rows with cells containing full stops or copy the column, Paste Special Values over the top, filter and delete those not containing a full stop. I'd prefer the latter as presumably ColumnC itself should be deleted.

The periods could be removed with Find and Replace.

pnuts
  • 58,317
  • 11
  • 87
  • 139