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 RepliesI'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
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
My attempt is in cell D2 on sheet1.
I have to match table column name with the column name in the sheets and get the values from column 6 in sheet2 if "# of values" in sheet1 matches with "Counter" of sheets for the same column.
if ("Table Column Name sheet1"="Column Name sheet2" then
if("Counter sheet2"="# of Values sheet1" then
return "Total # of rows Sheet2" from same row.
I am using Excel 2003.
Sheet 1 ....
I have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.
I need INDEX/MATCH to return the correct values for the subset below.
Column A (Forecast Status) - Column B (Account Name)
Commit - Account01 (Row 1)
Commit - Account02 (Row 2)
Won - Account03 (Row 3)
Won - Account04 (Row 4)
Won - Account05 (Row 5)
Upside - Account06 (Row 6)
Lost - Account07 (Row 7)
Upside - Account08 (Row 8)
Won - Account09 (Row 9)
Commit - Account10 (Row 10)
In a new tab, I am creating a 'dashboard' view that will group each of the following:
Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10)
Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9)
Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8)
Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)
I want to write a macro that match multiple column and return a value as I show in the table below
Plant
SLOC
Grade
Posting Date
1
555
abc
[Code] .....
I have to check multiple column from 2sources and get the Posting Date from Table2 to Table1. I'm quite confused about the function which I should use like match, lookup, index, etc.
I have a file with multiple column headers and data entries (dropdowns) into the rows. I wanted to create a secondary sheet...like a pivot table..if the vertical and the horizontal are both in the two columns selected it returns a value in the table.
I would use a pivot table, but it is a data entry sheet and the pivot table does not bring up all the horizontal and vertical values unless they have already been selected in the sheet.
I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!
I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status
plus a whole bunch of other columns of information.
On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.
I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.
Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.
I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.
I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.
I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.
Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.
I would like to be able to get a list of all values in a given column that match a specific name in another column.
Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.
For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.
The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.
CPT Sample Book.xlsx
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
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI 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)
My knowledge of Excel is quite basic, so i had little luck searching for this one.
We have a main data sheet in which everything is inputted, but i would like that data to be split up and reported (copied into) in 2 or 3 other sheets, depending on a single criteria.
Is this able to be done so these 'report' sheet are constantly updated, and the columns in the reports dont include irrelavant columns which are in the main data sheet.
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
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
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].....
I am designing a workout template i have a table with sets and reps and i would like to be able to select a heading and return all of these values to a specified box.
I have attached the file : excel help forum.xlsx
Does anyone know how to return multiple rows in an array with the same value?
i know that if i use ....
When I enter my formula on the same sheet the data is on, I get the answer I am looking for, but when I enter it on a separate sheet, I get #N/A
Here is the formula on the sheet that contains the data: (works perfect)
=INDEX(K1:M144,MATCH(H17&I17,K1:K144&L1:L144,0),3)
Here is the formula on the separate sheetreturns #N/A)
=INDEX(List!K1:M144,MATCH(D4&E4,List!K1:K144&List!L1:L144,0),3)
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula...
=INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
I have a workbook that has 5 worksheets. Worksheet1 is the Summary tab, Worksheets 2-5 contains data all sitting under the same headings and formats however vary in row number e.g.
Worksheet 2 - 63000 rows
Worksheet 3 - 48000 rows
Worksheet 4 - 23000 rows
Worksheet 5 - 21000 rows
In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.
=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI
[code]....
I have a excel workbook. which have a master data sheet or table as drawn.
[Code] ........
The above sheet is master sheet. I have also worksheets which named are matching with column head from "DARCL", "MMT", SSL"......till "GRT" (No "order" named worksheet is there). So I want to feed data or value as total order in "ORDER" column, (it is not necessary to put the value in each row) and the total value or number is distributed by me in particular column or colums. After data feeding I have required a command button or any button that can copy or show the reference row (customer name) with its cell value in the matched column head worksheet. As example: total order is 200 put in "ORDER" column in row 3, and 200 is distributed as by myself 100 in column "E" (MMT) and 100 in column "H" (RITC) and so on...after this feeding, I click the button and the data will show or copy as table M/S UIW : 100 in "MMT" worksheet and M/S UIW: 100 in "RITC" worksheet.
As example layout of others worksheets.(attach pic)
After click command button on the master sheet the related customer name and the value will be populated in respective matched column head with worksheet. If there is no cell value in master sheet the below mentioned cell are hides.
master sheet.jpg
others worksheets.jpg
I got a good start on what I need to do from this thread here: [URL] ......
A user will use a userform to enter in results from a room inspection into one sheet and then on another sheet selects the maid and it pull up the matching room inspections. I wish to then limit it to a date range which can be found in two cells.
Currently cells D2:H2 contain the array
[Code] ......
and cells D3:H3 contain
[Code] .........
I would like to further limit those searches by restricting the date range, Cells D4 and E4 contain the first of the month and last of the month respectively.
I would like to avoid the easy answer, start a new workbook each month, but I won't be the person entering the data or using the separate sheet to conduct performance reviews so it needs to be one workbook that lasts from month to month.
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.......