Conditional Array From Big Pile Of Data
Feb 26, 2007
I've got a big database dump from SAP. For simplicity, let's say it looks like:
Item# Supplier Model Description ItemCategory
1 A A-1 Something Tool
2 A A-24 Somethinel Tool
3 B R45 Bla Blah Kit
4 C N75 SoandSo Tool
etc. with thousands of entries.
I want to be able to manipulate members of this data set that have "Tool" in the item category alone.
Is there a way to build an array based on a conditional factor of this kind?
View 9 Replies
ADVERTISEMENT
Feb 21, 2009
I have a 125x125 array of numbers each cell of which is equal to the row header divided by the column header. Using conditional formatting in Excel2007 I can visually identify the integer values. However, for each integer value, I want to list in 3 columns the cell value, the row header value (or reference) and the column header value (or reference).
View 9 Replies
View Related
Apr 16, 2014
I have a challenge, which i have attached a spreadsheet. It's probably easier if you look at the spreadsheet, rather than me trying to explain. I've added some notes for reference.
I've tried to use an IF/vlookup function but I cant seem to make it work.
Attached File : ans.xlsx‎
View 9 Replies
View Related
Feb 8, 2014
I'm looking to use the percentile formula given a condition. The percentile formula as I understand it is percentile (array,k), where K is the percentile I want.
What I want to do is select a column for the percentile, but only do it for values above zero (I don't want to get into the full explanation as to why but I cannot cheat and just sort the columns).
The context is that I was doing some averageif calculations previously and want to remove outliers (top 5 and bottom 5% of data).
View 1 Replies
View Related
Nov 28, 2008
Attached is the file where I want to derive Min Date of seq A and asset Type Char.
SeqAsset TypeStDateEndDate
AChar21-11-200828-11-2008
BEnv22-11-200829-11-2008
CProp30-10-200830-11-2008
AChar03-11-200801-12-2008
AChar04-11-200801-12-2008
AChar05-11-200801-12-2008
AChar06-11-200801-12-2008
AChar07-11-200801-12-2008
I want to find the Minimum Date of Seq "A" and of Asset type "Char". I used following Array Formulas but showing the correct answer 30-10-2008MIN(IF(A2:A9="A",C2:C9="Char"),(D2:D9)) But Istead of 03-11-2008 it's showing 30-10-2008 date of seq C and of asset type Prop
View 2 Replies
View Related
Aug 21, 2013
I am trying to use conditional formating that will highlight any cell in the range M2:P9 are greater than 0 AND where M1:P1 (text) does NOT equal Q2:Q9 (text).
View 3 Replies
View Related
Nov 1, 2013
I need to sum several non-contiguous columns if certain other columns meet a certain criterion.
Details:
Need to sum columns Q,R,S,T,U,V,W,X,Y,Z,AC,AD,AI,AJ,AK,AL,AM,AN,AP,AQ,AR,AS,AT,AU,AV,AX,AY,AZ,BA,BC,BD,BE,BF,BH,BI,BK,BL,BM,BN,BQ,BR
in sheet "(1) 65539004 SAP"
IF
column D in sheet "(1) 65539004 SAP" = A2 in sheet "(4) Brand Breakout" AND column L in "(1) 65539004 SAP" = $B$1 in "(4) Brand Breakout"
The formula will be placed in C2 in "(4) Brand Breakout" and copied to other cells just FYI,
View 9 Replies
View Related
Jun 19, 2014
How to conditionally format an array formula? The basic formula is an index(match criteria1 and match criteria2. I have come across sites where they mention you can incorporate aggregate into the formula to remove the Alt+Ctrl+Shift thus allowing for the formula to work in conditional formatting. Shown below is the formula I am trying to use. Basically if the formula below = today() then be yellow.
{=IFERROR(INDEX(Table2[Comp Req Date],MATCH(1,([@[SLDR_EF]]=
Table2[Component Material])*([@[Grand Sequence]]=Table2[Order Seq Num]),0)),"")}
View 2 Replies
View Related
Feb 21, 2007
I have this :
ColumnRow
1 7
1 27
5 3
5 7
5 31
5 35
5 39
5 59
9 7
9 11
9 31
I need a formula to use with conditional formatting that "lights up" the corresponding cells. So, in this case cells A7, A27, E3... should "light up".
Is this possible, or should I use VB?
View 9 Replies
View Related
Jun 23, 2014
I have the following data; B1 and B2 are entered by the user and are named 'heat' and 'race' respectively. Conditional Array Formula Example.xlsx I'm after the ROW number that matches the minimum time (col C) for the heat and race combination shown - in the example below (heat: A, race: 100m), I'd like row '5' returned (or, better still, row '2' of the array A4:D11), as that's the lowest time for Heat A, 100m.
A
B
C
D
[Code].....
but this dosesn't exactly seem elegant, and doesn't work!
ie I simply want the row number (ideally relative to the range) for the lowest time for any given inputs 'Race' and 'Heat'.
View 3 Replies
View Related
Feb 25, 2009
This has been kicking my can all morning! Should be simple. I'm trying to conditionally format an array from the value of a single cell.
=IF(A1<80, A3:A24,RED,0)
=IF(A1>80,A3:A24,GREEN,0)
=IF(A1>120,A3:A24,BLUE,0)
View 2 Replies
View Related
Jul 16, 2009
Basically, I'm trying to alter the code by inserting another condition where if S12 = R than it will exclude the value found in D12, other wise D12 is included. It was initally setup with a range of $C12:$H12 but i had to write it out long to exclude $D12
Here's my ammended ...
View 9 Replies
View Related
Mar 28, 2014
I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
View 3 Replies
View Related
Jul 11, 2007
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8
What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want
=sumif(A84:A89;"x";'X-ref'!T4:Y4)
but it doesn't work since the first range is an column-array and the second range is an row-array.
View 5 Replies
View Related
Feb 28, 2014
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
View 7 Replies
View Related
Jun 30, 2014
Excel file. The file has two tabs: 'Input' and 'Master'. The 'Input' tab is for users to input any new records, and the 'Master' tab is to retrieve data from the 'Input' tab. Given that some users may want to insert a row in between (rather than add at the bottom).
I used arrays in the 'Master' tab such as:
={IF(ISERROR(1/Input!E2:E250),"",Input!E2:E250)}
Now there came the problem: in the 'Master' tab, the data cannot be sorted when the arrays are used. Otherwise, there is a warning message: You cannot change part of an array.
View 3 Replies
View Related
Aug 5, 2012
I need to process weather data that is collected either hourly or daily for several weeks - so for example I'd have Column A showing hourly intervals from 00.00 to 24.00 with Column B showing rainfall amount.
I need to output a single total for each day, say in Column C. I'm thinking I need to specify an array of 24 values from Column B, with a loop to repeat through until it runs out of data ?
I've started to piece together a subroutine but I need a grown-up to debug it ;
'Option Explicit
'routine to sum monthly data and output
Option Base 1
Sub monthdatasum()
[Code].....
View 6 Replies
View Related
Mar 2, 2013
In a userform I have 2 combobox, a textbox and a checkbox. In Checbox1 have data in A2: A550 and checkbox2 have data in B1: B50. I want the number entered in textbox1 to be written - by pressing the SAVE button - to the intersection data of combobox 1 and 2 and once written to can not be rewritten. Therefore, if you enter a wrong number, it will check that checkbox and where to enter the cell number, to be colored in red.
View 6 Replies
View Related
Mar 4, 2013
I have a column a1:A150 which includes some Data. I now want to declare these data as an array.
Afterwards I want from cell b50 to copy int the value from cell a1 and copying the next value from the array (cell a2) into cell b51 and so on until cell b200.
I want to do it by creating an array and not just by usual Excel formulas.
View 3 Replies
View Related
Aug 31, 2013
I have ten customers who have multiple locations where we do work. On ourinvoice I want to use Data Validation (list option) to read the input "CustomerName", and then change the validation list in the "Job Reference" cell accordingly. For example if A1 is the customer name and A2is the "Reference" where the data validation list will be. Customer ABC has 3 locations, XYZ has 5, CompanyD has 4, and so on. The range for thevalidation array is C1:C5. ABC's list oflocations is located at A10:A15, that of XYZ at A16:A20, and Company D atA21:A25 (and so on, for the others). Howcan I load the data validation range (C1:C5) with the correct list from, forexample A16:20 based on A1 being customer XYZ?
View 1 Replies
View Related
Jul 14, 2014
I have two columns of data of which I need to transpose into rows. Sounds easy enough but the data looks like the following:
Business Role
Person
Accounts Payable Voucher Reviewer
SMITH,JOHN
[Code].....
I have read some sites talking about index and column or using VBA.
View 6 Replies
View Related
Mar 8, 2007
Is there a way to find data in an array similar to the way match, index or vlookup is used for data in cells of the spreadsheet?
Example, if I have a 2 dimensional array:
1 apple
2 orange
3 pear
4 banana
5 grape
and I want to know if the word "banana" exists in the second column of the array, is there code that would determine if it was in there at all, and is there code that would determine that it was item 4 in that array?
View 9 Replies
View Related
Jan 18, 2008
In attempting to answer another post, I came up with this code to try to test whether data in columns "p" through "w" matched data in columns "a" through "h".
The problem is, it's acting as though everything is a match, and in my test sheet, I have some data that matches, and some that does not match.
It just highlites the whole range.
Here's the sample data:
******** ******************** ************************************************************************>Microsoft Excel - Book2a.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGHIJKLMNOPQRSTUVW1abcdefgh pqrstuvw2abcdefgh pqrstuvw3abcdefgh abcdefgh4abcdefgh pqrstuvw5abcdefgh pqrstuvw6abcdefgh pqrstuvw7abcdefgh abcdefgh8abcdefgh pqrstuvw9abcdefgh abcdefghSheet3 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
And here's the
Sub deldupes()****'if data in p through w matches data in a through h, delete p through w****Dim ah()****Dim pw()****Dim arrcount As Integer****Dim cell1 As Range, cell2 As Range****Dim DataCount As Integer****Dim Counter As Integer****'change the sheet name as necessary.****With Worksheets("Sheet3")********DataCount = .Range("A" & Rows.Count).End(xlUp).Row********ReDim ah(1 To DataCount)********For Each cell1 In .Range("A1:A" & DataCount)************If IsEmpty(cell1) Then GoTo nextcell1************For Counter = 1 To 8****************x = x & .Cells(cell1.Row, Counter)************Next Counter************arrcount = arrcount + 1************ah(arrcount) = xnextcell1:********Next cell1********DataCount = .Range("P" & Rows.Count).End(xlUp).Row********For Each cell2 In .Range("P1:P" & DataCount)************If IsEmpty(cell2) Then GoTo nextcell2************For Counter = 16 To 23****************x = x & .Cells(cell2.Row, Counter)************Next Counter************On Error Resume Next************y = WorksheetFunction.Match(x, ah, 0)************If y = "" Then****************With .Range("P" & cell2.Row & ":W" & cell2.Row).Interior********************.ColorIndex = 6********************.Pattern = xlSolid****************End With************End Ifnextcell2:********Next cell2****End WithEnd Sub
I realize that:
If y = "" Then
should be:
If y "" Then
View 9 Replies
View Related
Apr 21, 2006
i have used the database from j & r solutions. i have altered it slightly to suit me. the database works fine apart from when using the find all button it will only return 4 entries. if there are more than 4 entries it returns runtime error 9.i have zipped up the code and marked where the error is shown when i debug
View 2 Replies
View Related
May 19, 2006
I'm trying to create a data entry form that uses comboboxes. I can populate the comboboxes with data from an excell sheet. There are 3 columns of data that are called to each combobox, and the list of data is the same in each combobox. When the chosen line is selected for each combobox I then want to pass all that data back to the excel sheet in the same column layout. I suspect this will need to be done with an array.
Basically, I want to have five tasks with timing details that are done from Monday to Friday - these are listed in excel. I want each of these tasks to appear in each combo box (two columns in the combobox) so that the user can decide which task will be done on which day. Then the tasks are passed back to excel as a timetable. If it were 1 column I could do it but the second column is causing me grief. I suspect this will need to be done with an array.
View 9 Replies
View Related
Mar 15, 2014
I am trying to pick certain data from a table and sum it in order to produce a formatted report.
The data has many different items of which I want to pick certain ones to sum on one line
See attached and some of the formula's that I have used, but aren't quite what I'm after
The formula I want to use is .... VLOOKUP(E5:G5,A14:C23,3,) ......
But it wont reference a range (E5:G5) !!! and I also want the formula to ignore blanks (as blanks in the data file are actual totals).
View 2 Replies
View Related
Oct 17, 2009
I've encountered is the spreadsheet attached. Basically I am trying to create a "complex" array (at least it's complex for me lol) to basically extract some values from tables and put them in one together. The array I thought would work is on the table with BRIGHT YELLOW BACKGROUND, whereas the table that could do the same work but more "manually" is with a RED border around it. I know I can do it manually as the red border table shows, but I've been trying all day to make it work with a complex array.
View 4 Replies
View Related
Nov 9, 2009
I want to extract data from array string and then sum the values. For reference attaching the excel.
View 14 Replies
View Related
Mar 16, 2014
I have text in an array (there are formula in all the fields) and I would like to arrange this text so that it is in a list format.
View 1 Replies
View Related
Jul 5, 2014
I've built a simple inventory tracking system, and decided a reporting feature would be nice. There are four categories that are entered when inventory is removed...Date, Employee Name, Item Description, Location, and quantity.
Four my reporting purposes I'm only concerned with Date, Employee name and Item Description.
I've been able to write code that does what I want using a multiple Cases and a For loop once the case is identified. However, the more data there is the longer this takes...so I decided to stretch myself and try my hand at arrays (first time really working with arrays), but I'm having trouble figuring out exactly what I need to do.
Here is what I think the steps need to be.
1. Store my data (the categories above) which are located in the Check Out sheet
2. Go through the arrayed data to find exact matches based on my search criteria (here is where the Cases come in)
3. Pull out only that data and write the information to a "Report" Sheet
4. Export that sheet to PDF (this part I already have)
Below is a copy of what my current "working" code looks like (I should mention that the search selections are made from a userform this is what the Cases are deciphering between which ones are blank etc...), also most of my variables are instantiated as Public variables within a Public_Variables module also below.
[Code] ....
And the Public Variables...
[Code] ....
View 4 Replies
View Related
