Multiple rows/Same Address

[dnatoday]

I have a spreadsheet with multiple rows of people who have the same address. I want to eventually create labels that list all the people at the same address on one label.

Is there an easy way to select all the same rows that have the same address & pull the names into one row?

There are anywhere from 1 to 5 people at the same address.

Thanks for you help!
D

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[dnatoday]

Alright. here is an sample of the file I want. The first tab is the original data, the second is the way I would like it to wind up. I guess I would want first to look if there was an original name in a row, then see if their is another row with the same address, then append the row with the multiple name.

the reason I don't want to do this by hand is because I pull the data from a database where we change addresses/data & I have to pull a fresh spreadsheet each time I want to make labels.

Thanks!
Deanna

[NBVC]

I am hoping those are not real names/addresses?

[dnatoday]

@NBVC: I put in fake data, but tried to show the real types of data I have.
Thanks for checking.

[NBVC]

To avoid VBA, you need a few steps...

I put my results in Sheet3 of attached.

First create a list of unique Addresses in Columns A:D of Sheet3.

To do this go to Sheet3, go to Data|Filter|Advanced Filter, then in the List Range field click the range picker and then go to sheet 1 and select columns D:G, then click the data picker button again to expand and then select copy to another location and select cell A1 of Sheet3 for the Copy to field. Check Unique records only box. And click Ok. You should get a unique address listing.

In Original Data sheet enter formula in H2:

=D2&"_"&G2&"_"&COUNTIF(H$1:H1,D2&"_"&G2&"*")+1

copied down. This counts unique addresses based on street address and zipcode (in extreme case that same address exists in 2 locations)....

In Sheet3, E3 enter:

=COUNTIF('Original Data'!H:H,A2&"_"&D2&"*")

copied down. This gives count of matching names to unique address.

In F3:

=INDEX('Original Data'!$A:$A,MATCH($A2&"_"&$D2&"_"&COLUMNS($A$1:A$1),'Original Data'!$H:$H,0))

copied down. This extracts 1st IDs for each match.

In G3:

=IF(COLUMNS($A$1:A$1)>$E2,"",INDEX('Original Data'!$B:$B,MATCH($A2&"_"&$D2&"_"&COLUMNS($A$1:A$1),'Original Data'!$H:$H,0)))

copied down and across as far as necessary to get all names.

If you want to rearrange so that addresses are to the right, just drag columns A:D to where you want them, and then delete the empty columns A to D.

Hope it helps.

[dnatoday]

Thanks for taking the time to help me. I MOSTLY have it working except for this: it is returning one less name than address. It's giving me 2 rows for each address with multiple name, and skipping the first name on each duplicate row.

For rows that only have one name, it is returning #N/A in the index & no names in the name fields.

I moved my "additional name" field to after the zip (column E)-- I still need to print that additional name if it exists, but I knew I didn't want to find unique based on it.

my count unique column (F):
=COUNTIF('Original Data'!H:H,A2&"_"&D2&"*")

my index (pulls id) column (G):
=INDEX('Original Data'!$A:$A,MATCH($A2&"_"&$D2&"_"&COLUMNS($A$1:B$1),'Original Data'!$H:$H,0))

my name columns (H-K)
=IF(COLUMNS($A$1:B$1)>$F2,"",INDEX('Original Data'!$B:$B,MATCH($A2&"_"&$D2&"_"&COLUMNS($A$1:B$1),'Original Data'!$H:$H,0)))

I feel like it must be an issue in my index column--it is not pulling the first ID but the second ID for that unique address.

Can you help?
thanks again,
D

[NBVC]

The formula in H should be:


=IF(COLUMNS($A$1:A$1)>$F2,"",INDEX('Original Data'!$B:$B,MATCH($A2&"_"&$D2&"_"&COLUMNS($A$1:A$1),'Original Data'!$H:$H,0)))

[dnatoday]

Yay! That fixed one part, but I am still getting a double row. I'm going to run the unique address again and see if that fixes it.

You are a god among men and a boon to all poor users without a proper IT department!!!!

[NBVC]

To get the unique list, make sure you are just choose the 4 address columns.. don't include the names or ids in the selection.. you are just filtering the addresses... Also make sure you have headers or it may duplicate the first one.....