Select cell B2 and click &quot;Freeze Panes&quot; this will freeze Row 1 and Column A.

For future reference, selecting Freeze Panes in Excel will freeze the rows above your selected cell and the columns to the left of your selected cell. For example, to freeze rows 1 and 2 and column A, you could select cell B3 and click Freeze Panes. You could also freeze columns A and B and row 1, by selecting cell C2 and clicking &quot;Freeze Panes&quot;.

Visual Aid on Freeze Panes in Excel 2010 - 
http://www.dummies.com/how-to/content/how-to-freeze-panes-in-an-excel-2010-worksheet.html

Microsoft Reference Guide (More Complicated, but resourceful none the less) - http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP010342542.aspx

I have a two tables

    Student
    --------
    Id	Name
    1	John	
    2	David
    3	Will

    Grade
    ---------
    Student_id	Mark
    1			A
    2			B
    2			B+
    3			C
    3			A


Is it possible to make native Postgresql SELECT to get results like below:

    Name	Array of marks
    -----------------------
    &#39;John&#39;,     {&#39;A&#39;}
    &#39;David&#39;,    {&#39;B&#39;,&#39;B+&#39;}
    &#39;Will&#39;,     {&#39;C&#39;,&#39;A&#39;}

But **not** like below

    Name	Mark
    ----------------
    &#39;John&#39;,     &#39;A&#39;
    &#39;David&#39;,    &#39;B&#39;
    &#39;David&#39;,    &#39;B+&#39;
    &#39;Will&#39;,     &#39;C&#39;
    &#39;Will&#39;,     &#39;A&#39;



Postgresql aggregate array

It seems like it&#39;s taken for granted that you must not use Webrick as production server, but I can&#39;t really find anywhere mentioning why. The consensus seems to be:
&quot;Webrick is ok for development, but Thin or Unicorn is the choice for production, period.&quot;

I did look up Thin server&#39;s homepage and it talks about requests/second but I don&#39;t really understand the graph since there&#39;s no annotation.

Can anyone let me know why I should use Thin or Unicorn compared to Webrick? Also is there any benefit to using Webrick for development? I&#39;ve been using Webrick since it comes with rails, and I think there should be a reason why it&#39;s default.

I&#39;m using Heroku by the way.

Webrick as production server vs. Thin or Unicorn?

I want to &quot;freeze&quot; Row 1 and Column A simultaneously in Excel 2010. 

Is this possible?

Freezing Row 1 and Column A at the same time

<p>I want to "freeze" Row 1 and Column A simultaneously in Excel 2010.</p>
<p>Is this possible?</p>


When I want to find the last used cell value, I use: 

    Dim LastRow As Long
    
    LastRow = Range(&quot;E4:E48&quot;).End(xlDown).Row
    
    Debug.Print LastRow

I&#39;m getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. 
What&#39;s the reason behind this?

Error in finding last used cell in Excel with VBA

I&#39;m writing an application which works with excel files.
I need a feature to delete a sheet.
I have to use an assembly Microsoft.Office.Interop.Excel.dll.

It&#39;s running fine on developer machine but when I try to deploy it on server I&#39;m getting an error:

&gt; Could not load file or assembly &#39;office, Version=14.0.0.0,
&gt; Culture=neutral, PublicKeyToken=71e9bce111e9429c&#39; or one of its
&gt; dependencies

I understand that problem occurs when MS Office is not installed on a machine.
Customer don&#39;t want to install and buy MS Office on a server not at any price.

