Posts by mohammedismail

Hi Guys,

Please could you help me out with a formula which will help me identify how many Rows belong to Top 10 % Group Or top 20% Group. For Example in the below case there are 20 rows and Top 10% are the first 2 rows.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 402"]Row Labels[/TD]
[TD="width: 92"]Amount[/TD]

ABC

63,855,641.60

DEF

36,100,068.26

SGH

23,324,389.74

ABC

20,534,610.54

DEF

14,017,828.67

SGH

11,669,652.96

ABC

11,452,970.69

DEF

10,868,123.63

SGH

10,072,343.93

ABC

9,995,804.15

DEF

9,812,192.01

SGH

9,680,726.24

ABC

9,632,613.12

DEF

8,159,122.82

SGH

6,429,932.46

ABC

6,065,187.29

DEF

6,043,460.64

SGH

5,887,114.29

ABC

5,523,253.55

DEF

5,231,107.87

[/TABLE]

Hey Guys

Can you please help me count unique values by Category. But the catch is if the product is already there in Batch1 it should not be counted in the Batch2. It should count only the unique values in Batch 2 which are not part of Batch 1 and 3. Same with Batch3.. the products must not repeat in Batch 1 or 2.

Please find the sample sheet attached and also the desired output which I got using the pivot table.

Re: Creating flexible tables with update options

Not sure if I have understood it right..You mean to say that you will enter Value is FW1 column and rest of the Columns should populate automatically(Like Value1, Value2..)?

Re: problem with AVERAGE function

Try this formula, however it also takes the Decimals into consideration!

[BFN]=AVERAGE(IF(A$1:A$37="","",VALUE(IFERROR(LEFT(A$1:A$37,FIND("#",A$1:A$37,1)-1),""))))[/BFN]

It's an array formula so CTRL+SHIFT+ENTER

Re: Macro to open file search window, open file, then run code

Try this !

Sub GetFile()
Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath
End Sub

Re: Macro that counts all characters in Worksheet

Glad to have helped you! Sometimes googling helps

Re: Macro that counts all characters in Worksheet

Check out this, may be it gives more information than you need !

Sub CountCharacters()    
    Dim wks As Worksheet
    Dim rng As Range
    Dim rCell As Range
    Dim shp As Shape




    Dim bPossibleError As Boolean
    Dim bSkipMe As Boolean




    Dim lTotal As Long
    Dim lTotal2 As Long
    Dim lConstants As Long
    Dim lFormulas As Long
    Dim lFormulaValues As Long
    Dim lTxtBox As Long
    Dim sMsg As String




    On Error GoTo ErrHandler
    Application.ScreenUpdating = False




    lTotal = 0
    lTotal2 = 0
    lConstants = 0
    lFormulas = 0
    lFormulaValues = 0
    lTxtBox = 0
    bPossibleError = False
    bSkipMe = False
    sMsg = ""




    For Each wks In ActiveWorkbook.Worksheets
        ' Count characters in text boxes
        For Each shp In wks.Shapes
            If TypeName(shp) <> "GroupObject" Then
                lTxtBox = lTxtBox + shp.TextFrame.Characters.Count
            End If
        Next shp




        ' Count characters in cells containing constants
        bPossibleError = True
        Set rng = wks.UsedRange.SpecialCells(xlCellTypeConstants)
        If bSkipMe Then
            bSkipMe = False
        Else
            For Each rCell In rng
                lConstants = lConstants + Len(rCell.Value)
            Next rCell
        End If




        ' Count characters in cells containing formulas
        bPossibleError = True
        Set rng = wks.UsedRange.SpecialCells(xlCellTypeFormulas)
        If bSkipMe Then
            bSkipMe = False
        Else
            For Each rCell In rng
                lFormulaValues = lFormulaValues + Len(rCell.Value)
                lFormulas = lFormulas + Len(rCell.Formula)
            Next rCell
        End If
    Next wks




    sMsg = Format(lTxtBox, "#,##0") & _
      " Characters in text boxes" & vbCrLf
    sMsg = sMsg & Format(lConstants, "#,##0") & _
      " Characters in constants" & vbCrLf & vbCrLf




    lTotal = lTxtBox + lConstants




    sMsg = sMsg & Format(lTotal, "#,##0") & _
      " Total characters (as constants)" & vbCrLf & vbCrLf




    sMsg = sMsg & Format(lFormulaValues, "#,##0") & _
      " Characters in formulas (as values)" & vbCrLf
    sMsg = sMsg & Format(lFormulas, "#,##0") & _
      " Characters in formulas (as formulas)" & vbCrLf & vbCrLf




    lTotal2 = lTotal + lFormulas
    lTotal = lTotal + lFormulaValues




    sMsg = sMsg & Format(lTotal, "#,##0") & _
      " Total characters (with formulas as values)" & vbCrLf
    sMsg = sMsg & Format(lTotal2, "#,##0") & _
      " Total characters (with formulas as formulas)"




    MsgBox Prompt:=sMsg, Title:="Character count"




ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub




