Sorting a column by using formula #3

I am trying to use sort function just to delete blank cells in between
Sort order doesn't matter actually. 

Data is coming by the use of simple cell reference of "another sheet

--
Prais
-----------------------------------------------------------------------
Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558
View this thread: http://www.excelforum.com/showthread.php?threadid=27144

0
10/22/2004 7:35:51 AM
excel 39879 articles. 2 followers. Follow

2 Replies
495 Views

Similar Articles

[PageSpeed] 39

Hi
you may use the following addin to filter out blank cells:
http://www.rondebruin.nl/easyfilter.htm

-- 
Regards
Frank Kabel
Frankfurt, Germany


Praise wrote:
> I am trying to use sort function just to delete blank cells in
> between. Sort order doesn't matter actually.
> 
> Data is coming by the use of simple cell reference of "another sheet"
0
frank.kabel (11126)
10/22/2004 7:55:40 AM
Praise Wrote:
> I am trying to use sort function just to delete blank cells in between
> Sort order doesn't matter actually. 
> 
> Data is coming by the use of simple cell reference of "another sheet"

Your sample:

Cell A1: Mr. abc
Cell A2: Mr. XYZ
Cell A3: Blank Cell
Cell A4: Blank Cell
Cell A5: Ms. Lee
Cell A6: Mr. PQR

Let the sheet named Source house the above sample.

Insert 2 new rows before the current row 1 such that the sample wil
occupy:

A3:A8

In A2 enter the label: Data.

In B1 enter: 0, which is required.

In B2 enter the label: Coding

In B3 enter & copy down:

=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")

Now switch to the destination sheet. Lets call this sheet Destination.

On Destionation, do the following...

In A1 enter:

=LOOKUP(9.99999999999999E+307,Source!B3:B8)

In A2 enter the label New Data.

In A3 enter & copy down as far as needed:

