I have a spreadsheet that within Range(E2:H800) there are a number of different text/string values and blanks.
Using a Macro, how can I quickly fill a variable with each of the unique (non-blank) variables?
I have a list of data in a range on an Excel worksheet.
I want to create a macro that searches the range e.g. $A$1:$A$10 and identifies the unique cell values into an array.
So if range data = "A,B,B,C,C,D,D,D,E" I would end up with an array containing 5 items "A,B,C,D,E".
I would then like to cycle through each array item and bring up a message box displaying the value of each array item.
I have an array called "Universe", which have duplicated values. I am trying to write a code that gives me an array which have only unique values.
View 9 Replies View RelatedI need to capture a list of unique usernames appearing in a column on the sheet, and also the number of times each user has a desired value occur for their row (an error). The number of users and the names of the users will change from one sheet to the next making a static solution unworkable. The user names will be unsorted in the column.
My first reflex is to use an array, and an associative array if possible would allow me to reference the user by name, instead of a clumsier index number and iterating through a loop to verify if the user is already stored in the array. I don't know if it's possible in VBS though. I'll add any new users names to this array if they aren't already in there.
In an excel i have 3 columns they it contains around 12000 records
Group FA Title
A S1 bbbb
A M1 xxxx
A M2 eeeee
A S1 ffffff
A S1 pppp
A M3 aaaaa
A M2 ooooo
A M2 qqqq
A M1 ttttt
Here i need to get the unique FA, so i filter the column FA, my question is, After filter with FA column ,is there any way to store these unique FA(ie S1,M1,M2,M3) into an array using vba?
I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
=SUM(IF(FREQUENCY(IF(('TX1'!$B$2:$B$15=B2)*('TX1'!$C$2:$C$15=C2)*('TX1'!$D$2:$D$15=D2),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0)),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0))>0,1))
I'm trying to build a results sheet for some work that we do.
1 column has the month in which the work was done. ie.
01/01/04
01/01/04
01/02/04
01/02/04
01/04/04
01/09/04
01/09/04
01/09/04
etc... In the example above, as you can see, some months have multiple jobs
done, others only 1. Also there are some months when no work was done at all.
The dates are always the 1st of the month, so no problems in looking at
specific dates, just the month values.
So if I wished to show the last 4 month's when work was done, what function
would I need to use to get Excel to produce a list like this...
01/01/04
01/02/04
01/04/04
01/09/04
What kind of formula I need to identify if in a given range there are only unique values?
The range (containing 12 cells) may contain only two text values: "Actual" or "Forecast".
If all values are equal to "Actual" I would like the formula to return "A".
If all values are equal to "Forecast" I would like the formula to return "F".
If there are both "Actual" and "Forecast" values found in the range I would like the formula to return "A/F".
I use the following code to extract a unique list of values and paste the list to a specified range:
Public Sub extract_unique()
Dim a As Range, v As Range, w(), i As Long, r As Range
Set a = Selection
Redim w(1 To a.Count, 1 To 1)
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) And Not .exists(v.Value) Then
i = i + 1: w(i, 1) = v.PrefixCharacter & v.Value
.Add v.Value, Nothing ..............
The code gets interrupted at this line:
If Not r Is Nothing Then
with the following error: "Code execution has been interrupted."
When I click debug, the line above is highlighted. If I click the sideways triangle to resume code execution it finishes normally. What can I do to stop my code from being interrupted?
I have data of different accident year. Now I need to identify different Accident Year and use them in Macro to do certain things. For example in the file attached I am using them in Msgbox. Now to acheive that I first extracted unique values using advanced filter on Column C and later use the different value to POP up the Msgbox. (This is achieved). Now I want to skip this part of doing Advance filter to extract unique value. Is there a way I can know unique value of data through the code itself and use them in program. here is what I am doing currently: I have delted the most of the data cause I am concerned with coulumn containing acc_year only.
Sub test()
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= Range("C2" _
), Unique:=True
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C3").Select
Do While ActiveCell.Value <> ""
MsgBox ("The Year is " & ActiveCell.Value)
a = ActiveCell.Row
Range("C" & a + 1).Select
Loop
End Sub
i have a very large spreadsheet filled with telephone numberS and some other codes that go with them...i need to match the codes with the phone numbers.
Sub TRCO()
Dim TNs As Long
Dim i As Long
Dim TempArray() As String
Dim TRCO As String
Dim CD03 As String
Dim ASOC As Range
TRCO = "TRCO"
CD03 = "CD034DF1"
'Filter TN's
Columns("B:B").EntireColumn.Insert
Set tempRange = Range("A6", Range("A65000").End(xlUp))
With tempRange
. AdvancedFilter _...................
I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.
Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.
I need to know how to read all the criteria1 brought by the autofilter (in the dropdown box) from one column one by one and then print the table. Is that possible? I've read that it's hard but never read how to do that.
View 4 Replies View RelatedI found this thread which deal with populating unique values in my listbox.
Here is the thread link. [URL] ........
What these line of code mean (the ones in red).
Code:
Private Sub UserForm_Initialize()
Dim v, e
With Sheets("maintenance").Range("c2:c500")
v = .Value
End With
With CreateObject("scripting.dictionary")
[Code] ......
Note : I simple tried to check the value of v by giving msgbox (v), just below the line v = .Value.
But I got run-time error 13 : Type mismatch error.
Here is a sample of database,
A
1
Jan-12
2
Feb-12
3
Mar-12
4
Apr-12
5
May-12
6
Jun-12
7
Jul-12
8
Aug-12
9
Sep-12
10
Oct-12
11
Nov-12
12
Dec-12
If i lookup a value in the range A1:A12, say Sep-12, i need to get the cell address instead of the value of the row. but i know how to get cell address using CELL function. but i need to get cell address when i lookup the value.because lookup value will be dynamic.
I would like to fullfil a range a cell with different strings. As the cells are jointed to each others, I would like to pass the string array to the range and not to pass each string to each cell, to spend time.
I mean, with the function
Dim stTxt(3) As String
range(Cells(1,1),Cells(3,1)).value=stTxt()
But I have some difficulties with this function:
-if the range is a column, the function works, but if the range is a row, then only the first value of the array is passed, and to all the cells of the range
-I didn't manage to use this function when using
Range(Cells(x,y),Cells(z,t))
and when the range is in another worksheet. I need to use
Cells(x,y).resize(z-x,t-y)
(I'm using Excel 2000).
how I can pass correctly my string array to a row of cells? (cf 1. point).
I have an issues with a formulae I have written to measure two factors;
A; How many different countries appear within a certain date range. B; What countries are they.
I tried a unique list, but could not get it to work within a range? I dont particuarly care how the data is pulled through as long as I do it. At the moment I have become stuck in 'Unique Ranges' and have n given up?
The formulae issue is in the tab "Problem_Formulae", the dat in the sheet 1, and the date ranges in sheet 2. Also, just to make life a bit more challenging pivot tables are a no go .
I am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range
I am using the following array formula to establish an overall count:
=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))
Ctrl+Shift+Enter
The date column is E:E.
The store number column is G:G
In A1 of Sheet1!, I need a formula that lists and sorts all the 'unique' values from range
ECWP!$O$6:$Q$1505
i have two arrays that I want to use in a trend function. I don't think i can just use the array as is in the fucntion so my guess is that I need to pass the array into a range of data, and help on how I can do this? (also this is in VBA, fyi)
View 9 Replies View RelatedYou have an array and a range of the same size and you have to put the array values into the range, something like this:
Dim i As Integer
Dim myCell As Range
Dim myArray(10) As Double
i=0
For Each myCell In Range("A")
myCell.Value = myArray(i)
i = i + 1
Next myCell
except that this code looks a bit awkward to me.
Sheet1 has following two columns
----A----------B
01-07-2007---10
01-07-2007---20
02-07-2007---30
02-07-2007---40
02-07-2007---40
03-07-2007---60
03-07-2007---70
03-07-2007---70
03-07-2007---90
04-07-2007---100
04-07-2007---110
Sheet2 has following three cotrols
Textbox1=02-07-2007
Textbox2=03-07-2007
combobox=?
I want to Extract Unique values from column B between date range textbox1 and textbox2 and finally how to add these unique records in combox1
I'm trying to define a range of values in two cells M1:M2; lookup that range of values in a three column array (K12:M600) and sum the values in column M12:M600 if they are positive values. I've been trying this formula:
=SUMIF(M12:M600,">0",(VLOOKUP(M1:M2,K12:M600,3,TRUE)))
but cannot seem to get it to work.
I have an array that refers to a range of values:
Dim vtimeheader As Variant
vtimeheader = Worksheets("output").Range("e4:xfd4").Value
(1) I now want to delete all values in the array
(2) I want to apply the format "General" to all the cells in the array
I have a list of names from cell A1:A10 in sheet "Input." Each of these names has its own corresponding sheet in the workbook. I want to be able to run the same exact VBA code for each sheet. In other words, I am trying to get my name variable to automatically change to the next value on sheet "Input." I'm sure this is pretty simple to do, but I can't seem to find anything that works!
View 5 Replies View RelatedHow I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?
For e.g., in Column A (row 1 - 10):
Proj-001
Proj-001
Proj-002
Proj-004
#N/A
#N/A
Proj-007
Proj-002
On this is a column of Categories and a Column of sizes. I want to use these in a user form. The user will select their Category from a drop down list and the second drop down list will include only the sizes that appear next to the chosen category So for example in the attachment if the user chose 'AUD' as a category they would get the size choices of, '2x4 insert', '2x4 replica' and 'A4L' in the other drop down menu.
The master list of sizes will have to remain on a worksheet as this is what other operators will amend from time to time.
I have four named ranges (Segment, Keyword, Impressions and Dropdown) and I would like to create a formula-based ranking of keywords by impressions and clicks. Using the following array formula, I am able to return the correct values for impressions or clicks:
{=LARGE(IF(Segment=DropDown,Impressions),$H7)} where $H7 is the number ranking 1, 2, 3 etc.
My question is what array formula could be used to find which row in the array returned that number and then pulls the data from the same row in the other named ranges?
Essentially find row of {=LARGE(IF(Segment=DropDown,Impressions),$H7)} but return Keyword and Clicks on that row.
Other Notes: I cannot use pivot tables and some values might be the same which would make Vlookups not accurate for duplicate values.
Link to an example document to clarify this. [URL] .......
Im using excel 2010 As it's 60 times quicker I was trying to speed up my code and replace all loops by putting the value into an array, and then transfer the array to the worksheet
It seems to be straightforward for math calculations like in this example:
[URL]
But no luck with the one below. I was trying to test it on a simple loop which replaces two types of string into the 3rd one:
Code:
Dim lastrow, lastrow2, i As Long
With Worksheets("KPI5")
lastrow2 = .Range("N" & Rows.Count).End(xlUp).Row
.Range("T7:T" & lastrow2).Value = .Range("F7:F" & lastrow2).Value
For i = 8 To lastrow2
If .Range("T" & i).Value = "Modification" Then
[code]....
I've am array formula that I use to provide highest values:
Code:
{=INDEX(Data!$D$2:$D$128, MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))
=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-MIN(ROW(Data!$D$2:$D$128))+1)),
MATCH(LARGE(Data!$D$2:$D$128,ROW(A1)), INDEX(Data!$D$2:$D$128,
MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-
MIN(ROW(Data!$D$2:$D$128))+1)), , 1), 0), 1)}
I want to replace the range Data!$D$2:$D$128 with a dynamic reference: like that one:
Code:
ADDRESS(MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+1,
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")&":"&ADDRESS(
MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+COUNTIF(Data!$Q$2:$Q$10000,Summary!$B$1),
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")
The two formulas work well in separate sheets but crash when put together, how could I possibly insert the second address formula into the first one?