Multi-dimensional Array Solution
I am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level:
I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red).
Here is my existing Keyword highlighting
Sub Highlight_Keywords()
Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Summing In A Multi Dimensional Array
I've created an array that is 60 pairs of cells wide. Each row in the array represents a different account. The 60 pairs of cells contain up to 60 payments in a payment stream. The first cell of each pair may contain an amount of interest to be paid, or zero if the time band in which it was/will be paid is not within the time range that I'm evaluating. The second cell of each pair may contain a number between 1 and 10 representing the time band in which the interest will be paid, or zero if the payment date falls outside of the time bands. I need to be able to sum the interest to be paid in each time band for all of the accounts in my database. Is there an Excel function that I could use or will I need to write some VBA code to loop through each row?
View Replies!
View Related
Populating A Multi-dimensional Array
I have a spreadsheet which I will be adding to over time. I need a macro that will look for the last 10 rows and then pull together a summary table based on data from several of the columns on each of those rows. I've tried to go about this by using an array. The array will always be the same size (it will always be 10 by 11, i.e. EngineArray(10,11)-basically the same size as my summary table). I've also set Option Base 1. What I’ve got so far is below. It isn’t working properly though. To start with I was getting ‘type mismatch errors’. Now instead of reading the numbers from the selected cells into the array, it just changes all the cells I’m trying to read from to ‘TRUE’?
View Replies!
View Related
Evaluate Multi-Dimensional Array Matrix
I'm trying to use VBA to do some matrix processing. I have successfully done matrix processing in the spreadsheet, but I'm looking to port the logic into VBA to more easily reuse central formulas and reduce the amount of code in the spreadsheet. The function shown below works. I had to hack around the variable declarations to eliminate processing/compile errors but it seems to function (no pun intended). While debugging I noticed that this function would seem to get called 4 times. I do have other modules running, although I don't think any other modules are affecting this. After some input from shg, I updated the variable declarations, but I'm getting an "Overflow" error reported. Sometimes (not always) a divide by zero also pops up. I do have the Msgbox for errors at the end, I haven't tried removing that part to see if the error reporting vanishes. I'm not sure 'hiding' error alerts is the best thing to do anyway...
View Replies!
View Related
Multi-dimensional Array Not Showing In Listbox
I have made the function below to return a variant multidimensional array. I pass the function an array of folder paths that I wish to search through looking at subfolders within that path where their name matches a search string that I pass to the function. eg., it will find a folder named "Catnap" if you pass the string searching for as "Cat*". The size of the array it builds depends on how many folders it finds that match the search string and so needs to be built dynamically. Hence, I believe it builds a 2 dimensional array horizontally and I transpose it at the end of the function. In each element I put the folder name that was found in the first dimension and the path to that folder in its second dimension. I have a 2 column listbox on a form that I set this array to. eg., Me.ListBox1.List = DirPaths("C:","Cat*",vbDirectory,100) This works fine and shows a list of folder items found by folder and path in the two columns of the listbox if there is greater than 1 search found. However, when the search only finds 1 then the listbox shows the folder name with the folder path in row 2 of the list box. (See below).....
View Replies!
View Related
Fill Range From Multi-dimensional Array
I searched and found a few posts about transposing arrays into a range of cells, but none of them seemed to solve my problem. So, my problem is, I have a .Net assembly which provides various functions to allow Excel to access our Oracle DB stored procedures/tables, etc. This assembly is exposed via COM Interop. I call the GetSPINTypes() method, which returns me a list of type pairs (ID, Name), in a CSV string format. I split the CSV into rows, and then put each row into a 2-dimensional array. I then need to dump that array into one of my sheets in Excel, so I try to do the usual Range.Value = Array, but this sometimes tells me there is a type mismatch, and most times just doesn't fill the range. I've checked my arrays in the watch window, and they have definitely been filled in correctly, the values just don't appear when they are put into the sheet. See the code I'm using below:......
View Replies!
View Related
Join Dynamic Multi-dimensional Array
the built in "Join" function can join all elements of a 1-dimensional array into a string with delimiter. Now, how do I do that with multi-dimensional array if I just want to join 1 dimension of it. For instance, I have: m = 10 n = 20 Redim my(m, n) 'assign values to array here... 'I want to join, say, my(5, 0 to n) only 'How to do that without declaring a new 1 dimension array? Also, I want to write a join sub/function to do the above for n-dimensional array, do I need to write each one for every number of dimension (1 sub for 2-dimension, 1 sub for 3 dimension, etc.)?
View Replies!
View Related
Fill & Rearrange Multi-dimensional Array From Another
I've got the folowing array's date1>company1>price >Company2>price >company3>price >enz Date2>company1>price >company2>price >enz enz. But these are not the array's that i need for a correlation that i want to make. Is it possible to transform the array's above to an array such as: Company>date1>price >date2>price >date3>price >enz
View Replies!
View Related
Creating A 2-dimensional Array From A 1-dimensional List
I've been a lurker on this forum for a long while and it's always been able to provide me with lots of excel tips, and for that I am grateful! But this time I have a question that I can't find the answer to here, or anywhere else on the web after a few hours of looking. As a note, I'm not very experienced with Excel, probably somewhere between novice and intermediate. What I need to create is a 2-dimensional array of data. The vertical (y-axis) are the tools, and the horizontal (x-axis) are the jobs. Where the y-axis and the x-axis intersect, will be the quantity of tools needed for that specific job. There are almost 1500 tools, and 100 jobs.
View Replies!
View Related
Checking If Variable Is Multi-dimensional
Is there a quick way to check if a variable is an array or multi-dimensional? I am trying to send variables to a sub to print to excel but I am sending mixed variables - some are one dimension, some are two dimensional. Here is what I am using: Private Sub Send(Item As Variant, Top As Integer, ToSheet As String, Row As Integer, Column As Integer) 'Sends values to Excel ' Loop counter Dim i As Integer For i = 0 To Top - 1 ActiveWorkbook. Sheets(ToSheet). Cells(i + Row, Column).Value = Item(i) Next End Sub I suppose I could set up a second routine to handle one dimensional variables, but I thought I would ask the pros -
View Replies!
View Related
Lookup Values From A Multi Dimensional Table / Data Sheet
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex) On the report I have values 123A and 1234A On the data tab I have table that has. Ref Tot Value Desc Value1 Value2 Value3 1 123A Widgets 1 2 3 2 123A Widgets 4 5 6 Ref Tot Value Desc Value1 Value2 Value3 1 123B Nuts 7 8 9 5 123B Nuts 1 3 5 Ref Tot Value Desc Value1 Value2 Value3 7 1234A Bolts 2 4 6 11 1234A Bolts 3 5 8 So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View Replies!
View Related
Interpolate Two-dimensional Array
With a known X and Y, trying to solve for Z from a table. ie: IF a variable X is defined across a row, another variable Y defined down a column, the data field Z fills in-between. What do I need to use to interpolate for both X and Y to solve for Z?
View Replies!
View Related
Function For 2-dimensional Array
I am trying to write a public function that fills a table for a 2-dimensional array and am having trouble with my named ranges. The x-axis is based on years (range F2:O2) and the y-axis is a q_factor (range E3:E23) so the data range would be (F3:O23. The following outlines my logic: Public Function bondValue(years As Range, q_factor As Range, z As Double) Dim nRow As Long, nCol As Long Dim bondPort As Range nRow = q_factor.Rows.Count nCol = years.Columns.Count sumTau = 0
View Replies!
View Related
Fill ComboBox With 2 Dimensional Array
I can set up a 2 dimensional array by using array = Workbooks("Workbook.xls").Worksheets("Data"). Range("D_all") as range D-all contains 2 columns and 20 rows I can send that list to a combo box by using Me.ComboBox1.List() = array If I have ColumnCount set to 2 then both columns will be listed If I have ColumnCount set to 1 then the 1st part of the array will be listed But how do I list just the 2nd part of the array
View Replies!
View Related
Data Type Of Coordinates Of 2-dimensional Array
I have a big array "DataArray" and want to access it: For i = 1 To 4 variable = DataArray(SourceArray(i)) Next i "DataArray" has two dimensions, so SourceArray has to consist of data like this: SourceArray(1) = 1,2 sourceArray(2) = 2,4 etc What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.
View Replies!
View Related
Multi-cel Array Formula Not Working
See attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour. Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.
View Replies!
View Related
Entering Multi-cell Array Formulas
I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1". Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...
View Replies!
View Related
Multi-result Array Formula With Max
I am using multi-result array formulas to reduce error checking when processing arrays of numbers. However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into. Example for A1=2,B1=4 =2*A1:B1 array enetered gives 4,8 However =Max(2*A1:B1,5) array enetered gives 8,8 rather than 5,8. I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.
View Replies!
View Related
Having A Solution Returned As A Value
Is it possible to have a formula that will return the answer as the value. For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value. I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution. Would this require a macro?
View Replies!
View Related
SumProduct Fast Solution
my questions below are more oriented to hows & whys, as opposed to asking for a fast solution to a pressing problem. I'm just reviewing the basics of SumProduct to be sure that I understand what I'm really doing when I deploy the function. So, I have the following formula:
View Replies!
View Related
Use Lookup Formula For Solution
I am trying to solve a problem with = LOOKUP. Here's the data. A1 can have a value between 1-100. IF A1 IS <80.00, NO MULTIPLICATION IS NEEDED IF AI IS 80.01 TO 85.00, MULTIPLY A2 BY 0.37% IF AI IS 85.01 TO 90.00, MULTIPLY A2 BY 0.52% IF AI IS 95.01 TO 95.00, MULTIPLY A2 BY 0.87% IF AI IS 95.01 TO 100.00, MULTIPLY A2 BY 1.07% Is there a =LOOKUP formula that can be used here?
View Replies!
View Related
Provide Both Formula And Vba Solution.
I have an excel sheet which has been exported from an 'Employee Attendance Software', http://www.flickr.com/photos/34247990@N06/4299288997/ In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow. Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion http://www.flickr.com/photos/34247990@N06/4299288959/
View Replies!
View Related
Hlookup With Dates Solution Needed
I am using HLOOKUP to show a date depending on the month shown in B10. If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "". Here is my formula: =HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE) I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.
View Replies!
View Related
IF Input And Color Identify Solution
I have the new version of excel and I can't figure the correct formula to do the following. Attached is my example. A1 is available Inventory qty. C1:H1 are forcast qty's. I would like to have the available forcast qty. shown in black and the unavailable forcast qty. shown in red based on available inventory shown in A1 Example So C1,D1 and E1 would be Black and F1 and G1 would be red. As inventory changes so would the colored numbers on the forcast.
View Replies!
View Related
Solution/altenative To Data Table Using >2 Variables
Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.
View Replies!
View Related
Text Overwrites Blank Cells! Solution?
My problem is that long text for the cell overlaps any blank cells to the right. I have more then 20.000 rows, so I would not like to put a space character in all the blank cells. Is there a way of formatting which will stop this? I don't want these texts to overlap the blank cells!
View Replies!
View Related
Finding Effective Solution For Updating Links
I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
View Replies!
View Related
VBA Solution To Compare/replace Data Between Columns
I have two columns of adjacent data Call them A & B (although this could differ) If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.
View Replies!
View Related
Desperately Seeking Solution - Payroll Tax Withholding
I need to determine federal tax withholding from gross pay. If gross amount (for S-0 = single no dependents) is Over.................But NOT Over............Then Subtract.........And multiply $0.00...............$195.00.....................$5 1.......................10% $195.00............$645.00.....................$99 .......................15% $645.00............$1,482.00..................$351 ......................20% $1,482.00..........$3,131.00..................$447 ......................25% $3,131.00..........$6,763.00..................$849 .....................30% If gross is $400 then what are the total taxes taken out? Could someone please provide a formula? I tried this one: =IF(M10
View Replies!
View Related
Unique Job #'s By Date VBA Solution
I'm looking for a VBA solution that will return a list of unqiue job numbers in B by date, the below is some sample data in Sheet 1 Sheet 2 is the result I'm looking for and usually there will be muliple job mumbers for the same date and in this case I'm fine to leave it as a blank cell in sheet 2 for that day. Unable to use advanved filtering as it's a data table thats changing all the time. It's verly likely that the same job numbers will be in multiple days....
View Replies!
View Related
Dimensional Table
i have a problem with my report. there is a dimensional table: DIM RESULTSDIM_111.1DIM_111.8DIM_111.2DIM_25.3DIM_25.5DIM_380.2DIM_380.1DIM_380.35 i need to make a new table in wich all "dim" will repeat only once and the results will be the average of the results that belongs to the same "dim" the times that the "dim" repeats can change and the "dims" could be not only till 3
View Replies!
View Related
Macro – Two Dimensional Look Up
The workbook has two sheets. Sheet1 has numbers in column A going down the sheet and dates in row 2 going across the sheet starting in column B. Sheet2 is similar but the column and rows do not line up with sheet1. I would like the macro to look at the numbers in column A and the dates in row 2 on sheet1 and find the same match of number and date in sheet2 and enter the value from sheet2 into the appropriate cell in sheet1. I understand it can be done in a formula with index and match but I would prefer a macro.
View Replies!
View Related
Dimensional Weight Formula
I have attached a sheet that contains a pretty simple formula that helps me figure out the dimensional weight of a shipment. It is just a simple vlookup that returns the proper rate (Column H) depending on DIM Weight and Zone. It works great. The issue I'm running into is with various exceptions that I need to put into the formula. The criterias that I need to put into the rate formula (Column H) are: 1-If the one of the Length (Column A), Width (Column B), and Height (Column C) is greater than 60 I need to add 7.50 to the the returned lookup value in Column H. 2-The second longest side the Length (Column A), Width (Column B), and Height (Column C) is greater than 30 I need to add 7.50 to the the returned lookup value in Column H. 3-If the grith (2x Column C + 2x Column B) is greater than 130 I need to add 45 to the the returned lookup value in Column H. I'm working with a few IF formulas but I can't seem to get all of them to work at the same time. Does anyone have any thoughts?
View Replies!
View Related
Bi-dimensional Dynamic Range
I've tried the below formula to get a bi-dimensional dynamic range that is defined by the last no blank column and last no blank row, too. =A1:INDEX(A1:D18,MAX(NOT(ISBLANK($A$1:$D$18))*ROW($A$1:$D$18)),MAX(NOT(ISBLANK($A$1:$D$18))*COLUMN($A$1:$D$18))) Does any one have a different approach to get this without using the offset function?
View Replies!
View Related
2-dimensional Date Range Criteria
Attached is a really simplified version of what I'm trying to accomplish. I'll attempt to get my brain working well enough to explain it: For each row, I have a person's name, their department, their site, and then a series of dates (representing dates of attendence). The four columns after the dates ("Counts" is the first) are me trying to keep all the right numbers in mind, and are the routes I've been trying to take on this. I have also tried to make a custom formula called ClassInRange, which isn't playing nicely. What I eventually need to populate is the little 2x3 section at the bottom where the three sites and the two departments I'm working with (down from 5 sites and 8 departments for the sake of sanity). What I need to be able to do is populate those cells with something that will tell me how many people from which site and department have attended something within the date range. So, for example, I need to know that the 2 guys in Wellesley attended something between January 1st and December 31st of 2007. (American date style, in case anyone's lost) If you're able to help, please keep in mind that I'm not the end-user; I'm just the slightly crazy dude trying to create this thing and make it simple to use. Eventually, the Dept/Site box will be part of a template which is copied and pasted up to four times on a report sheet, each fed by different date-range criteria. Also, the important information is the person's name, dept and site, which I hope gives some reason behind the organization; we want to find the person easily.
View Replies!
View Related
Double Lookup From Two Dimensional Table
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table. For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted.
View Replies!
View Related
2-dimensional Date Range With Dynamic Copy And Paste
Some of you may have seen my 2-dimensional Date Range Criteria thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note. The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating. I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent. What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question. The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the 2-dimensional Date Range Criteria of the report. What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.
View Replies!
View Related
Extending Previous 'conditional Statements Solution'
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution: http://www.excelforum.com/excel-gene...-new-post.html I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
View Replies!
View Related
Converting 3x10 Array To A 1X30 Array To Run A Match Formula
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1. Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows? Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet. And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column. I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
View Replies!
View Related
Filter An Array (the Longer One) Using The Shorter Array As The Criteria
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE") I then copy and paste 'values' and filter out the 'false' to get my final result. This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value. This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
View Replies!
View Related
|
TRACKER
