Dissolve One Field, Append to another

Hello,

I am trying something new and I didn't know what to search for in the forums. I have created a key map, where every page in the book is a different part of the city. The pages run sequentially (ie A1, A2, A3, B1, B2, B3). At the end there is an index with every street name listed. Next to each street name are the page numbers the street falls on. I have an Excel document, attached here, showing all the street names in one column and page number in another column. However, a street may be listed multiple times if it falls on multiple pages. I need to be able to "dissolve" the street names so they are listed once and append the page column to the street column. Therefore it will go from:

Street Name
Aaron St -------- D3
Aaron St -------- D4
Aaron St -------- D5
Abby Ln -------- C2
Abby Ln -------- D2

To This:

Street Name PageName
Aaron St: D3; D4; D5
Abby Ln: C2; D2

I hope that makes sense. Thank you.

In D3, dragged across as far as you need to and then down

=IF(AND(COUNTIF($B3:$B$701,$B3)>COLUMN(A1),COUNTIF($B$3:$B$701,$B3)=COUNTIF($B3:$B$701,$B3)),INDIRECT("$C"&COLUMN(D1)),"")

You can then copy and paste special>values to remove the formulas, Filter on Column D to delete duplicates and use concatinate to put them in single cells. Does that work for you?

When the formula is posted, the forum automatically puts a space every 50 characters (anti spam I think). Remove the space in INDIRECT.

Sorry, mistake in my formula. In D3

=IF(AND(COUNTIF($B3:$B$701,$B3)>COLUMN(A1),COUNTIF($B$3:$B$701,$B3)=COUNTIF($B3:$B$701,$B3)),INDIRECT("$C"&COLUMN(D1)+ROW(B3)-3),"")

See attachment.

If either

a) you wish concatenate into one string

b) you wish to purge data post "consolidation"

You would be better off using VBA.

If, however, you wish to leave the original listing as is and simply generate an alternate view of the same data then perhaps the attached will be of interest ?

Floyd, did you look at my attachment? It looks like you didn't drag the formula to the right.

See this thread

http://www.excelforum.com/excel-gene...tire-rows.html

In D3 dragged down, using this formula instead
=IF(B2&C2=B3&C3,"Delete","Keep")
Hope that helps.

No, it should work as long as the entries are in order(ie. for a single street don't have E3, D3, E3, D3 It should be D3, D3, E3, E3). I checked "Asbury Lane" for example, has 2 deletes. The formula should be comparing the data in that row to the data in the previous row.