I have a worksheet which Sorted in ColA contains Product Id#, ColB contains Vendor, ColC contains Grade# (1 thru 6). Now there may be multiple Vendors (ColB) for same Product # (ColA) but with different Grade# ColC).
I would hope if you can please help me formulate for ColD to pull (list) ALL Vendors for the same Product# (there maybe as many as 6) PROVIDING that the Grade# (ColC) is either 3 or 4.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I have been trying to auto update a table by looking up information from a table on top. However, for the top table there are multiple entries in the same column, and my look up only checks the first row and ignores the rest.
My formula at this point is: =IF(ISERROR(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))=TRUE, "", IF(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4)=0, "", INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))).
If you can take a look at my attachment, this formula is in the cells I40:W40. Eventually, I will want to populate the entire bottom table with the same formula.
I'm trying to get a result of all rows in a table that has the same lookup value.
For example:
David25
Brian40
Steve57
David68
David902
John11
Gerry3
I want to look for David and that the result will be all rows that starts with David:
David 25
David 68
David 902
I tried using vlookup but it always returns the first row.
I'm working on a database right now for our school, similar to a directory. What I want to do is be able to look up what items a staff member has been issued and when/if they were returned. What I have right now is one sheet with all the information about the staff that looks like the one below. I want to create a separate sheet where you could look up a staff member by last name and can find all the information in the array relative the the specific cell looked up.
Last Name
First Name
Position
Building
Items
So, on the "lookup" sheet, I want to be able to input either Doe and have the look up return cells B2:E6, or if I input Smith it should return cells B7:D11.
I've been experimenting with Vlookup, hlookup, index, and match but haven't been able to get exactly what I want..
At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss
I'm having difficulty finding a way to ask excel to lookup a value based on an array spanning multiple rows and columns. There's a mock example in the attached.
View 2 Replies View RelatedI 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 RelatedCertification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
I am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".
=INDEX($A$2:$C$9999,SMALL(IF($A$2:$A$9999=$E2,ROW($A$2:$A$9999)-1,"hh"),COLUMNS($G2:G2)),2)
A
B
C
D
E
F
G
H
I
J
K
L
1
name
score
color
[Code] ..........
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies View RelatedAfter going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method.
=IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0)))
It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails.
I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet.
This is how I have modified the formula to suit me..
=INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0))
<<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>
Running Excel 2007, I have attached an example of the spreadsheet I'm working on. The actual spreadsheet contains many more sheets and thousands of rows, with several more locations, but the concept is the same.
Challenge 1:
I want to search Computer Name (column C) for names starting with AKL, WEL or WLG and then copy those rows to the NZ worksheet (leaving the original worksheet rows intact).
Then for names starting with DEN, copy rows to the USA sheet.
Then similar for other names...
I have been exhausting myself by experimenting with all sorts of nested INDEX, MATCH and VLOOKUP formulas, but I'm not up to the task and suspect a vb macro is the best or only way to do this..?
I found this post but I haven't been able to adapt it to work in my sheet... probably just my inexperience... http://www.excelforum.com/excel-prog...ell-value.html
Challenge 2:
The next time the IT dept gives me an updated asset report, I will copy all the relevant data into this 'All Data' sheet, replacing the old data that was there - can the NZ and USA sheets dynamically adjust, or do I have to delete what's there to guarantee I won't accumulate old data? I guess I could use a blank 'template' file each time, if this is easier than building the function into vb...
Once the data is organised in this way, I intend to create a summary sheet that will show a count of assets in each location, probably with a stacked chart to pretty it up.
The table i'm looking up contains 2 rows with a value which matches my lookup value, how do i get the vlookup to pick up both values?
this is what i have:
ACEUSA-0017,730.84
SISTRUINSU-0018,865.69
WARNORAMER-00212,868.59
ACEUSA-00113,948.61
This is what vlookup picks up:
ACEUSA-001 7,730.84
ACEUSA-001 7,730.84
using this formula:
IF(ISNA(VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE)),0,VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE))
This is what i need it to pick up
ACEUSA-001 7,730.84
ACEUSA-001 13,948.61
I do need ISNA to take care of #N/A
My daily receiving list has Item# received that day e.g.
ReceiptList.xls
Item#
123
456
I need to compare this to a Data Base list that has 10000 item# , seller, and status (this data base can have repeated same item# but varied seller and therefore different status
e.g.
Ing.xls (Data Base file)
Item# Seller Status
123 ABC Approved
123 GMC Not Approved
456 ABC Pending
456 XYZ Not Approved
456 QRS Approved
etc. +10000 items
Now I need to compare the daily receipts to the data base file and pull all the sellers for those particular items and their status which like I said could be on many rows, one row, or not listed at all to the ReceiptList.xls(which I can then make sure that the received items are from an Approved source etc.)
I have a worksheet of data that I am trying to reference to. I will have the data as a "dump" on one worksheet, and lookup from that sheet. I've attached an example of the data dump. I need to essentially VLOOKUP the value (i.e. 054_6425) and then count over 3 columns, and down two rows. I've tried INDEX MATCH, but can't get that to work. Can you get VLOOKUP to count over and down and return a value? File "example" is attached.
View 2 Replies View RelatedData to appear on one of my sheets.
I have a database of family info, where each family has an ID in A on the "Mems" sheet, and each child is listed separately on "Children" sheet and they each also have the family ID listed for each child in A.
On the Mems sheet all the IDs are unique.
On the Children sheet, as there can be multiple kids per family the same ID value can appear multiple times in A.
How do I get the Mems sheet to show all the kids names (in B on the children sheet) either in 1 single cell (concatonated) or in a series of cells apended at the end of the relevant data row on the Mems sheet?
I have a column with with Employee ID numbers, with anywhere from 2-5 blanks rows in between them. In another sheet I want a column that returns these ID numbers without the blank rows in between. All of the ID's begin with "N", for example:
I have:
N33333
(blank row)
(blank row)
N22222
(blank row)
(blank row)
(blank row)
N66666
I want:
N33333
N22222
N66666
I am working on a spreadsheet for some packages we are sending to multiple recipients. I have figured out how to get all the weights calculated, now my Mailing Manager has asked me to come up with a calculation for the actual postage amount for all the 1400+ packages...Unfortunately it is not as easy as that would seem.
There are 9 different mailing zones and each zone has 19 weight categories and each category has a corresponding postage amount for the package. So I need to come up with a formula to calculate the package postage amount based on those multiple categories.
For example:
Zone 1:
0-1lb = $2.12
1.1-1.5lb = $2.12
1.6-2lb = $2.21
etc
How do I go about entering in that information from which a formula can determine the proper package postage cost and what formula do I need to use in conjunction.
This is all done within a address list so each package is a row and all weights and zones will be in a separate column.
Package is in zone X and weighs Y pounds therefore the postage is Z. Something like that.
How do you count the number of columns in a range in order to reach ( find) a specific value in that row ?
View 5 Replies View RelatedI am trying to solve a big problem for a project that I have to complete and for hours now I have searching for help as I run over this website. I will place a photo below and explain what the problem is...
I have this row that has 105 cells (I only put some of them for an example) and I have to find from these 105 cells how many 1,2,3,4,5,6,7,8 and 9 exist.
Is this possible to be found through a command?
I want to look up dates, list, two different variables (i called it D and P) and add the Ds and Ps which happen within the columns (it may have to skip columns and add). look at the attached image for the problem. i tried various combinations of sumifs to no avail. Is there a VBA code for performing this.
in the problem: list1 looks at the list1 and the date range from the bottom and adds the corresponding D and P values from Jan 2005 to Apr 2005. dates and lists will vary and are inputs.
I am trying to perform a multiple look up that will look for a value in column B then go over two columns and look for a value then return a value several columns over. Attached is a worksheet with an example.
View 4 Replies View RelatedI have a 20000-row sheet generated by a daily report and I only want to work with about a quarter of this data; any rows that contain "PH4" in the M column. What's the best way to automatically single out this data to work with?
My initial solution was to create a seperate sheet to pull off all the columns with PH4 in the M cell, eg cell A8 would be:
=IF([WEB_StoSta_Rep.xls]Sheet1!$M8="PH4",[WEB_StoSta_Rep.xls]Sheet1!A8,"")
Then I could run lookups off that sheet. However, this feels like a sloppy solution to me, and Excel throws up an error when saving, unable to save external link values with available resources. Is there a better way of going about this?
I have a table (Arrears) showing a list of customers with part numbers and quantities in arrears. A second table is a customer order table showing details of what needs to be shipped for a given date.
Based on these two tables, I would like to add a column to the Order table to show the values for arrears against the order quantity for the given date. In addition if there are no order requirements but there are arrears then insert a row to show the corresponding arrears.
see the attached spreadsheet for more details. This shows the two source tables (Arrears and Order) and the expected results showing the added column for arrears and the inserted rows.
Basically I'm trying to look up a series of numbers against a separate row of numbers and look for a match regardless or number order.
For example
If you look at the above picture I'm trying to do a query of some sort that will look up the numbers in A8:G8 in then search each row in the above table ie look for the numbers in B1:J1, B2:J2,B3:J3 etc I need to be able to search each row and look for the sequence of numbers regardless of order, if there is or inst a match for all numbers it should look at the next row and so on (maybe multiple matches). If there is a match then it should display the Name located in column "A" into cell G8. In this example to Jarrad row contains the numbers located in A8:G8. If there is no match it should display "None".
I'm trying to find any easy way to do this as I have over 500 rows I'm trying to query. The number's in A8:G8 in this example could also be more or less, ie here I have included 6 numbers but this could be 3 or 9 etc.
Having an issue with an array formula.
{IFERROR(INDEX('sheet2'!$A$2:$A$14,SMALL(IF('sheet2'!$C$2:$C$14="X",
ROW('sheet2'!$A$2:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.
Originally I did not need to skip any rows, but now I have to skip certain rows. I've tried using my current array formula and skipping the rows not needed but can't get it to work.
{IFERROR(INDEX('sheet2'!$A$2:$A$5,$A$9:$A$14:SMALL(IF('sheet2'!$C$2:$C$5,$C$9:$C$14="X",
ROW('sheet2'!$A$2:$A$5,$A$9:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.
Can this still be done with an array formula? I need to skip rows 6, 7, 8. Not sure what I'm doing wrong but no names are appearing. Sheet 1 looks like this with "Job Titles" starting in A2 on sheet1.
Job Titles
Dept.
1
2
3
4
Claims
CSI
Outb Calls
CSI
Notifications
CSI
Evaluator
CSI
I tried a new formula in cell C3
:=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(ROW(Sheet2!$C$2:$C$14)=9,
IF(Sheet2!$C$2:$C$14="x",ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1)))),C$2),1),"") entered as an array.
I should see the name "C. Pepper" followed by "L. Smith" when I copy the formula to the right into D3, "J. Rast" would be skipped as these are the rows not being picked up, but nothing is showing.
Sheet 2 looks like this with "Staff Name" starting in cell A1:
Staff Name
Mgr
Claims
Outb Calls
Notifications
Evaluator
A. Hawkins
Adams
[Code] ..........
I am looking to build a VBA to do the following action:
Look up an ID number from column F (ID numbers are separated by an empty row at current),
then check whether there is a corresponding value in columns N to P,
if there are values, copy them onto a different sheet into three different columns
if there values below this particular row, i.e. if the rows below say row 9 (where we looked up the ID number) in columns N - P are not empty, also copy all these values and add additional rows as needed on the sheet this is copied to
then look up the next ID in column F and do the same until there are no more IDs