Hi I was wondering if you could help me with something, say that you have three colums, first column contains ten transactions (1,2,3,4,5,6,7,8,9,10) which the user will input, column 2 and 3 will separate the transactions in to two Column 2 (1,2,_,4,5,_,_,8,9,10) and Column 3 (_,_,3_,_,6,7,_,_,_). Now on another column I want to get the transactions from column 2 and display them without spaces using a formula/function (i.e. New Column (1,2,4,5,8,9,10)). How do I do this?
Hi Kim_mandy,
Based on the information provided here: http://www.cpearson.com/excel/noblanks.htm
You could use something like:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
Where "BlanksRange" would be, in your example, B1:B10 ("column 2") and "NoBlanksRange" would be D1:D10 (a new column). So you could, for instance, in D1, put the array formula:
=IF(ROW()-ROW($D$1:$D$10)+1>ROWS($B$1:$B$10)-COUNTBLANK($B$1:$B$10),"",INDIRECT(ADDRESS(SMALL((IF($B$1:$B$10<>"",ROW($B$1:$B$10),ROW()+ROWS($B$1:$B$10))),ROW()-ROW($D$1:$D$10)+1),COLUMN($B$1:$B$10),4)))
Note: After typing this, and any, array formula you must press CTRL+SHIFT+ENTER. Just pressing ENTER will not give you the results you need. After doing this for D1, fill the formula down to D10.
Once you've done that, you can also create a list with no spaces for column 3 data by replacing the $B$1:$B$10 references to $C$1:$C$10 and the $D$1:$D$10 to $E$1:$E$10. Again, remember to press CTRL+SHIFT+ENTER after typing or editing these array formulas. Fill down to E10.
That should do it!
Hi thanks for the quick reply, I'm still trying to process the entire formula in my mind ... I tried to use it in an excel sheet though and what happened was that on the first row it worked but for the next few rows it showed #NUM, by any chance, if it's not too much of a bother, what was supposed to happen?
You're getting the #NUM! error because you didn't press CTRL+SHIFT+ENTER after typing the formula as I said in my post above.
Copy my formula from above, then switch to Excel. Double-click cell D1 to go into cell edit mode then press CTRL+V to paste the formula. Once the formula is in the cell, hold down CTRL and SHIFT and then press ENTER. After hitting Enter you can let go of all three buttons.
D1 should now say "1". Fill that down to D10 and you should get the correct results.
I just realized that I never got to thank you! Thank you I think this helped me
You're very welcome! Almost a three-year anniversary of sorts.
Glad you're still around, and hopefully you've been using the forum since then to help you out.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks