Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repetitive Data in Excel | Excel Worksheet Functions | |||
Excel hangs when remove some cells | Excel Discussion (Misc queries) | |||
how do i remove multiple entries in an excel database? | Excel Discussion (Misc queries) | |||
how do I embed/Link pictures onto excel database cells? | Excel Discussion (Misc queries) | |||
How can I keep database format cells at Excel when using Word Mai. | Excel Discussion (Misc queries) |