LinkBack URL
About LinkBacks
Hi,
I have COLUMN B which goes from B2:B4000. Most cells have a text entry but a number of them are empty. I would like to put the entire list in COLUMN C less the blank entries. I want to use a formula, not a filter.
Appreciate any help.
Thanks
TB
Hi,
I have COLUMN B which goes from B2:B4000. Most cells have a text entry but a number of them are empty. I would like to put the entire list in COLUMN C less the blank entries. I want to use a formula, not a filter.
Appreciate any help.
Thanks
TB
Try:
Enter this formula in an available cell:
Please Login or Register to view this content.where X1 is the cell you entered the Countif formula in.Please Login or Register to view this content.
then the formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER...
And then copy it down to C4000
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
I put your firdt code into COLUMN E and it returned a vale of 3378.
I put the second value into COLUMN F and the cells all took on a blue outline. and only cell F2 returned an entry?
Thanks for trying.
Here is an example of the 2 columns I have. I put a formula in column C to add the "A". I want Column D to list columnC without the spaces. I hope that is a little clearer;
Cant get the columns to separate. The entries "A-LS" are in column c.
COLUMN B COLUMN C
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
LS A - LS
MARCOM
JAG
LS A - LS
ADM(PA)
ADM(PA)
ADM(PA)
AIRCOM
LS A - LS
LS A - LS
JAG
LS A - LS
Thanks again
Last edited by King_Quake; 09-24-2008 at 02:50 PM.
Did you change the X1 in the formula to E1 or E2 (wherever you put the Countif formula)?
Also did you confirm the formula with CTRL+SHIFT+ENTER so that { } brackets appeared around the formula...? then copied down.
Hi,
Yes to the first question.
I did not see any of the squiggly brackets, I will try it again.
Thanks
Hi NVBC,
I did what you said and of course it worked perfectly. Sorry for my sloppy entries. I really appreciate the help. I wish I had the aptitude for these formulas, but I will never be a programmer.
Thanks again.
Signed up just to see the formula example... So glad I did, you life saver.
Thanks
I tried to modify this formula to work with blank cells in a row instead of a column. This is what I have so far:
=IF(COLUMNS($AV$27:AV27)>$AR$45,"",INDEX(AV27:BP27,SMALL(IF(AV27:BP27<>"",COLUMN(AV27:BP27)-COLUMN(AV27)+1),COLUMNS($AV$27:AV27))))
Where ROW AV27:BP27 is where the data exists that contain blank cells. It seemed to work with the first box, but when I dragged the formula across the row, it returned #NUM. It's probably something with the $. Whatever it is, I'm stuck...
Wohooo works!. Thank you
Dear NBVC,
can you help me in the same condition?
I have a sheet column A like this:
abc
fr
ejr
2
35
656
78
jfor
I want my column B to show the same values in same order they are in Column A without any blank cell inside. That is, my Column B should look like follows:
abc
fr
ejr
2
35
656
78
jfor
I tried day long with this solution but I do not know why it is not working! Can you help me out please? I need it very urgently!
Advance thanks.
Originally Posted by NBVC
Rishad...Welcome to the Forum.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you so much FDibbins! I am new you know. Thanks a lot. But I cannot find the link to start a new thread!
Can you help in this regard?
Rishad,
To start a new thread - read this - Once you click on www.excelforum.com which is the home page, you will see many subforums listed there. Click on the subforum relevant to your question. It will open up the page containing all the threads created in that subforum. On the top of the page, you will see a blue button called "+Post New Thread". Click on that button and proceed.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I have been searching high and low for a formula that will do just what you describe in excel mac 2008. Nothing seems to work, and I am hoping that the code discussed will finally do the job. However, I cannot find it. The post which is supposed to contain the code instead contains an invitation to log into the forum, despite my already being here... Please, I would be most grateful if you could repost the code for copying data from one column to another sans blanks!
Hi NBVC, I can't seem to copy the code from this post. I have been seeking exactly what is described here, and am praying that ive finally found it! Please could you repost it?
I will be happy to repost the code. Unfortunately, the spreadsheet it is in is at work, so I won't be able to post it until Monday. I will write back with the formula on Monday evening.
Formula to remove blank cells.xlsx
In a hurry, don't have time to explain. But this is the tracker that contains the formula you're looking for. I'll reply again tomorrow to further explain.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks