Removing "Blank Cells" from a column
This is my first post on this forum so please bear with me :) I've created a spreadsheet for one of my clients. In the sheet various data is entered. This data is then processed and a new column of data is produced, typically less than 1000 cells. In some cases this new column contains multiple identical entries. I have found a formula which creates a copy of the column and removes duplicate entries; =IF(COUNTIF($D$1:D1,D1)=1,D1,"") - Entered into E1, E2, etc. The problem is that this forumla leaves "blank" cells where duplicate entries were previously. (The cells just show as blank, they actually contain the above formula in them) I'd like to be able to remove these blank spaces and make the data more presentable but I'm having trouble figuring out how to do this. I'd like to try to keep the solution to a formula so that its automated and occurs as soon as the data is entered, and I'm unfamiliar with VBA. If anyone can help me I would really appreciate it. I'm using Excel 2003 if it matters. Thanks all. -- PaulQuincy ------------------------------------------------------------------------ PaulQuincy's Profile: http://www.excelforum.com/member.php...o&userid=31474 View this thread: http://www.excelforum.com/showthread...hreadid=511547 |
Removing "Blank Cells" from a column
One non-array formulas approach ..
Assuming source data in Sheet1, cols A to D (col D = key col) In a new Sheet2, Put in A1: =IF(ISERROR(SMALL($E:$E,ROW())),"", INDEX(Sheet1!A:A,MATCH(SMALL($E:$E,ROW()),$E:$E,0) )) Copy A1 to D1 Put in E1: =IF(Sheet1!D1="","",IF(COUNTIF(Sheet1!$D$1:D1,Shee t1!D1)1,ROW(),"")) Select A1:E1, fill down to cover the max expected data extent in Sheet1 (Format the cols to taste) Sheet2 will return the desired results, with all lines neatly bunched at the top -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "PaulQuincy" wrote in message ... This is my first post on this forum so please bear with me :) I've created a spreadsheet for one of my clients. In the sheet various data is entered. This data is then processed and a new column of data is produced, typically less than 1000 cells. In some cases this new column contains multiple identical entries. I have found a formula which creates a copy of the column and removes duplicate entries; =IF(COUNTIF($D$1:D1,D1)=1,D1,"") - Entered into E1, E2, etc. The problem is that this forumla leaves "blank" cells where duplicate entries were previously. (The cells just show as blank, they actually contain the above formula in them) I'd like to be able to remove these blank spaces and make the data more presentable but I'm having trouble figuring out how to do this. I'd like to try to keep the solution to a formula so that its automated and occurs as soon as the data is entered, and I'm unfamiliar with VBA. If anyone can help me I would really appreciate it. I'm using Excel 2003 if it matters. Thanks all. -- PaulQuincy ------------------------------------------------------------------------ PaulQuincy's Profile: http://www.excelforum.com/member.php...o&userid=31474 View this thread: http://www.excelforum.com/showthread...hreadid=511547 |
Removing "Blank Cells" from a column
I tried what you said but i ended up with a sheet 2 not showing anything at all. Maybe I did something wrong, I'll try again a couple times. To make it easier you can assume that all the data is in column A only (the data is only in a single column) -- PaulQuincy ------------------------------------------------------------------------ PaulQuincy's Profile: http://www.excelforum.com/member.php...o&userid=31474 View this thread: http://www.excelforum.com/showthread...hreadid=511547 |
Removing "Blank Cells" from a column
"PaulQuincy" wrote:
I tried what you said but i ended up with a sheet 2 not showing anything at all. Maybe I did something wrong, I'll try again a couple times. To make it easier you can assume that all the data is in column A only (the data is only in a single column) My apologies, Paul .. the earlier criteria formula (col E in Sheet2) was incorrect Put in E1: =IF(Sheet1!D1="","",IF(COUNTIF(Sheet1!$D$1:D1,Shee t1!D1)1,ROW(),"")) should have read as: Put in E1: =IF(Sheet1!D1="","",IF(COUNTIF(Sheet1!$D$1:D1,Shee t1!D1)1,"",ROW())) [ No change to the other formulas ] (The earlier criteria formula flagged the *duplicates* in col D in Sheet1, instead of the *uniques*) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Removing "Blank Cells" from a column
=IF(COUNTIF($D$1:D1,D1)=1,D1,"") - Entered into E1, E2, etc.
... and perhaps simpler to illustrate (going back to your orig. post) Put instead in E1: =IF(D1="","",IF(COUNTIF($D$1:D1,D1)1,"",ROW())) Put in F1: =IF(ISERROR(SMALL(E:E,ROW())),"", INDEX(D:D,MATCH(SMALL(E:E,ROW()),E:E,0))) Select E1:F1, copy down to say, F1000 to cover the max expected extent of data in col D Col F will auto-return the uniques list from col D, all neatly bunched at the top Here's a quick sample to illustrate the above construct: http://cjoint.com/?cniYDLtDb8 PaulQuincy_wks_1.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
All times are GMT +1. The time now is 12:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com