find attached my spreadsheet. I want the excel to look at j2.. search for it in A coloumn and return its corresponding B coloumn values. There might me multiple values with the same name in A2. I would like the excel to return all the multiple values with their corresponding B coloumn values.
View 3 Replies
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
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
I need a formula to return column header if there is data in the column, any data.But I also need to return the second, third, fourth header with data too
In column A row 2, I have =IF(M8"",$M$1,IF(N8"",$N$1,IF(O8"",$O$1,IF(P8"",$P$1,IF(Q8"",$Q$1,IF(R8"",$R$1,IF(S8"",$S$1,IF(T8"",$T$1,""))))))))
This returns the row 1 header for the first instance of data in row 2 columns M thru T But in B2, I need the second header with data in the column.
I need the results in yellow (1st, 2nd, 3rd, 4th) from the array to it's left
1St match2nd 3rd 4thColumn MColumn NColumn OColumn PColumn QColumn R
Column MColumn PColumn QData data2Data3
Column NColumn OColumn PColumn R Data Data2 data3 data4
Would use match/index but can't increment it to the second or 3rd match
I want to return the column headings (the months) where there is a 'No' value, per person. How to do this?
Here's the screenshot
Screen Shot 2014-03-17 at 11.45.34 am.png
And here's a dummy version:
Example Spreadsheet.xlsx
I have my lookup value in column A. The vlookup result in Column B.
=VLOOKUP(A1,ALL!B2:Y10482,2,FALSE)
I now want to drag this across to populate and return values up to column Y.
When I drag the formula or copy it to Column C it does this:
=VLOOKUP(B1,ALL!C2:Z10482,2,FALSE)
The lookup value, table array, and column index number have now changed to column B, C:Z, and 2, when I need it to increment like this:
=VLOOKUP(A1,ALL!B2:Y10482,3,FALSE)
The only thing I want to change as I paste it into the adjacent field is the col_index_number.
Is there a way to do this horizontally without manually changing the formula 26 times?
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area.
here is an example of my form.
COLA | COLB |
--------------
1 | 324 |
--------------
2 | 325 |
--------------
1 | 327 |
---------------
this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
I have made this function that returns the string value for a column based on it's number but it starts getting more complicated after I've been thru the alphabet once and have to return two string values to move across the chart.
PHP
Function colnum_string(ByVal num As Integer) As String colnum_string = "IV" If num = 1 Then colnum_string = "A" End If If num = 2 Then colnum_string = "B" End If If num = 3 Then colnum_string = "C" End If If num = 4 Then colnum_string = "D" End If If num = 5 Then colnum_string = "E" End If If num = 6 Then colnum_string = "F" End If If num = 7 Then colnum_string = "G" End If If num = 8 Then colnum_string = "H" End If If num = 9 Then colnum_string = "I" End If If num = 10 Then colnum_string = "J" End If If num = 11 Then colnum_string = "K" End If If num = 12 Then colnum_string = "L" End If If num = 13 Then colnum_string = "M" End If If num = 14 Then colnum_string = "N" End If If num = 15 Then colnum_string = "O" End If If num = 16 Then colnum_string = "P" End If If num = 17 Then colnum_string = "Q" End If If num = 18 Then colnum_string = "R" End If If num = 19 Then colnum_string = "S" End If If num = 20 Then colnum_string = "T" End If If num = 21 Then colnum_string = "U" End If If num = 22 Then colnum_string = "V" End If If num = 23 Then colnum_string = "W" End If If num = 24 Then colnum_string = "X" End If If num = 25 Then colnum_string = "Y" End If If num = 26 Then colnum_string = "Z" End IfEnd Function
Is there a better, more simple way to return the string value for a column from it's number reference?
Each row in Table 1 represents an "Expanded" array formula.
In Table 2 I have a "Running" formula to return progressive max values working Left to right.
My question is, can this be written as an array?
Returning something on these lines ...
{(IF(B1="","",MAX($B1:B1))),(IF(C1="","",MAX($B1:C1))),(IF(D1="","",MAX($B1:D1))),(IF(E1="","",MAX($B1:E1))), etc.}
Drag B7 across and down to see what the array should contain for each row in Table 1
Sheet1 Â ABCDEFGHIJKLMN1Table
1Â Â Â 1234Â Â Â Â Â 42Â 141Â Â 5Â 3Â Â Â Â 53Â 1Â 46Â 423Â Â Â Â 64Â 122Â 4567Â Â 89105Â 122Â 4657Â Â 89106Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7Table
2Â Â Â 1234Â Â Â Â Â 48Â 144Â Â 5Â 5Â Â Â Â 59Â 1Â 46Â 666Â Â Â Â 610Â 122Â 4567Â Â 891011Â 122Â 4667Â Â 8910Spreadsheet
FormulasCellFormulaB7=IF(B1="","",MAX($B1:B1))
as an example, i have a column of data that includes cells with blank and non-blank values...i want to write a formula that will refer to this column and list the non-blank values - either in one cell or separate cells.
View 9 Replies View RelatedI've got a complicated logic formula which looks at multiple values on a large spreadsheet to return a singular figure based on whether all these conditions are met. It works well but I wan't to add an extra stipulation to it. I won't paste the full formula as it probably wont mean much without all the data so heres a mini version with simplifed conditions - but that DOESNT work:
=SUMIF(K10:K13,IF(H10="CAT","CAT",IF(H10="DOG","DOG",IF(H10="ALL",OR("CAT","DOG"),""))),L10:L13)
Its the last bit that I'm having problems with - I want to say if H10 equals "ALL" then in my table of values to add up not just "DOG" values or "CAT" values but "DOG" and "CAT" values
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
I have a two different workbooks book 1 and book 2 with some data.
If values of first two columns ( Column A and B) of both workbooks are matching then I want a formula that can return the value of third column (column c) of book 2 in book 1 column c.
example:
Book 1
column A / Column B / Column C
100 / 200 /
Book 2
Column A / Column B / Column C
100 / 200 / 300
now I want a formula that can return in column C of book 1 to display 300.
And the data runs across some 100 rows in both workbooks.
I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".
Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom
Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom
I'm sure this is elementary, but I can't seem to figure it out.
Ok, imagine this data (say column A). I want to have a cell that automatically searches for the least value (will always be the first number from top) and returns it in another cell. And then repeat the same for the second number from the top.
Beg Search STA
-
-
300
-
-
1050
-
-
2200
I have a challenge which I am trying to solve without VBA and I am uncertain how to approach
If I have the following spreadsheet consisting of three columns:
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
D.........................
E.........................
F........6........4......
The result I would like to achieve is
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
F........6........4......
So, it means I would like to return the rows where Columns B and C have a value. Is this possible with strictly formulas?
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 3 Replies View RelatedI have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies View RelatedI am trying to use a formula similar to VLOOKUP() to look in a table for two values and if both values are found, return another value.
Sheet2 is an example of my output file, sheet one is a generated file. i would like the vlookup formula in sheet 2 cell D2 and down to be something like this,
=VLOOKUP(A2,Sheet1!A:K,10)
But I would like to lookup A2 and B2, and the row that contains both values, return the value in the J column
I'm trying to use vlookup to return multiple values for the same unique identifier. I've read a couple of examples within the forums, but I haven't been able to find anything I understand. I have employee ID's and these ID's have specific access associated with their ID's and each access is listed within a different row. Within a new spreadsheet I removed all duplicates of the employee ID so now I no longer have a one to one match between spreadsheets, so I would like to create a vlookup that will list all access associated with each employee ID. I have attached a sample of the data sheet I am working with.
View 5 Replies View RelatedI am trying to create a formula that will do the following:
If A1 is between 95 and 99 then populate with 2
If A1 is between 90 and 94 then populate with 3
If A1 is between 85 and 89 then populate with 4
If A1 is between 80 and 84 then populate with 5
If A1 is below 80 then populate with 6
I basically have this worksheet which has Cell D3 Being Dynamic. It puts the lowest cost supplier from columns H:W. If i change the prices in H:W it will bring up the lowest cost supplier in D3. Once D3 is chosen i want it to self populate F3 and G3 based off of what is in D3. This data should be pulled off from what is in the H:W columnns corresponding to the supplier in D3.
View 3 Replies View RelatedColumn A:
pants A green
Pants B red
Jacket C green
Jacket D blue
Polo C white
....
I need a formula to find some value( in our case "colors" = "green, red, blue, white" ... = 20-30 values), and to return in column B, the values, like:
Column A: Column B:
pants A green | green
Pants B red | red
Modern green Jacket | green
blue winter jacket | blue
Polo white D | white
I have different product feeds (csv) and i will like to search for colors in the product names.
I am trying to find a way to use information in one cell in order to look up corresponding multiple values on another sheet. The problem that I am running into is that VLOOKUP only returns the first value. I need the values to be calculated in different cells going vertically, not all combined in the same cell as was in an earlier post. I am not looking to sum anything so a pivot table also doesnt work.
Sample data:
A / B / C / D
Identifier / Pub ID / Invoice # / Job #
ABCD1234 / ABCD / 1234 / A41254
ABCD1234 / ABCD / 1234 / B41254
ABCD1234 / ABCD / 1234 / C41254
DCBA4321 / DCBA / 4321 / A56789
etc.
I am looking for it to do this:
A / B
Identifier / Job #
ABCD1234 / A41254
/ B41254
ETC.
I want to enter ABCD1234 into another worksheet and have it return all of the job #'s, but I have a lot of these so I don't want to have to look up how many job numbers there are associated with it and have to enter different formulas into multiple cells. Filtering also isn't an option as there are simply too many cells to calculate.
I have written many Index/Match and cannot figure out why I cannot create a formula for this.
View 1 Replies View RelatedI have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.
View 7 Replies View RelatedI know would like to return column C in the INDI LEG VALUES table if the following occurs
View 4 Replies View RelatedI have an excel sheet with cols of information e.g IDNo, FirstName,LastName etc. I have created a form in VB where you enter the IDNo (& Click on a command button called View) It should populate designated textboxes on the Form. Thus it should check the col where the IDNo is stored, find the IDNo and populate the adjacent cols i.e FirstName, LastName in designated textboxes on the form.
View 3 Replies View RelatedI need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".
The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).
I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.
Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
I have number data in columns F through AK. In column AL I want a formula that will look in columns F through AK from the left to right. Once it identifies a positive value I want the formula to identify if after that positive number there are any occurances where there are 6 0's in a row (anywhere up until column AK). The formula can return a True or False. I want to drag the formula down across many rows of data. If the entire row contains 0's, I do not want it identified as a TRUE
View 9 Replies View Related
