Sum Multiple Columns And Rows Using Offset And Match
Mar 25, 2013
Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.
I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.
=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum
FYI:
PnL!$c$9 = is the block referred to as C9 below
$A29 contains 107000
PnL$A$10 is the block below A9 below
ControlMonth is currently set to 3
[code].....
View 5 Replies
ADVERTISEMENT
Oct 29, 2008
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
View 9 Replies
View Related
May 22, 2014
ertret.jpg
I am trying to delete rows that contain the same addresses BASED ON COLUMNS.
Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.
I want to keep the ones that DO NOT MATCH.
P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH
View 1 Replies
View Related
Jul 10, 2008
I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......
View 9 Replies
View Related
Nov 11, 2013
Attached is a sample file.
I can't figure out a formula that will match either 1,2,or0 in column K and offset to corrosponding value in column B. Then average of all values that came up.
how to accomplish this. VBA is acceptable, but formula is prefered.
0.644
0.627
0.641
[Code]....
View 5 Replies
View Related
Jan 15, 2010
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 Replies
View Related
Sep 6, 2007
need a quick macro to match rows in two columns as per demonstartion below.
View 9 Replies
View Related
Aug 6, 2008
I am trying to use the formula below
=INDEX($M$4:$AG$75,MATCH($D$3,$K$4:$K$75,0),MATCH(A6,$L$4:$AG$75,0))
The last MATCH function is where it fails, because the value I am looking at (A6) could be in one of 5 different columns between L and AG
View 9 Replies
View Related
Jun 11, 2014
I am trying to work on a index match but can't seem to make it work.
My look up value are on column while the data I want to show and look up array are on rows and still getting 0 results.
Is there any solution ofr this to make it work without altering my look up value & arrays to columns as well?
View 4 Replies
View Related
Feb 19, 2008
i would like to find code for a macro(s) to delete selected columns and delete row by partial string..
due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..
View 8 Replies
View Related
Feb 9, 2012
I have data like this:
Code:
500a
TRUE470b
440c
TRUE410d
TRUE380e
350f
[Code]...
and I want to look up an approximate value in Col2, say 310, and return it's next-highest friend from Col3, in this case "g". This is easy enough with INDEX and MATCH. But I want to take it one step further and only use those values which are approved by Col1 - so in this case I want 310 to ACTUALLY return "e"
I found some good information here which gets me close. The following works very well. It uses a boolean & operator to match two values at once, but it only works for exact matches. This example goes down the list and finds the first "sydney" which has an "x" and gives the result "h".
Code:
xmelbournea
sydneyb
xadelaidec
xmelbourned
sydneye
[Code]....
When I use this approach on data like in my first example it falls over, my guess is because the boolean & falls down before the MATCH function has a chance to accept an approximate match.
View 5 Replies
View Related
Dec 6, 2007
I wish to add more criteria to the "Find Feature to Find 3 Matching Criteria in 3 Columns in Excel" (http://www.ozgrid.com/VBA/advanced-find.htm) up to 7 criteria if possible. How can I do this? I wish to display the results in a dialog box or in a different sheet. I also want to run the find feature from a different worksheet, perhaps using a button instead of having to make a selection in the table.
View 2 Replies
View Related
Feb 20, 2008
I'm using an index array formula I learned here to lookup a value between 2 sheets in a workbook. I have had no trouble using it when matching just 2 pieces of information (first & last name), but now I need to make 3+ matches to get the value & the formula is no longer working.
When I check "show calculation steps" in the attached example, I can actually see that all 4 items have successfully matched across the 2 sheets but for some reason the formula is still not pulling in the desired value. Am I wrong to think that I can match more than 2 items w/ this kind of a formula?
View 3 Replies
View Related
Aug 21, 2013
In an Excel sheet; I am trying to match and align data in column C to data in column A, but it is essential that the data contained in the entire row (coln B thru to coln W) moves when Column C is matched and aligned with column A.
Sample workbook attached.
View 9 Replies
View Related
Nov 12, 2006
I would like to search across a number of rows in a worksheet and then match against two columns.
e.g.
Column B Column C
test 10
I would like the function to match against all rows that match against "test" in Column B and then work out which cell in Column C has the highest value
View 9 Replies
View Related
Jan 20, 2009
I would like to match column data in a source spreadsheet to column data in a target sheet. If a match is found, I would like to copy the corresponding row range from the source sheet to a separate, third sheet. For values where no match in found in the a target sheet, I would color the unmatched cell in the target sheet red. If a match was found, the cells would be colored green. The data in the Source sheet is in column A, while the Data in the Target sheet is in Column T. The data will be pased in the third sheet in Column T preserving original formats
I have this code, gleaned from several postings on this forum that somewhat works. The problem is that I get false mismatches (i.e. some cells get colored red even when there is a match and the data got copied to the third sheet) even though there are no duplicates. I have made sure that the formats are identical in both Target and Source sheets to try to fix this. Also, I don't want to cut the entire row , but just copy and paste a row range onto a third sheet. The column and row ranges are variable. I am attaching a file.!!
Sub CutRows()
Dim i As Long, k As Long, n As Variant, r As Range
Application. ScreenUpdating = False
With Sheets("Source")
Set r = Range(.Cells(1, 9), .Cells(65536, 6).End(xlUp))
End With
k = 0
i = 6
While Not IsEmpty(Sheets("Target").Cells(i, 20))
n = Application.Match(Sheets("Target").Cells(i, 20).Value, r, 0)
If IsNumeric(n) Then
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 35
k = k + 1
Sheets("Source").Rows(n).Cut Sheets("Sheet3").Rows(k)
Else
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 3
End If
i = i + 1
Wend
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
View 7 Replies
View Related
Apr 14, 2009
I am using Excel 2007 and I need to search for data patterns that are spread across many columns. I am not sure how to search so that criteria is met across all the columns concurrently.
For example
I am interested in data that matches the following criteria ;
Column B has the value 55
Column C has the value 70
Column D has the value 80
Rows of interest are when all the criteria in columns B,C,D are met at the same time (e.g 55 in B, 70 in C and 80 in D).
View 9 Replies
View Related
Mar 13, 2014
I've got a problem where I am trying to return a value if two criteria from one table match two from another.
I have included a example of my issue.
=INDEX(Time!D:D,MATCH(Data!A2,Time!A:A,0)*AND(MATCH(Data!C2,Time!C:C,0)))
I have had a look around lots of forums but cant get my head around what is wrong with my formula
View 4 Replies
View Related
Jun 1, 2007
I am trying to find min value for a row (product price) within two columns (price lists) and return row one (company name) in third column ( see the attachmet). My first idea was to use “min” and “if” but a problem occur when there are text or zero or none values in the price list. I tried with conditional formatting, offset function, match-index and other methods without success
View 4 Replies
View Related
Dec 30, 2013
I need to Index & Match the Max Value for 3 separate columns labeled "Price". The columns are not adjacent and cannot be moved. I am able to get the Max for the 3 Price columns using:
=MAX($L$12:$L$45,$O$12:$O$45,$R$12:$R$45).
However, I need the matching value in column D labeled "Int. SF". I can only do this for one column at a time so far using this formula
=INDEX($D$12:$D$45,MATCH(MAX($L$12:$L$45),$L$12:$L$45,0)).
When I try to use all 3 columns with Index & Match
=INDEX($D$12:$D$45,MATCH(MAX($L$12:$L$45,$O$12:$O$45,$R$12:$R$45),$L$12:$L$45&$O$12:$O$45&$R$12:$R$45,0)), #VALUE! is the answer.
If I enter it as an array, the answer is #N/A. I tried making the 3 columns into a table (which I named Price1) and tried the formula again
=INDEX($D$12:$D$45,MATCH(MAX(Price1),Price1,0)),
But that did not work either.
I tried using a V-Lookup, but can only get results for the first column, not the other two. I've attached my spreadsheet.
View 6 Replies
View Related
Mar 17, 2009
Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.
I tried using sumproduct but It wont give me anything but error messages or a zero.
Looks like this
=SUMPRODUCT(A:A="Stewart",(G:G="Fiduciary"),C:C)
Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C
View 9 Replies
View Related
Nov 15, 2012
Any formula that can look at the data in multiple columns and check for a match - returning a value such as YES or NO. Below is an example of my sheet.
A
B
C
D
E
F
G
1
Name
Number
Name
Number
Name
Number
Match?
[Code] ..........
Basically the formula would be in Column G and would look at B, D, & F to see if they match or not. In the example above - row two - they all match. So a True, 1, or Text option such as YES would be great. Same as row 3 only you can see they are different.
View 5 Replies
View Related
Jun 14, 2007
I cant get this one off the ground. Normally I am pretty good to find bits and pieces of advice from other posts and put something together, but this one has me stuck from the get-go. I want to create several lists based upon the row(s) containing at least one set of criteria (day and time, contained in two differnt columns). Each row allows for up to 3 sets of days and times (i.e. columns B and C, columns D and E, and columns F and G) Each row is date and time stamped as to when the data was entered in that row (column H).
I want one list for each set of the various day/time combinations allowed in columns B thru G. The list should provide the name(s) contained in column A, in chronological order based on column H, when a criteria match is made in columns B:C, D:E, or F:G. This is far easier to understand with the attached sample. The lower section of the sample represents my desired output that I cannot seem to achieve.
View 3 Replies
View Related
Mar 9, 2008
I have 4 colums of customer input data, Length, Width, Height and Quantity. There are over 20 rows to accomodate multiple combinations. I have several other hidden pricing pages with all possible combinations including materials, pricing etc. Assuming I am on the frst pricing page and first combination I need a formula that will match the first 3 inputs per row and if true give me the 4rth input for the matching row.
Example:
Customer input-
row 1 = 8 - 12 - 4 - 15
row 2 = 4 - 4 - 2 - 25
etc.
Order Page-
Combination 1 = 4x4x2 quantity___ (input row 2 matches 4-4-2 give quantity 25)
Combination 2 = 4x8x2 quantity___ (no input row matches 4-8-2 give quantity 0)
Combination 36 = 8x12x4 quantity___ (input row 1 matches 8-12-4 give quantity 15)
View 3 Replies
View Related
May 13, 2014
I have a large data set which is basically just transactions on accounts by date and I want to return the transactions in date order by account number. I've attached an example where sheet 2 is the transaction listing and sheet 1 is what I'd like to return.
View 3 Replies
View Related
Oct 26, 2011
I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.
=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))
Code reflects the ALL GRADES data below.
TERM SUBJECT NUMBER CRN CREDITS GRADE ID
1 Biology 301 12345 4 B 123456
2 Biology 302 23456 4 B 123456
3 Biology 303 34567 4 A 123456
3 Biology 338 65432 4 C+ 234567
3 Biology 338 54321 4 A 345678
1 Biology 336 43210 5 B+ 456789
1 Biology 337 43210 2 A 456789
1 Biology 338 65432 4 B+ 456789
1 Biology 301 12345 4 A 567890
2 Biology 302 23456 4 A 567890
3 Biology 303 34567 4 A 567890
Row Labels Min of TERM Count of GRADE First grade All grades
123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!
345678 3 1 A A #NUM!
456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11
* Need all grades where TERM = Min of Term
View 7 Replies
View Related
Feb 23, 2012
I have a table where I want to match data to a reference. The reference appears in more than one row in the first column and I want to find out which row numbers that reference appears in. However, using MATCH I can only find the first time that reference appears. Is there another way of finding at least the next row that the reference appeared in?
E.g. in the table below, Match always returns row 2 when searching for ID 123. Can I set up the formula so that it tells me where the next match appears, working downwards??
IDColour 1Colour 2matchdesired result123greenorange23123greyspring green24123purpleblue2#N/A144blueturquoise56144pinksaphire5#N/A180redyellow7#N/A129yelloworange8#N/A
View 1 Replies
View Related
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
Sep 26, 2013
I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
View 2 Replies
View Related
Dec 18, 2006
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View 9 Replies
View Related
