how do I remove repetitive cells in an excel database?


J

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.
 
Ad

Advertisements

C

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
 
T

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
 
T

Tyro

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

Tyro
 
J

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??
 
Ad

Advertisements

C

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top