Combining data for a map index

[noodles91380]

Hey, I've searched through this forum for an answer to my question, the problem is...I don't know how to word it exactly. I'm making a road map, and the kind with a grid and a street reference guide. What I have is a table with two columns: road names and grid cells. It appears like this now:

Oak St. E2
Oak St. E3
Oak St. F3
Elm St. H5
Maple St. A1
Maple St. B1
etc.

What I want is this:

Oak St. E2, E3, F3
Elm St. H5
Maple St. A1, B1

etc.

Any clue how to do that?

[ChemistB]

I assume you have a unique list of streetnames on a different sheet. (let's say col A of sheet2). In Column B try this array formula (can be dragged right and down)

=IF(COLUMNS($B2:B2)>COUNTIF(Sheet1!$A$2:$A$7,$A10),"",INDEX(Sheet1!$B$2:$B$7,SMALL((Sheet1!$A$2:$A$7=$A10)*ROW($A$1:$A$6),COLUMNS($B$2:B$2)+COUNTIF(Sheet1!$A$2:$A$7,"<>"&$A10))))

You need to enter this using CNTRL + SHFT + ENTER rather than a simple ENTER. You'll see {} around your formula if you do it right. Does that work for you?

[noodles91380]

Hey, thanks for the reply! I'm a light Excel user, I do most of my mapping work in ArcGIS...so apologies if I don't follow some of the things that would likely be implied.

Copying that formula in my Column B (in sheet 2 I assumed), and using CTL-SHFT-ENT to ensure the formula was contained within {} didn't seem to quite work, the cell remained blank.

I attached the file if anybody wants to take a look at it. I'm sure I'm just missing something completely obvious, but again, I'm not the heaviest Excel user... Sheet 3 is just an example of how it needs to look.

Thanks!

[ChemistB]

You needed to match your cell references to fit your data. Here's the updated sheet.

Let me know if you have any questions.

[noodles91380]

Fantastic! I will be sure to dig into that formula to understand what you did (I don't want to just freeload off your knowledge). But I truly appreciate it, thanks again!