ErrHandler:
    If bPossibleError And Err.Number = 1004 Then
        bPossibleError = False
        bSkipMe = True
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
        Resume ExitHandler
    End If
End Sub

Source

Re: copying coloured cells

And We have no knowledge of how your workbook looks like !

Please attach a sample workbook!

Re: Depreciation

Sorry. I dont think I know how to do this. However I shall try to do this.

May be someone else can help you!

Re: Getting data with web query and rearrange the data

Do you Need VBA or Formula will also help ?

Re: Depreciation

Please see the attached sheet. I have changed the Formula. the WDV is in Minus!

[ATTACH=CONFIG]68602[/ATTACH]

Re: Getting data with web query and rearrange the data

From what I understand you need vba to extract just the numbers between the Chinese Characters ?

From [TABLE="width: 122"]

[TD="class: xl64, width: 122"]成交量:45953手[/TD]

[/TABLE]
to 45953 ?

Re: Depreciation

Here you go !

[BFN]=SUMPRODUCT($D$9:$D$16*$E$3/365*$C$9:$C$16)[/BFN]

The Formula in your sheet need to have an additional bracket, currently it is incorrect!

Your formula needs to be

[BFN]=(($E$3*D9)/365)*C9[/BFN]

Re: Sort Multiple Sheets with Macro Loop

Here is the code.let me know if it helps!

Sub M()


    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ws.Range("B4", Range("B" & Rows.Count).End(xlUp).Address).Sort Key1:=[b4], _
    Order1:=xlDescending, Header:=xlYes
    Next ws
  
End Sub

Re: Help in the coding part

Glad you were able to do it yourself!

Re: Sum the data after filtering

Please try and post the sample excel sheet for the ones who are willing to help. People do not want to spend time to create an excel sheet for someone else's work.. Hope you understand where I'm coming from !

Re: Extract specific text based on codition from a string in a cell.

Try the below Formulae..Let me know if that helps!

In D2

[BFN]=IFERROR(IF(IFERROR(SEARCH("Curr:",A2,1),0)>0,LEFT(A2,IFERROR(SEARCH("-",A2,1)-1,0)),IFERROR(LEFT(A2,IFERROR(SEARCH("Non-Compliant",A2,1),0)-1),LEFT(A2,IFERROR(SEARCH("Compliant",A2,1)-1,0)))),"ERROR")[/BFN]

In E2

[BFN]=IFERROR(IF(IFERROR(SEARCH("curr:",A2,1)>0,0),IFERROR(MID(A2,SEARCH("/",A2,1)+2,SEARCH(")",A2,1)-2-SEARCH("/",A2,1)),0),IF(RIGHT(A2,13)="Non-Compliant","Non-Complaint","Compliant")),"ERROR")[/BFN]

Re: Cannot get PowerView on the ribbon

Quote

According to your description, you have already have the Power View add-in loaded, but your still can't set Power View tab in the ribbon. Right?
In Excel, the Power View tab appears only when the current worksheets contain Power View reports. Please go to INSERT->Power View to insert a Power View report. If the button is gray, you may need to repair or reinstall your excel.

Source

Re: Paste the data based on condition

Try the below code ! Next time please attach a sample workbook so that it will be easy for people who are willing to help!

Sub M()


For i = 2 To Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row
x = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
If Sheets("Sheet1").Range("E" & i) > 0 Then
Sheets("Sheet1").Range("D" & i).Copy Sheets("Sheet2").Range("A" & x & ":A" & x + 1)
Else
Sheets("Sheet1").Range("D" & i).Copy Sheets("Sheet2").Range("A" & x)
End If
Next




End Sub

Re: Delete Content In Next Row Cells If Duplicated

That can happen only using VBA!