=IF(ROW()-ROW(A$3)+1<=$A$1,INDEX(Source!$A$3:$A$8,MATCH(ROW()-ROW(A$3)+1,Source!$B$3:$B$8)),""

--
Aladin Akyure
-----------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=416
View this thread: http://www.excelforum.com/showthread.php?threadid=27144

0
10/23/2004 10:09:44 AM
Reply:

Similar Artilces:

Doubt with 3 condition
Can anyone help in solving the following doubt? I have to 3 parameters Condition 1, condition 2 and condition 3 If Condition 1 or Condition 3 has value 1 then my result should show as 10. How can I do this using if condition. I thought it was working? saraskandan@gmail.com wrote: > > Can anyone help in solving the following doubt? > > I have to 3 parameters > > Condition 1, condition 2 and condition 3 > > If Condition 1 or Condition 3 has value 1 then my result should show as > 10. How can I do this using if condition. -- Dave Peterson Hi Sara Try =IF(...

Registration/Activations Process for CRM 3.0
Hello, Is there a publicly available guide which explains what the full CRM 3.0 registration/activation process is? If not, would someone be able to quickly highlight the steps involved please? Regards, Tony ------=_NextPart_0001_8B1F221F Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Tony, There is not a guide but I can briefly outline the processes and you can please let me know if you have any specific questions. Licensing: - During the CRM Server installation you will be prompted to enter you CRM 3.0 PID keys. For most people that will be located on an orange st...

PDF file #3
I have Publisher 2002 and would like to insert a .pdf file into a document I am working on. How do / Can I do this? Many thanks, Ger ...

End Of Month Formula #2
Good evening all. I am a little stumped here, cannot seem to resolve this simple requirement. In cell A1 I have the value from 1 to 12 (representing the month). (This is manually entered.) Now, in cell B1, I would like to enter a formula, so that the last date of the month in A1 is shown, eg A B 1 5 30/05/03 .... or A B 1 12 31/12/03 ... etc As usual any assistance would be grayefully received. Cheers, Mathew --- Outgoing mail is certified Virus Free. Checked by AVG anti-vi...

protecting a range of raws and columns with formula in Excel sheet
I need help in protecting a range of raws and columns with formula in Excel sheet. An Excell Workbook contains 14 sheets (12 for each month, one for yearly total and one for description) More than 2000 employees use it. So I would appreciate if you could help me to do it using Macro or similar quick method. You want to protect the same ranges on each of the 14 worksheets? Sub ProtectAllSheets() Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count With Sheets(N) .Cells.Locked = False .Range("A1:B14").Locked = True 'adju...

formula required for excel 2002
hi all, require some assistance for formating a cell group ie F9:F28 that will count a symbol i.e.( a tick or cross) instead of a numeral and total it at the bottom.at F29. thanks in advance jml Are you use a special font (like Marlett) and a letter (a) for the tick? If so, just use COUNTIF =COUNTIF(F9:F28,"a") -- HTH Bob Phillips "jmlsav2" <jmlsav2@discussions.microsoft.com> wrote in message news:95E648E6-0549-4CDB-A5C3-E9841514C198@microsoft.com... > hi all, > require some assistance for formating a cell group ie F9:F28 that > will count a symb...

Printing a blank cell if sumproduct formula not true
If I have a formula such as =sumproduct((A1:A5=B2)* (C1:C5=D2))how can I get the cell that the formula is in to print blank if the formula is not true? Thanks for any help. > ...if the formula is not true? I might be wrong, but it looks to me that you are doing a "Count" of how many matches there are in Parallel. In Excel, True * True = 1. Would it be correct to say if the "Count" is zero, then hide the results? Perhaps a custom format that hides zero values. Say "General;General;", or something similar. The last ";" will hide zero values. --...

Using multiple filters in 1 Worksheet
Hi all, Anyone have any idea how I can use more than 1 custom auto-filter on different rows in the same workbook? It appears as though I can only use 1 at a time, when I filter out rows with one set of cutom filter rules, I can't go to another row and apply different (non- conflicting) custom filter rules on top of the newly filtered workbook. Does that make sense? Can anyone help? Thank - Shaun scratch this request - I figured it out. >-----Original Message----- >Hi all, >Anyone have any idea how I can use more than 1 custom >auto-filter on different rows in the s...

Import Error CRM 3.0 German
I am running German version of CRM 3.0 and for testing purposes we're trying to import just 6 adresses from an Excel sheet. But every import fails with the message: "Daten in der Zeile stimmen nicht mit dem Importtyp überein" "0x8004032d" We already reformatted the Excel sheet, we exported the stuff into: - txt, tabulator-splitted - csv, splitcharacter splitted, everythings fails, we are nor able to import those adresses into register "company" (Firma) Someone got an idea why? Thanks in advance for any hint, greets Tom hi Tom, check your datatype, is...

arrange columns through sheet and multi sheets
good morning A B C D Equipment serial type model mudump 123 triplex 1600hp In another sheet A B C D Equipment serial model type mudump 123 1600hp 1600hp i need to collect "type" in column C without copy and paste because the data is too wide more than those items In Sheet2 row 1 place the necessary headers (which are similar to Sheet1 headers) and ColA with equipment name try the ...

Using Macro to post totals
I have a very simple sheet that I use a barcode scanner to input the barcodes of coupons. I use a vlookup to find the value of the coupon. I have a total of the coupons in one cell. I want to write a macro that will copy the total of the batch, paste it in a column, then delete the data entry column. I did that with no problem. The problem is, how do I get batch number 2 to past the value of the total in the row under the value of batch number 1? I am currently overwriting the same cell. I would like to get a grand total of all batches too. I have read about looping in VBS, but don't fully...

[CRM 3.0] Installation error [DBNETLIB] ConnectionOpen (PreLoginHandShake()).
Hello, I'm trying to install Microsoft CRM 3.0 Small Business Edition on a Small Business Server 2003. SQL server is installed. Or during the installation process I have this error : " [DBNETLIB] ConnectionOpen (PreLoginHandShake()). General network error. Check your network documentation " Do you know what the problem is ? Thank you for advance. Can that come owing to the fact that the SP4 is not or badly installed? "Davyd" <davyddavid@XXXyahoo.fr> a �crit dans le message de news: %23TFyG7gDHHA.1748@TK2MSFTNGP02.phx.gbl... > Hello, > > I'm...

From single cell variables to a single column serie
I'm after to form a "single column serie" using manually enetered (or automatically changed) values in a "single cell". Lets say that the value in A1 is changed daily and each value is written to the next row, so I have A2:A32 serie for values from A1 in December trial variable (serie progression) (date) (values from A1) 1 23 23 23 23 23 2 24 24 24 24 3 32 32 32...

grand total column B from every worksheet in workbook
I want to have a function in a cell that totals column B from every worksheet in my workbook even w/o knowing how many worksheets there are or what the name of any worksheet is. Even better, because I would like to have a "summary" worksheet, the function should skip column B in its own work sheet -- so, the function I'd like might total every number in a column called "Qty" for any worksheet in the workbook that has a column named "Qty". I have an Excel doc that holds all of my sales data. The data is imported as a text file from a webstore db into a wor...

sort??
Hello, Everybody. I have serial numbers such a H5805 H5805Q H5806 H5806Q H5807 H5807Q H5808 H5808Q I want excel to sort them just like how I listed it above. but excel keeps pushing all the serial numbers with the Q to the bottom. Is there a way for excel to sort and omit the prefix Q? Please let me know. Kitty -- Kitty ------------------------------------------------------------------------ Kitty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14898 View this thread: http://www.excelforum.com/showthread.php?threadid=265300 Hi! ?? My Excel2003 sorts the...

Sort (MNY2002)
For some strange reason my MNY program not sorting correctly. In view and sort I have it checked to sort by date, increasing. The first access of MNY on a day it appears as sort, day, decreasing, yet increasing is checked why?. Thanks for any help. ...

How to use the same drop down fields
How do I use the same drop down fields in multiple forms without recreating the values with each form? On Sun, 15 Nov 2009 08:51:01 -0800, Petra van Vuurem <Petra van Vuurem@discussions.microsoft.com> wrote: >How do I use the same drop down fields in multiple forms without recreating >the values with each form? The only way to do that is to prepare a macro that populates the list. Here's some sample code (see http://www.gmayor.com/installing_macro.htm if needed): Sub PopulateCityDropdown() Dim FieldName As String Dim WasProtected As Boolean ...

Menu Bar #3
Hi is it possible to customise the menubar and get rid of all the ones thats already there ??? Many Thanks Benn -- cassy01 ------------------------------------------------------------------------ cassy01's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=780 View this thread: http://www.excelforum.com/showthread.php?threadid=483238 what i mean is customise the menubars only on that workbook, when the book is closed it goes back to normal. thanks -- cassy01 ------------------------------------------------------------------------ cassy01's Profile: http:...

clustered stack column charts
I am trying to create a chart with three stacks in two columns. I tried using the method described by Bernard Liengmen's but it doesn't allow me to add a secondary axis for the last (sixth) data series. I don't get it??? ...

Printing with more than one column per page
I'm using the term, "column" incorrectly here. I want to print a list of records, but I want to have 2 or three "columns" of records per page. Word can do this using the "columns" control, but I can't figure out how to get Excel to do this, or how to get Word to import an Excel file such that I can do this. The item is an electronic price list, and it's going to be over 100 pages if each line is one record, and it's mostly blank space. Thanks for any suggestions. I'm using Excel and Word '97. Regards, Robin Robin, My Excel add-in &quo...

How to automate Outlook using MFC
Team: I have found the link http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3BQ220600 , and says that I need to identify my version of outlook, and link to its resoective file, for example : msoutl.olb But I do not I understand How I chose the directive #include "msoutl85.h",or where is this directive from?, or what is the relation between the directive and the version of outlook file *.olb? what is the intermediate step between both? Thanks in advanced, German Medina +519-662-8500 Thanks in advanced, The header files are generated when you use the #import d...

Outlook Toolbar #3
Since upgrading to Windows XP I seem to have lost a bar for my attachments. The bar was similar to the Subject bar and previously all my attached files would sit in there. Now the files are in the main body of the message. Has anyone any ideas on how to get this bar back for my attachments? I am using Office 2000, Outlook SP3. Check Tools | Options | Mail Format. Switch to HTML or Plain-Text format. -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "Danny" <...

It is not a formula!
Can anyone tell me how I can display the value: =[long value] in Excel? If I format the cell as text, I can put in "=1+1" and it displays as such just fine. However, if I want to put more than 1023 characters after the initial "=", I get a "Formula too long" error. I am not specifying a formula. I just want to display a lot of text that happens to start with an "=". Is the only way around this to add a space before the equals sign? Tell me there's a better way! -- Steven Wazowski -------------------------------------------------------------...

Using Outlook with Multiple Users in XP Pro
Is there a way to get outlook to allow all users to all other users email without logging in to each users in XP Pro. Given that everyone uses the same email address??? Jim, is this in a domain environment? Are you using Exchange? Thanks! "Jim" <anonymous@discussions.microsoft.com> wrote in message news:e6a201c43c88$6a300fd0$a301280a@phx.gbl... > Is there a way to get outlook to allow all users to all > other users email without logging in to each users in XP > Pro. > > Given that everyone uses the same email address??? > > if you are using ...

using Interoperation to call Managed code from COM
Hi! This question is just for understanding. I have found this example in Code project. I have a COM component and a .NET class library. The COM will call the .NET type library that is sitting in between the COM and the .NET library and the type library will call the .NET library. This type library is sort of encapulating the .NET library The .NET library has as the extension as all know the famous DLL and the type library has extension TLB. I have noticed that the .NET library must be registered in the registry. But what about the type library that is sort of a COM thingy does t...