Duplicates?

[txbullets]

Attachment 346483Not sure if consolidating is the right term here. Have searched some but not sure what to look for. I have a list where companies are repeated multiple times with the same address and some with different address. I am trying to sort by address and have all the repeated address deleted so that I only have one set of data for each address. The purpose of this is to put it into Mappoint so I can map the addresses out so it needs to stay in a format to which I can load it to Mappoint.

I have deleted all the contact names and other useless info for this task and am down to the following fields required for Mappoint

Customer Name
Customer Address
City
State
Zip

Example of my problem

Cust Name.............Address..........City..........State...........Zip
ABC Comp................111 abc Ln......Houston.......TX...........77505
ABC Comp................111 abc ln.......Houston.......TX..........77505
EFG Comp................222 EFG Ln......Baytown......TX...........77520
EFG Comp................222 EFG Ln......Baytown......TX...........77520


So I would need to delete the duplicate for each company. There are some that have different addresses and I would want to leave those alone. Basically I need to sort by address and at each change of address I need the info and need to delete the repetitive inbetween.

[mikeTRON]

Post a sample workbook with sampel data, but I think you can use a pivot table and be done with this in a few seconds.

[txbullets]

Sorry it took me a bit to respond. I need to updated my pivot table skills some. Have not used it in a while so gonna play with that and see if it will work. When I use pivot tables I am not getting the format I need. I will try to manipulate it.

[mikeTRON]

Post a sample dataset and I will do it for you, its really pretty easy once you start playing around with them.

http://chandoo.org/wp/excel-pivot-tables/
http://chandoo.org/wp/2009/08/19/exc...bles-tutorial/
http://chandoo.org/wp/2010/01/27/pivot-table-tricks/

[mikeTRON]

Also, you can use the native function Remove Duplicates.
http://office.microsoft.com/en-us/ex...001034626.aspx

[txbullets]

Post a sample dataset and I will do it for you, its really pretty easy once you start playing around with them.

http://chandoo.org/wp/excel-pivot-tables/
http://chandoo.org/wp/2009/08/19/exc...bles-tutorial/
http://chandoo.org/wp/2010/01/27/pivot-table-tricks/

I am missing something on the pivot table because I can not get the labels right or it wants to sum it. I tried changing it to product and other things but can't. So here it is. There are different contacts which I deleted so there are multiple addresses for one company. at every new address I want a row with column labels as they are now in the sheet. Just delete every row that is a duplicate of the address above it. I will load the Excel sheet as to the first post here in a second

[txbullets]

sample attached.

[Punnam]

Hi,

This can be done by array function,But untill if you have same data twice of more than that it can be done as shown in the attachment.

Incase of same company difference in other information ,Provide a sample workbook without any confidential data.

Punnam

[Punnam]

@ txbullets ,

What is your expected result.

Punnam

[Vikas_Gautam]

Hi..
1. Just select the Range from A2:D5 then Click "Remove Duplicates" in the DATA Tab ...
2. Uncheck "My Data has Headers"
3. Select the column on which unique items are to be captured and unselect others...
4. Click Ok..

You data has been reduced to unique rows based on selection of your BASE Column..

[txbullets]

@ txbullets ,

What is your expected result.

Punnam

Pretty much what you did. I am trying to load them into a mappoint software so I can map it so I don't need repeat addresses. Some companies have multiple locations so I will need those companies listed for each address so I can map each location.

[txbullets]

OK I feel dumb. This is exactly what I needed. Thank you! That was too easy!

[Punnam]

Let us take the example of
first company 11111111 it has information in four rows out which 5 have same ADDR1 = HC 2, BOX 756 & row 3 &6 are different but the Pin is same & CONTACT is different for 5 of them so which would you like take as criteria .?

Punnam