# Posts by mohammedismail

• ## Get Top N % using Formula

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"]

[tr]

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

[/tr]

[tr]

[td]

ABC

[/td]

[td]

63,855,641.60

[/td]

[/tr]

[tr]

[td]

DEF

[/td]

[td]

36,100,068.26

[/td]

[/tr]

[tr]

[td]

SGH

[/td]

[td]

23,324,389.74

[/td]

[/tr]

[tr]

[td]

ABC

[/td]

[td]

20,534,610.54

[/td]

[/tr]

[tr]

[td]

DEF

[/td]

[td]

14,017,828.67

[/td]

[/tr]

[tr]

[td]

SGH

[/td]

[td]

11,669,652.96

[/td]

[/tr]

[tr]

[td]

ABC

[/td]

[td]

11,452,970.69

[/td]

[/tr]

[tr]

[td]

DEF

[/td]

[td]

10,868,123.63

[/td]

[/tr]

[tr]

[td]

SGH

[/td]

[td]

10,072,343.93

[/td]

[/tr]

[tr]

[td]

ABC

[/td]

[td]

9,995,804.15

[/td]

[/tr]

[tr]

[td]

DEF

[/td]

[td]

9,812,192.01

[/td]

[/tr]

[tr]

[td]

SGH

[/td]

[td]

9,680,726.24

[/td]

[/tr]

[tr]

[td]

ABC

[/td]

[td]

9,632,613.12

[/td]

[/tr]

[tr]

[td]

DEF

[/td]

[td]

8,159,122.82

[/td]

[/tr]

[tr]

[td]

SGH

[/td]

[td]

6,429,932.46

[/td]

[/tr]

[tr]

[td]

ABC

[/td]

[td]

6,065,187.29

[/td]

[/tr]

[tr]

[td]

DEF

[/td]

[td]

6,043,460.64

[/td]

[/tr]

[tr]

[td]

SGH

[/td]

[td]

5,887,114.29

[/td]

[/tr]

[tr]

[td]

ABC

[/td]

[td]

5,523,253.55

[/td]

[/tr]

[tr]

[td]

DEF

[/td]

[td]

5,231,107.87

[/td]

[/tr]

[/TABLE]

• ## Count Unique Values by category

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.

• ## Creating flexible tables with update options

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..)?

• ## problem with AVERAGE function

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

• ## Macro to open file search window, open file, then run code

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

Try this !

Code
``````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``````
• ## Macro that counts all characters in Worksheet

Re: Macro that counts all characters in Worksheet

Glad to have helped you! Sometimes googling helps

• ## Macro that counts all characters in Worksheet

Re: Macro that counts all characters in Worksheet

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

• ## copying coloured cells

Re: copying coloured cells

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

• ## Depreciation

Re: Depreciation

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

• ## Getting data with web query and rearrange the data

Re: Getting data with web query and rearrange the data

Do you Need VBA or Formula will also help ?

• ## Depreciation

Re: Depreciation

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

[ATTACH=CONFIG]68602[/ATTACH]

• ## Getting data with web query and rearrange the data

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"]

[tr]

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

[/tr]

[/TABLE]
to 45953 ?

• ## Depreciation

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!

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

• ## Sort Multiple Sheets with Macro Loop

Re: Sort Multiple Sheets with Macro Loop

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

Code
``````Sub M()

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

End Sub``````
• ## Help in the coding part

Re: Help in the coding part

Glad you were able to do it yourself!

• ## Sum the data after filtering

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 !

• ## Extract specific text based on codition from a string in a cell.

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]

• ## Cannot get PowerView on the ribbon

Re: Cannot get PowerView on the ribbon

Quote

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

• ## Paste the data based on condition

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!

• ## Delete Content In Next Row Cells If Duplicated

Re: Delete Content In Next Row Cells If Duplicated

That can happen only using VBA!