Advanced Excel

1 . Advance in excel?? How to classify the data in different table automatically?

Answer : there might be an easier way, but try this with your sample

- your sample is in cells A1 to A6

- in B1 copy and paste this

=RIGHT(A1,3)

- then copy and paste the formula down the B column

- the highlightt all of it and sort by column B

- that organizes everything for you

1 . !!!MS Excel formula help Required !!!!?

Answer : Your error isn't because there are nothing in those cells.

It is because there are a non-number in those cells.

(Like a space.)

Either, select those cells and press delete, or (if those cells are a result of a formula), you probably have " " instead of "" in the formula in those cells.

Simply modify it.

Or, easier:

Change your formula in P12 to

=IF(AND(C12="", H12="",M12=""), "", SUM(C12, H12, M12))

2 . Excel Functions and formulas used in Banking Sector While making the MIS.?

Answer : If you go into MS-Excel, and select any empty cell, then use the "fx" button just to the left of the Formula Bar, then you will see all of the 330+ Functions available. This is known as the Insert Function button, and it can also be done through the menu option.

Within the dialog window that appears, you can choose different functions based upon Category. There is a drop-down box for selecting categories, such as Financial, Statistical, Math & Trig, etc...

You should learn how these functions are used if you are not already familiar with them

3 . Which country has a more advanced type of farming? India or Argentina?

Answer : I would say India, because we have all sorts of farming technology, right from Barbarous methods to most advanced precision farming. We have achieved highest productivity by some farmers in bihar on rice ie 21 MT/ha which is higher than all countries including china and is a Guinness book record achievement. We are first in Mango production, second in Vegetables and Banana and has highest grape productivity. Our country is too big like china that others cannot win in agriculture. But there are countries like argentina, denmark, Thailand etc which excels in some specific cases. We have everything while others have only a few things to excel.

4 . How to make excel supporting semicolon CSV ?

Answer : on Easiest way is to Save that file as CSV

After That open that file in Notepad (or Wordpad/Word)

replace all Commas with Semicolons

Press Ctrl + H

in find box type ,

and in replace with box type ;

and press Replace All

now save the file

you are done.

(*.*)a

5 . Need a Macro in EXCEL?

Answer : At the bottom of your macro you have this code...

' Copy and paste each area

For i = 1 To NumAreas

RowOffset = SelAreas(i).Row - TopRow

ColOffset = SelAreas(i).Column - LeftCol

SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset)

Next i

Replace the above code with the following...

'Prompt: Paste only values?

Dim ValuesOnly As Integer

ValuesOnly = MsgBox("Paste only the values?" & _

vbCr & vbCr & "Yes = Paste values" & vbCr & _

"No = Paste all", vbYesNo + vbQuestion, _

"Paste all or values only")

' Copy and paste each area

Application.ScreenUpdating = False

For i = 1 To NumAreas

RowOffset = SelAreas(i).Row - TopRow

ColOffset = SelAreas(i).Column - LeftCol

If ValuesOnly = vbNo Then

SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset)

Else

SelAreas(i).Copy

PasteRange.Offset(RowOffset, ColOffset).PasteSpecial xlPasteValues

Application.CutCopyMode = False 'clear Clipboard

End If

Next i

Application.ScreenUpdating = True

6 . Worksheet_Change to hide rows in Excel?

Answer : The `Rows("5:5")`

would be better as `Rows(5)`

. The method you used would be better as `Range("5:5")`

.

```
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Rows(5).EntireRow.Hidden = (UCase(Target.Value) = "ODD")
End If
End Sub
```

Since comparing B2 to *ODD* already produces a *True* or *False*, you can dispense with the `If/Else/End If`

. Text comparisons in VBA are usually case sensitive, hence the need for `UCase`

to force case insensitivity.

7 . Error Importe Excel With OpenRowSet?

Answer : 0down voteaccepted

The `SELECT`

statement inside your connection string is not needed, you can remove it:

```
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=C:\Hola.xlsx', [Ripley$]')
```

You may also not need the `Xml`

