Conditional INDEX MATCH - To Extract Companies From Orders Sheet
Apr 10, 2014
Attached spreadsheet.
Basically I have 2 sheets. The first an orders sheet which shows all orders from companies with the date of the order, whether it is a repeat order, and whether it has been delivered. The second sheet a customer account sheet which shows the order history by customer. I have used the following formula :
=IFERROR(INDEX(Orders!$B$2:$B$23,MATCH(0,INDEX(COUNTIF($A$1:A1,Orders!$B$2:$B$23),,),)),"")
To extract the companies from the orders sheet however if a company has had no previous order (i.e. a new customer) and has not recieved a delivery yet I do not want him added to the sheet.
I have tried amending my formula in different ways but always encounter problems.
View 3 Replies
ADVERTISEMENT
Dec 2, 2009
sheet 2
column C is list of chemicals (to give context)
column A is a list of the publication numbers they apear in
column B is GOING to be the list of publication titles they are in
sheet 3
column A is a complete list of every publication number in our library
column B is the corresponding publication title
what i would like to do is extract the title from sheet 3 and input it into column B sheet 2 corresponding to whatever pub number is listed in column A of sheet 2
the code i have so far is in column b sheet 2 "=INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0))"
now the problem i have is this works perfectly when only one pub is listed in a cell in column A sheet 2 but when there is more than one pub listed (ie "pub number" next line in cell "another pub number") it gives a N/A result.
is there anyway to call up multiple pub titles from the multiple pub numbers within one cell
View 5 Replies
View Related
Jan 20, 2004
Columns F looks at another table within the same worksheet for the Customer Code that has the MOST Orders in Column B.....then places the Tot Orders amount in F2..... Cust Codes in Column E and the Cum Values into Col G ( that's already been accumulated and housed in column C ).
Cell F3 then go looks up the next largest order in Column B and pulls over the adjacent Customer Code & Cum Value to the E,F,G table again.
Basically there's 3 functions:
1 in E2
1 in F2
1 in G2
All to copy downward within that table
******** ******************** ************************************************************************>Microsoft Excel - Book4___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFG1CustomerTot*OrdersCum*Value.CustomerTot*OrdersCum*Value2Cust*682$943.Cust*682$9433Cust*973$787.Cust*973$7874Cust*1055$525.Cust*1055$5255Cust*840$438.Cust*840$4386Cust*125$150.Cust*125$1507Cust*27$300....8Cust*55$180....9Cust*43$75....10Cust*31$25....11Cust*71$12....Sheet1*
[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jul 9, 2007
1. Pupils have a target grade
2. Pupils can either do a higher or foundation Tier exam depending on their ability
3. Pupils do 9 modules and the grade bounderies are different for each module.
3. I use bar charts to report the results back to pupils and parents. Instead of just telling them you got 50% D your Target is C. I also want the target grade to be converted to a mark.
I do not want to create another woorksheet inorder to use Vlookup.
4. I now know that Index and Match can do the trick of reading the left columns. But I cannot get them to first consider the Tier for each student.
I was thinking of usind IF
IF Tier is Higher then Index the Higher Table and Match else Index Foundation Table and Match to get the mark. But I do not know how to express this in a way that's readable by Excel
View 10 Replies
View Related
Feb 26, 2010
I have a table I use visually, and I put it in a spreadsheet. Instead of finding a table value based on row and column label decisions, I use it as follows. I decide the row by matching exactly the height, h = 8 ft. In that row, I match the next larger value of 10 kips, in this case the value is 12.72; I then pick the column header label, in this case the value is 4 x 8.
I don't know how to make"B8:L8" equivalent to "B"&3+row : "L"&3+row in the 2nd Match formula?
View 4 Replies
View Related
Oct 3, 2012
I've got data in two columns (J and G). Column J contains product manufactured by companies in column G. Some products in column J are manufactured by more than one company in column G. I'm trying to create a conditional format to highlight products made by multiple companies.
My thought is that I need a formula that asks if the product is duplicated, AND the company is different, but I'm not sure how to do that.
View 9 Replies
View Related
Apr 11, 2009
This had been hunting me for weeks and I still could not come over it after weeks of study the conditional formating IF, match, index, Vlookup, etc to find several duplicated value, e.g Column B = Name 1 & Row B = Time and so on, where as Name will have data of Name 1 duplicated in different time, while row B, "Time"would change to a different Name and maybe it would go back to Name one.
My problem was dont know which formula I can use in a different sheet to bring back the data to Name 1 did Task A at Time A, while Name 1 did Task B at Time K, etc. If this is not Name 1, then Name B replace above.
View 4 Replies
View Related
Apr 16, 2009
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
View 6 Replies
View Related
Jan 11, 2007
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
View 2 Replies
View Related
Aug 13, 2012
I work for a manufacturing plant where we manufacture one product which is then sold under different names.
The product is made from the same material.
I am looking for a way to match up production to the orders and then subtract the produced quantity from the orders.
Tab 1 - Orders: Column 1 has the items (A1, A2 and A3 are all produced from A), Column 2 has the date when the order has to ship and Column 3 has the ordered volume.
Tab 2 - Production: Column 1 has the raw material, Column 2 has the production date and Column 3 has the amount manufactured.
Tab -1: Orders
Item
Ship Date
Ordered Qty, KG
A-1
8/22/2012
16
[Code] ..........
Tab -2: Production
Item
Production Date
Quantity
A
8/13/2012
140
[Code] ........
View 4 Replies
View Related
Feb 22, 2014
I am looking for some formula index/match with sheet "source" as input data and sheet "output" as report...
How can do it??
See workbook attached : lookup another sheet_john.xlsx
View 5 Replies
View Related
Feb 16, 2013
I have table as below of raw data. In which data will increase dynamically both for rows and columns from starting column for eg say Column "D".
I want to get ID data on sheet2 to compare it with some other data for the ID by using formula = INDEX(Dynamic range,MATCH(ID Ref- Sheet2,Sheet1! ref -dynamic,0),column ref) and get the referred ID data on "Sheet2").
On Sheet2
ID
Data1
Data2
Data3
Data4
Data5
Data6
Data7
[code].....
View 1 Replies
View Related
Dec 14, 2013
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)
View 5 Replies
View Related
Jun 10, 2013
I have a built a catalogue of records for various of my companies assets. Each asset has its own sheet in a work book and I have used a vb macro to include an automatic Index sheet at the front of the book. This takes the names of the various tabs, in the order I have them in the workbook and creates a simple listing in column A as an Index List
What I want to do is to build a summary table to the right of the Index List, pulling in a few of the key data points about the assets into a single table.
To do that I need to be able to extract the text string from the Index List and use it to form part of a cell reference pointing to a specific cell in the relevant individual Asset sheets.
View 4 Replies
View Related
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 Replies
View Related
Apr 3, 2014
I have 2 sheets in a file. One is given to me from our parts department containing all parts on a given truck model. The 2nd sheet is exported as a database file from inventor and imported as an update able sheet in excel.
I am needed to compare part numbers between the 2 sheets and the qty of each. So far I have it where it matches the part numbers and turns the ones I don't have in the model red. Now I'm trying to match the part numbers and pull the qty of said part from sheet 2, the imported one, and compare it to the to the qty in the 1st. All I really need to accomplish is matching the part numbers then copying the qty from sheet 2 to sheet 1 onto a new cell of the matched part number.
View 3 Replies
View Related
Aug 5, 2014
I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.
In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.
View 6 Replies
View Related
Jan 26, 2010
I am usuing INDEX and MATCH to pull back employees hours from a master sheet, I am asking excel to Index, matiching firstly the agents name and then the day of the week (Formatted: Mon,Tue,Wed,Thurs,Fri,Sat,Sun). This works perfectly for every day of the week using the following formuls except for Sun where it returns #REF!
=(INDEX(Hours!$B$4:$G$166,MATCH($F$1,Hours!$A$4:$A$166,0),MATCH(C7,Hours!$B$3:$H$3,0)))
View 2 Replies
View Related
Jan 8, 2013
i want the compile all information from few sheet (20) with same format on daily basis based on due and CF status cash flow. the format as below as the result will same as original format,
No.
Receive Date
PRF No.
Frank No
[Code].....
View 1 Replies
View Related
Jan 19, 2014
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?
View 7 Replies
View Related
Apr 7, 2007
I have been trying to create a macro button on my new orders page for my spreadsheet.
What I am hoping to achieve is a marco that when I click place it, it will take the information needed from the new orders page and paste it into the existing orders, but it does not end there the tricky bit im finding is how to get it to go to my stock spreadsheet and whatever part was ordered it would lower the stock by the quantity that was ordered so in effect the spreadsheet would lower my stock level as a items were purchased. I have attached the spreadsheet for you all to have a look at. I only have very basic VB skill but eager to learn. If anyone can solve this for me I defiantly owe them a pint
View 3 Replies
View Related
Nov 18, 2013
I download a daily inventory file from my supplier.
.
I sell only selected products from that file.
.
I would like to use a list of the product part numbers of the products I sell on sheet 2 (Inventory) and have all the results from sheet 1 (ProductFeed) built into an expandable table on sheet two including all the parts numbers (expandable because I will add or delete part numbers from the list) I have listed and not just one part number but several.
.
Second issue: the download file contains part numbers in column "A" with variables like 418, 418_color, 418_size etc.... I would like all part numbers that begin with the number 418 and the ones that contain the variable added to be listed in the new table so I will get a list of all part number 418 and the variables which include 418 in them.
.
I want to return the results of the whole row(s) of the matched data as well in the new table.
.
I have attached a file of the working data that I have with the data I will use although the ProductFeed will change daily which I intend to copy and paste into the table each day.
View 2 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
May 23, 2014
We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
View 5 Replies
View Related
Jan 21, 2014
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?
View 3 Replies
View Related
Feb 4, 2014
I have two sheets of data , sheet A and Sheet B.
Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.
I have filled out the first two rows of results that should be returned as an example.
One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.
View 3 Replies
View Related
Aug 28, 2012
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
View 5 Replies
View Related
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
Jun 10, 2009
Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.
View 10 Replies
View Related
Jan 15, 2013
I couldn't resolve the formula of Index-Match for getting the values that matches the following criteria
1) Match the "Category" of Table 1 to Table 2
2) Match the "value" of Table 1 to Table 2, if value is not present in Table 2 take the upper closest value.
Once the above conditions are met, Take the Result from Table 2
Table 1 is for Input and Table 2 is for Database.
Please note that Data in Table 2 should not be sorted in any way. I tried to use the -1 option of the Match function for upper closest value but it didn't work out.
Have a look at the file attached : IndexMatchUpperClosestValue_AlongwithExactMatch.xlsx
View 3 Replies
View Related
