Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default how do I remove repetitive cells in an excel database?

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 367
Default how do I remove repetitive cells in an excel database?

On Jan 15, 11:05*am, jjr1975
wrote:
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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default how do I remove repetitive cells in an excel database?

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

Tyro

"carlo" wrote in message
...
On Jan 15, 11:05 am, jjr1975
wrote:
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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default how do I remove repetitive cells in an excel database?

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" wrote:

On Jan 15, 11:05 am, jjr1975
wrote:
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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 367
Default how do I remove repetitive cells in an excel database?

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

On Jan 15, 11:56*am, jjr1975
wrote:
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" wrote:
On Jan 15, 11:05 am, jjr1975
wrote:
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- Hide quoted text -


- Show quoted text -




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default how do I remove repetitive cells in an excel database?

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

Tyro

"jjr1975" wrote in message
...
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Repetitive Data in Excel Exceldummy Excel Worksheet Functions 1 October 16th 06 04:06 PM
Excel hangs when remove some cells Fixthebox Excel Discussion (Misc queries) 1 September 7th 06 01:35 AM
how do i remove multiple entries in an excel database? Chi Excel Discussion (Misc queries) 1 December 2nd 05 01:02 AM
how do I embed/Link pictures onto excel database cells? a0relento Excel Discussion (Misc queries) 0 October 14th 05 05:27 AM
How can I keep database format cells at Excel when using Word Mai. Veronica Excel Discussion (Misc queries) 1 April 11th 05 11:07 PM


All times are GMT +1. The time now is 02:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"