I need to find top/bottom N elements in a range. I have created an array out of the range. I am not sure how to find the top/bottom elements? LARGE function would give me the actual value, however I am interested in finding the indexes.
View 3 RepliesI have a column with serial numbers, i want to go through that column and store all the unique serial numbers and the number of occurrences. then i want to know what is the highest occurrence.
View 9 Replies View RelatedI have a named range, called 'event' relating to cells a2-a200, I have a named cell, 'eventcounter'. I was hoping that I would be able to use the named cell eventcounter in formulae on the cells in the event range rather than having to use the cell address. So instead of having a formula that was something like this, =if(a3= etc etc.
I could refer to the range event and eventcounter. Eventcouter gets its value simply by counting the number of non blank cells in the event range, so consequently - I can only figure out how to address the last entry in the event range in the manner that i wish.
I have several tables on one sheet, each of which has a Grand Total value at the bottom right of the summed data range
I have manually selected (using ctrl + left mouse click) each Grand Total cell and defined all these cells as a Named Range called Grand_Totals
I thought it would be possible to reference each of the cells in the Grand_Totals range individually.
The =Index function works fine if the data is contiguous but I can't figure out how to get anything other than the first element of the Grand_Totals range
So for example
Lets say the cells of the Grand_Totals non-contiguous named range are F10,N10,F30,N30,F50,N50
How would I reference the third element/cell (F30) in this range. I should point out that I actually need to reference all the cells in the Grand_Totals range and the range has a lot more than 6 cells
I have a variable that gets set to an address range:
Code:
TAG_RANGE = Sheets(BAL_SHT_TAB_NAME).Range("A1", Sheets(BAL_SHT_TAB_NAME).Cells(Rows.Count, Range("A1").Column).End(xlUp)).Address
In the first loop that executes this command TAG_RANGE gets set to $A$1:$A$39
I want to loop through the values in that range and run tests against them. Is there a function that will take the values in the address range an convert them into an array so that I can use something like this:
Code:
For Counter = LBound(TAG_RANGE_ARRAY) To UBound(TAG_RANGE_ARRAY)
[run tests]
Next
Or is there someother direct way to do this other than creating a loop that fills the array element by element
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
I have a report as below:
Cust IDCust NameAcct 1Acct 2Acct 3…Acct 10…Acct 100Total123AB 123 255 663 - - 1,041 456AC - 3 833 51,008 - 51,844 157AD - 46 46 550 - 642 226FE 5 7,733 - - 553 8,291 658FZ 9 8,833 - 444 555 9,841
and would like to get the final product as following:
Cust IDCust NameAcct 1Acct 2Acct 3…Acct 10…Acct 100Total123AB 123 255 663 - - 1,041 456AC - 3 833 51,008 - 51,844 157AD - 46 46 550 - 642 226FE 5 7,733 - - 553 8,291 658FZ 9 8,833 - 444 555 9,841 Total Region 1 137 16,870 1,542 - 52,002 - 1,108 71,659
Criterias:
# of Rows are variables.
# of Columns are variables.
Sum amount for each of account including total per customer ID.
Total region line is hilited and bold.
Dim struserID As String
Dim strPassword As String
Dim strUploadFile As String
Dim strQueryURL As String
Dim objIE As SHDocVw.InternetExplorer
Dim htmlDoc As MSHTML.HTMLDocument
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlColl As MSHTML.IHTMLElementCollection
Set objIE = New SHDocVw.InternetExplorer
struserID = "xxxxxx"
strPassword = "abcdef"
strUploadFile = "C:Misc estupload.txt"
Code navigates to sign on page, enters userID and password, clicks on submit. The password used causes upload page to load. This all works ok. After upload page is loaded, the code does not enter either of the two following "for" routines.
I have a few hundred of these divs, that I need sort and filter.
I would like to remove ALL HTML and be only left with the following:
Example Name = KeithEmail = keith@example.comStore = Store 1 (This only has 2 options. Store 1, Store 2)
All of the below code is in an individual single cell, so there are about 400 cells
HTML Code:Â
<div userid=""286"">
<div id=""694"">
<h1 style=""display:none"">Keith</h1>
[Code]....
If I have say several hundred elements stored in an indexed array and I want to clear them each time the sub or function holding them is called, is there a way to erase the contents of the entire array without having to loop through each element.
Something like array().clear? Note that each time the sub is called, they are also redimed to some reference.
I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array. For example: Suppose i have 10 strings in the array. Is there a command to determine that there are only 10 elements in the 100 possilble array?
View 11 Replies View RelatedI can't seem to get my head around the simple issue of assigning a range of cell data to an array! Here is a snippet of what I have, maybe you can see the flaw:
View 8 Replies View RelatedI was wondering if there is a way to have the elements of an array counted. I have something like this:
View 6 Replies View RelatedI've been teaching myself VBA for the purpose of processing some time resolved data from an instrument. I've come up against a problem and I just can't work out why the code is failing. (using excel 2007, VBA 6.5)
I declare some arrays as dynamic, then ReDim them to a user defined number (with Option Base 1) and input data from a spreadsheet into the array. I can then output the data into the spreadsheet again and the lbound and ubound functions return the correct values for the array. What I cannot do is actually access individual elements. If I specify array(i) I get the "Subscript is out of range" error. The code relevant to this problem is as below (simplified as there are more arrays):
Writing macro for "Removing duplicates with less elements in a row."
I am trying to remove duplicate entries from a file based on the number of data present in a row. Please see below
Example:
Name Col-1 Col-2 Col-3 Col-4
abc 1 3 4
abc 1 2 3 4
abc
abc 1 4
I want to retain the row with maximum elements(row 2 in above eg.).
Result should be:
Name Col-1 Col-2 Col-3 Col-4
abc 1 2 3 4
Currently I am doing this manually, by adding countA at the end of each row, then arranging them in descending order. That will make sure that the row with more data comes first n hence gets retained, while other rows gets deleted.
Can this be done using Macro?
Below macro just deletes the rows,
Public Sub DeleteDuplicateRows()
Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
[Code] .....
I have a question regarding arrays. If I have too many elements in a 1D array(let's say 1000), how can I list all of them in a msg box (separated by comma)?
View 2 Replies View RelatedI am facing the some issue while tried to display the data from array. Below is code I have to tried to display the data from array.
Scenario: I have assigned the few values in my excel to an array and tried to display the data stored in array
Sub Main()
Dim Rows_Array() As Variant
Dim i As Integer,
Sheets("EmployeeWise").Select
Rows_Array = Range("C6:C" & Range("C65536").End(xlUp).Row).Value
For i = 1 To UBound(Rows_Array())
MsgBox Rows_Array(i)
Next
End Sub
I am trying to present a 10X10 Matrix Range (A1:J10) full with UNIQUE INTEGER random numbers. I thought to use an Single Dimension Array (100 deep) and fill it with the Rnd() Function.
Then, I thought to check the RANK of each element of that Array and transfer it to 100 cells (10X10) in the Sheet (assume A21:J30) with the help of 2 nested loops.
My problem is: How to find the Rank of each element within ARRAY1. Is there a way to refer to an Array as to a Range in a Worksheet. (I do not want to transfer 100 values from the Array to the Sheet - I rather prefer to check the Rank WITHIN(!) the Array).
Option Base 1
Sub MiKe()
Set AWF = Application.WorksheetFunction
H = 10
V = 10
Redim Array1(H * V)
For CL = 1 To H * V
Array1(CL) = Rnd()
Next
For HC = 1 To H
For VC = 1 To V.........................
I've used Rob Bovey's Chart Labeller a lot recently, but this morning when trying to add some labels, I get the error message
"Unable to get the Name property of the Series class (bGetSelectionIndexes())"
Is there a limit to the number of series it will work with?
I'm trying to populate a ComboBox or ListBox with elements parsed from a html code I've already parsed from a webpage. Explaining: I was able to extract from the webpage code the part that contains the information I want, which is:
VB:
<li><a href="/universidad/" rel="*">Universidades</a></li>
<li><a href="/universidad/duoc/" rel="28184">DuocUC</a></li>
<li><a href="/universidad/inacap/" rel="28162">INACAP</a></li>
[Code].....
Now I want to make a ComboBox containing every university as a different option and, if possible, to assign them the corresponding values shown in the code.
Join an array with Text elements to create a string that can be Evaluated
So for instance if I have Array("A", "B", "C") and I want to evaluate("=({" & Join(array, ",") & "})="A)"). Is there any way to do this without having to loop or push to a Named array first? I'll even take this evaluate thing if I can do it with text and numbers
I've defined a string array and would want to use it as a basis for a vlookup. Is it possible to find the elements of the array directly in the worksheet ?So far I've got :
[Code] .....
Filling all elements of a two dimensional array?. I know that I could write something like:
View 3 Replies View RelatedI am trying to produce an Excel file filled with data from the XML file.
Some of the cells need to have the same element data. However the Microsoft Excel is not allowing to map an XML element to more than one cells.
Is that possible to return the repeated elements from the range for example
Elements
Repeated Elements
A
A
A
B
[code]....
I have a listbox on a userform, with multiselect set to 2 (Multiselect extended). When the listbox is displayed for the first time, no elements are selected. At a certain point in this application, I want to get back to the original state and deselect all items.
I do that with the statement Listbox1.listindex=-1. I hope that is the right thing to do. However, the elements that originally were selected are still blue-coloured, like they became when the user selected them. How do I do to make them look deselected?
It also seems to me that the array Listbox1.selected(x) still holds the value "true" for elements that were selected. Is it so that in addition to say listbox1.listindex=-1, you have to loop through the elements in listbox1 and set all of them to false?
I have about 6000 rows of data in column A:G and growing
Column A is the key where i filter between a lot of duplicates
If i filter on X100000 there is about 432 elements (rows)
if i filter on Y100100 there is only 43 elements (rows)
The problem is i only want to copy the last up to 90 element to another sheet. I can copy all 432 rows to a second sheet and then copy last up to 90 rows thats what im doing now. And i am familiar with
"Selection.SpecialCells(xlCellTypeVisible).Select"
But still cant figure out haw to do it directly in the filtered sheet.
I want to test how many elements are not empty within a variant array.
For example for arrays Arr1 and Arr2 below the answers would be:
- for Arr1 not empty elements=1
- for Arr2 not empty elements=0
Arr1(0)=empty
Arr1(1)=2
Arr1(2)=empty
Arr2(0)=empty
Arr2(1)=empty
Arr2(2)=empty
I've tried with function COUNTA inside VBA but counts even the empty values:
Code:
NotEmptyCells = Application.WorksheetFunction.CountA(Arr1)
Is there another function to count this from an array or alternative way?
We have a need for formula that works out cost pricing but 1 of the items is based on the total cost excluding that item (VAT) and another is based on the total price (Cost of payment solution)...both are percentages of the respective factor. The way we have tried at the moment produces a circular formula...is there a way around this without a circular formula or a way to make a circular formula work to ensure profit is £0?
View 9 Replies View RelatedI recently cahve been working with a lot of webpages. Documenting the pages is quite loborious and inaccurate. I recently came across a utility that would explort all of the elements, their types etc and put it into a worksheet. For the life of me, I have not been able to find it. I was wondering if anybody knows of a utility like this, or how I could write a macro to parse this info.
View 2 Replies View Related