portion (but I'm not sure if that would cause an error):

```
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=C:\Hola.xlsx', [Ripley$]')
```

8 . Excel imports numbers with negative exponent incorrectly?

Answer : I assume your Windows Regional settings have the dot as the thousands separator and the comma as the decimal separator. In Excel, on the Advanced tab of the text import wizard, you need to change both of these -- not just the decimal separator.

Once you do that, the data should import properly. But, at least in my version, the cell is formatted with just two decimal places. However, the formula bar does show the correct value.

You can easily add decimal places to the formatted number.

An alternative would be to change your Windows Regional Settings with regard to the decimal and thousands separator.

9 . Python: Import graphic into to Excel and export whole sheet as EPS?

Answer : I'll tackle your requirements one at a time:

**Import an external graphic file in EPS format**

xlwt will allow you to import images into your spreadsheet, but only in BMP format. First you will have to rasterise your EPS file into BMP format, then do:

```
from xlwt import *
w = Workbook()
ws = w.add_sheet('Image')
ws.insert_bitmap('python.bmp', 2, 2)
w.save('image.xls')
```

[taken from this example in the source]

**Export a whole spreadsheet as EPS**

What you are describing requires Excel itself (or OpenOffice / equivalent) - as it is a rendering operation. The python-excel library you are using is for manipulating the Excel file format, not for rendering it.

10 . Trim down an excel formula?

Answer : As you always include the cell `C11`

inside an `INDIRECT`

please consider the following formula that places the `INDIRECT`

outside while the conditionals just return the range addresses as strings.

```
=INDIRECT("'"&C11&"'"&IF(AND(H11="Embryo",G11<>"F2"),"!$g$24:$g$27",IF(AND(H11="Seed",G11<>"F2"),"!$h$24:$h$27",IF(AND(H11="Seed",G11="F2"),"!$g$117:$g$120",IF(AND(H11="Embryo",G11="F2"),"!$h$117:$h$120",E2)))))
```

Please note that this logic will break if none of the conditions are met.

11 . SSIS Read date field from excel as string?

Answer : I don't have much experience with SSIS, just occasionally importing into SQL Server from Excel. But when I read Excel files using .NET, I make a connection something like this.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

The important part above when it comes to reading Excel dates as strings is the "IMEX=1" part. This keeps Excel from attempting to determine the data type.

I don't know if that's something you can apply with SSIS.

12 . Need a formula or solution in EXCEL?

Answer : theres no formula that can tell the difference between a formula and a harcoded cell.

so i recorded a quick macro for column B

Sub formulaB()

'

' formulaB Macro

'

'

Range("B1:B1000").Select

Selection.SpecialCells( xlCellTypeFormulas, 23).Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End Sub

13 . Excel 2013 Calculation Question?

Answer : The cells in your question: A, B, C are cells A1, A2, A3 respectively:

Use a comparison to check if C is "x" which will give a true/false result (1/0 in a math formula), so your formula for cell A (A1) could be:

= (A3 = "x") * 2 * A2

14 . Populate “List Box” with Advanced Filter (Unique Values Only)

Ask?

Answer : Ask?

Here's one way:

```
Private Sub UserForm_Initialize()
Dim arrUnqItems As Variant
With Sheets("HiddenDataList")
.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).AdvancedFilter xlFilterCopy, , .Cells(1, .Columns.Count), True
arrUnqItems = Application.Transpose(.Range(.Cells(2, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp)).Value)
.Columns(.Columns.Count).Clear
End With
Me.ListBox1.Clear
Me.ListBox1.List = arrUnqItems
Erase arrUnqItems
End Sub
```

15 . Can I define value for alphabet in MS Excel?

Answer : There would be no need to define a value. Say you have something like this:

______/ 1/1/11 / 1/2/12 / 1/3/12 /____

Matt / P / A / E /

Mark / A / P / P /

Luke / E / P / E /

You can use a formula called CountIF. It will count the number of cells in a given condition. For instance if I wanted to total the attendance into a number where P is worth 1, A is worth 0, and E is worth 2, I would set it up as:

=SUM(COUNTIF(B2:D2, "=P")*1, COUNTIF(B2:D2, "=A")*0, COUNTIF(B2:D2, "=E")*2)

Or since A is 0, no need to add it:

=SUM(COUNTIF(B2:D2, "=P")*1, COUNTIF(B2:D2, "=E")*2)

This would result in the total column with this:

______/ 1/1/11 / 1/2/12 / 1/3/12 / Total

Matt / P / A / E / 3

Mark / A / P / P / 2

Luke / E / P / E / 5

You will have to change the letters in the formula to match what you are doing. Hope it isn't to difficult.