Hi guys,
I'm running a formula on a column of data that will result in either a number or a blank in column B. What I want to do is include a fomula that will automatically scan this new column and remove any blanks and put it into column C. Almost like an auto remove duplicates formula that copies to the next column.
Can this be done?
I've included the sheet...
For my formula to work you need to have a header row, so I've inserted a new row 1 in the attached file and then put this array* formula in C2:
=IFERROR(INDEX($B$2:$B$223,MATCH(0,COUNTIF($C$1:C1,$B$2:$B$223),0)),"")
and then copied it down to the bottom of your data in column B.
*An array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.
Hope this helps.
Pete
ef112_ePDM%20Checking%20Tool(1).xlsxPete, you're a star, that's a lovely formula. When i paste another list in A tho it's giving me a space in C3. I need the formula to remove any spaces from the list. I've attached the file!
You also have a zero at the end of the list - this is because you've extended the range to row 3000, where you have real blanks (empty cells).
One way of getting rid of the empty cell would be to apply an auto-sort formula in column D, which would also sort the extracted unique numbers in order.
I'm a bit busy at the moment, though, so maybe someone else will chip in.
Pete
I used this formula. It's not an array formula and a little more complex but it should remove all spaces.
=IFERROR(INDEX($B$1:$B$500,SMALL(INDEX(ROW($B$1:$B$500)+($B$1:$B$500="")*10000,0),ROW(A1))),"")
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hey ChemistB!
I'm afraid it didn't work. It's returning FALSE for every line now!
Okay, first, it looks like you pasted the formula into the cell multiple times. Clear Column C and paste this formula into C2
=IFERROR(INDEX($B$2:$B$500,SMALL(INDEX(ROW($B$2:$B$500) -1+($B$2:$B$500="")*10000,0),ROW(A1))),"")
Because you are starting in row 2, I modified the range and substracted 1 from the formula (in red).
Does that work for you?
Hi ChemistB,
Thanks again for the reply. It's now displaying TRUE all the way down but I need the C Column to just display everything except the blanks in Column B.
@controlfreak
Because you asked me to have a look at this thread - I would use:
In C2: =VLOOKUP("?*",B$2:B$1000,1,0)
In C3 and down: =IF(COUNTIF(B$2:B$1000,"?*")>ROWS(B$2:B2),VLOOKUP("?*",INDEX(B$2:B$1000,MATCH(C2,B$2:B$1000,0)+1):B$1000,1,0),"")
change the references of 1000 to whatever the last row of your range is.
Last edited by Cutter; 06-19-2012 at 12:02 PM.
Not sure what you are doing but my guess is you didn't select column C and clear contents. Here is your spreadsheet with the formula in it.
Guys, ye did it, thank you so much!!
..And another option, here
Not Array.
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
@controlfreak
You're welcome. Thanks for the 'star tap'. Don't forget to mark your thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks