how do I remove repetitive cells in an excel database?

[jjr1975]

Have a customer database with everyone's name, address, etc. Most of the time
each family member is a client and therefore they have their own line item. I
have direct mailers that I wish to send out to all my customers homes, but I
only want to send one per household. How can I remove the extra rows in the
spreadsheet so I only have one line item per address?? Your help is
apprechiated.

 

[carlo]

Have a customer database with everyone's name, address, etc. Most of the time
each family member is a client and therefore they have their own line item.. I
have direct mailers that I wish to send out to all my customers homes, butI
only want to send one per household. How can I remove the extra rows in the
spreadsheet so I only have one line item per address?? Your help is
apprechiated.

There are probably a lot better solutions, but this one works fine for
me:
I assume you have a headerrow, if not: you need one.
So 1 is your Header Row.
Go to the next new column in Row 2 and enter this formula:
=COUNTIF(INDIRECT("A2:A" & ROW()),A2)
You have to change all "A" with your Address Column. For example if
you have your address in Column H the formula looks like:
=COUNTIF(INDIRECT("H2:H" & ROW()),H2)
Then apply an autofilter to your whole table:
select row 1, go to data --> Filter --> AutoFilter
Go to your new Column and select Custom in the Filter Dropdown and
enter "equals 1"

Then Copy your Table, goto a new sheet A1, right click, paste special
as values.
And then you have a clean database.

hope you understand what i mean, otherwise just tell me and i try to
explain better.

Cheers
Carlo

 

[Tyro]

You can try filtering on unique values with a copy to another region

Tyro

Have a customer database with everyone's name, address, etc. Most of the
time
each family member is a client and therefore they have their own line
item. I
have direct mailers that I wish to send out to all my customers homes, but
I
only want to send one per household. How can I remove the extra rows in
the
spreadsheet so I only have one line item per address?? Your help is
apprechiated.

There are probably a lot better solutions, but this one works fine for
me:
I assume you have a headerrow, if not: you need one.
So 1 is your Header Row.
Go to the next new column in Row 2 and enter this formula:
=COUNTIF(INDIRECT("A2:A" & ROW()),A2)
You have to change all "A" with your Address Column. For example if
you have your address in Column H the formula looks like:
=COUNTIF(INDIRECT("H2:H" & ROW()),H2)
Then apply an autofilter to your whole table:
select row 1, go to data --> Filter --> AutoFilter
Go to your new Column and select Custom in the Filter Dropdown and
enter "equals 1"

Then Copy your Table, goto a new sheet A1, right click, paste special
as values.
And then you have a clean database.

hope you understand what i mean, otherwise just tell me and i try to
explain better.

Cheers
Carlo

 

[Tyro]

I inadvertantly replied to Carlo instead of you. BTW, Excel does not have
databases. Access does.

Tyro

 

[jjr1975]

Carlo,

Thanks for your help. I think I did everything right, but when I put "equals
1" in the custom drop down menu, everything but the header row disappears.
Also when I copy and past special on a new sheet, only the header shows up
without drop downs. Any suggestions??

 

[carlo]

Hi

what exactly did you put in the fields of the custom form?
on the left side, in the upper drop down you have to put "equals",
which usually is default.
Then in the upper text field on the right side you need to put 1 (just
1, no quotation marks and nothing).

hth
Carlo