I install &quot;Redistributable Primary Interop Assemblies&quot; on developer machine as advised here: [http://forums.asp.net/t/1530230.aspx/1][1]
and compile my project again.


Code sample:

	public bool DeleteSheet(string tableName)
	{
		Excel.Application app = null;
		Excel.Workbooks wbks = null;
		Excel._Workbook _wbk = null;
		Excel.Sheets shs = null;

		bool found = false;

		try
		{
			app = new Excel.Application();
			app.Visible = false;
			app.DisplayAlerts = false;
			app.AlertBeforeOverwriting = false;

			wbks = app.Workbooks;
			_wbk = wbks.Add(xlsfile);
			shs = _wbk.Sheets;
			int nSheets = shs.Count;

			for (int i = 1; i &lt;= nSheets; i++)
			{
				Excel._Worksheet _iSheet = (Excel._Worksheet)shs.get_Item(i);
				if (_iSheet.Name == tableName)
				{
					_iSheet.Delete();
					found = true;

					Marshal.ReleaseComObject(_iSheet);
					break;
				}
				Marshal.ReleaseComObject(_iSheet);
			}

			if (!found)
				throw new Exception(string.Format(&quot;Table \&quot;{0}\&quot; was&#39;t found&quot;, tableName));

			_wbk.SaveAs(connect, _wbk.FileFormat, Missing.Value, Missing.Value, Missing.Value,
			Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
			Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
		}
		finally
		{
			_wbk.Close(null, null, null);
			wbks.Close();
			app.Quit();

			Marshal.ReleaseComObject(shs);
			Marshal.ReleaseComObject(_wbk);
			Marshal.ReleaseComObject(wbks);
			Marshal.ReleaseComObject(app);
		}
		return true;
	}

An exception
&gt; Retrieving the COM class factory for component with CLSID
&gt; {00024500-0000-0000-C000-000000000046} failed due to the following
&gt; error: 80040154 Class not registered (Exception from HRESULT:
&gt; 0x80040154 (REGDB_E_CLASSNOTREG)).

occurs on the line 

    app = new Excel.Application();


Can anyone advise on how to get this feature working successfully?

  [1]: http://forums.asp.net/t/1530230.aspx/1


How to use Microsoft.Office.Interop.Excel on a machine without installed MS Office?

How can I randomize lots of rows in Excel?  

    For example I have an excel sheet with data in 3 rows. 
    1 A dataA
    2 B dataB
    3 C dataC
    
    I want to randomize the row order. For example
    2 B dataB
    1 A dataA
    3 C dataC

I could make a new column and fill it with random numbers using =RAND() and sort based on that column. 

But is this the best way to do it? The RAND equation will provide up to [a million random numbers][1] and I have a quarter of a million rows so it seems like it would work.  

Thanks 

I searched for a bit and while [this answer about randomizing columns][2] is close it seems like way overkill. 


  [1]: http://support.microsoft.com/kb/86523
  [2]: https://stackoverflow.com/questions/10651553/randomize-columns

How to randomize Excel rows

I&#39;m trying to parse a text document using VBA and return the path given in the text file.  For example, the text file would look like: 

    *Blah blah instructions
    *Blah blah instructions on line 2
    G:\\Folder\...\data.xls
    D:\\AnotherFolder\...\moredata.xls

I want the VBA to load 1 line at a time, and if it starts with a `*` then move to the next line (similar to that line being commented). For the lines with a file path, I want to write that path to cell, say `A2` for the first path, `B2` for the next, etc. 

The main things I was hoping to have answered were:  
1. What is the best/simple way to read through a text file using VBA?  
2. How can I do that line by line?

Read/Parse text file line by line in VBA

    Sub test()
    
    thesentence = InputBox(&quot;Type the filename with full extension&quot;, &quot;Raw Data File&quot;)
    
    Range(&quot;A1&quot;).Value = thesentence
    
    If Dir(&quot;thesentence&quot;) &lt;&gt; &quot;&quot; Then
        MsgBox &quot;File exists.&quot;
    Else
        MsgBox &quot;File doesn&#39;t exist.&quot;
    End If
    
    End Sub


In this when i pickup the text value from the input box, it doesn&#39;t work. If however, if remove ``&quot;the sentence&quot;`` from If ``Dir()`` and replace it with an actual name in the code, it works. Can somebody help?

Check if the file exists using VBA

I have a list of dates, each date in it can occur more than once. I want to count the number of times each date occurs (histogram) and display it in a graph (with the Y axis being the number of times the date occurs and the X axis being the date itself)?

Sample list:

    19/05/2012
    19/05/2012
    19/05/2012
    17/05/2012
    17/05/2012
    16/05/2012
    16/05/2012
    16/05/2012
    16/05/2012
    15/05/2012
    15/05/2012
    15/05/2012
    15/05/2012
    12/05/2012
    12/05/2012
    12/05/2012
    7/05/2012

I clicked every menu option over the past few years and never did I find anything specific to this. Would this be a case for using PivotTables somehow?

Count number of times a date occurs and make a graph out of it

I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows:

    Column A   Column B  
    a          a
    a          b
    b          c
    c
    c

This Column B will actually need to appear on a different sheet, within the same workbook, so I assume it will need to work with the `sheet2!A1` style format.

I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A. 

Ignore Duplicates and Create New List of Unique Values in Excel

In this section of code, Excel ALWAYS prompts: &quot;File already exists, do you want to overwrite?&quot;

    Application.DisplayAlerts = False
    Set xls = CreateObject(&quot;Excel.Application&quot;)
    Set wb = xls.Workbooks.Add
    fullFilePath = importFolderPath &amp; &quot;\&quot; &amp; &quot;A.xlsx&quot;
    
    wb.SaveAs fullFilePath, AccessMode:=xlExclusive, ConflictResolution:=True   
    
    wb.Close(True)

Why does `db.SaveAs` always prompt me to overwrite existing file if I have `DisplayAlerts = False`?

How to use workbook.saveas with automatic Overwrite

Using Microsoft Excel 2010, I noticed two kind of controls that can be inserted into a document: *Form Controls* and *ActiveX Controls*. 

![enter image description here][1]

What is the difference between them?


  [1]: http://i.stack.imgur.com/02BSl.png

What is the difference between &quot;Form Controls&quot; and &quot;ActiveX Control&quot; in Excel 2010?

I would like to write an IF statement, where the cell is left blank if the condition is FALSE.
Note that, if the following formula is entered in C1 (**for which the condition is false**) for example:

     =IF(A1=1,B1,&quot;&quot;)

and if C1 is tested for being blank or not using `=ISBLANK(C1)`, this would return `FALSE`, even if C1 seems to be blank. This means that the `=IF(A1=1,B1,&quot;&quot;)` formula does not technically leave the cells blank if the condition is not met.

Any thoughts as to a way of achieving that? Thanks,

Content Type	Original Author	Original Content on Stackoverflow
Question	Matt Ridge	View Question on Stackoverflow
Solution 1 - Excel	Kyle	View Answer on Stackoverflow

Freezing Row 1 and Column A at the same time

Excel Problem Overview

Excel Solutions

Solution 1 - Excel

Webrick as production server vs. Thin or Unicorn?

Postgresql aggregate array

Attributions