1 . Excel Advance filter using VBA?

Answer :

The Advanced Filter capabilities do not include the ability to filter subtotals. So, it is not possible to create a VBA routine to use Advance Filter. 

While you can use VBA to create a Consolidate routine, which would do as you wish, it places the result in another area of the worksheet. 

As an alternative, I suggest a Before_DoubleClick event handler that will create a filtered list of subtotaled values in columns M:N on the worksheet. 

Copy the following event handler to the clipboard: 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ 
Cancel As Boolean) 
Dim i, LastRow, LastRowM 
LastRow = Range("A" & Rows.Count).End(xlUp).Row 
For i = 2 To LastRow 
If Application.CountIf(Range("M:M"), Cells(i, "A").Value) = 0 Then 
Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Cells(i, "A").Value 
End If 
Next 
Range("M1").Value = "Bill #" 
Range("N1").Value = "Sum" 
LastRowM = Range("M" & Rows.Count).End(xlUp).Row 
For i = 2 To LastRowM 
Range("N" & i).Value = Application.SumIf(Range("A:A"), _ 
Range("M" & i).Value, Range("C:C")) 
Next 
Target.Offset(1).Select 
End Sub 

Select the worksheet containing the data to evaluate and right click the sheet tab at the bottom. 

Select 'View Code'. 

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste'). 

Close the VBE (red button - top right). 

To create the list, simply double click any cell in the worksheet. If the data changes, just double click any cell again. A new, clean, extract will be created.


Leave a Comment

Name  
  Email   
Message
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.

enter image description here

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.

enter image description here

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 :

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.