Multiple Values Returned From Same Column
Apr 3, 2014
I have IDs in the first column of an excel chart. After that I have three more columns, being date of test, type of test (start, 3 months, 6 months, 9 months, finish), and lastly the result for the test.
Right now, the same IDs are listed multiple times for different results, so for example:
ID | Date | Type | Result
27 | 3/27 | Start |8.3
27 | 6/27 |3 Mon |7.9
27 | 9/27 |6 Mon |7.4
27 | 12/3 |9 Mon |7.2
27 | 3/27 | FINISH |6.5
What I need is the following layout:
ID | Start | Date | 3 Months | Date | 6 Months | Date | 9 Months | Date | Finish | Date
ID is only shown at left, and the values for the test result and corresponding dates are shown in their respective columns.
I tried to do an IF function with a LOOKUP inside, and it worked originally, but when I add more values for the same ID to the original column, it only shows the latest date, and only gives that result.
View 1 Replies
ADVERTISEMENT
Dec 23, 2013
I have a table where the rows are conditions and the columns are experiment numbers, as an example below:
Experiment 1
Experiment 2
Experiment 3
Hardness
X
X
Solubility
X
X
Density
X
X
The table is fairly large. What I would like to be able to do is use a lookup/formula that will return all the experiment numbers a given condition is tested in. In other words, for a given condition (i.e., Hardness), which columns have an "X". Not sure if that can be done with a lookup or not or if there is another function necessary. I would like to be able to do it without macros though and also have it automatically update if I move the "X" around to different experiments.
View 3 Replies
View Related
Jun 20, 2006
I am trying to write a formula that will look at an array (containing text strings) and then for each occurrence of a particular value return the text string from another cell in another column but which matches the row of the occurring value. I would prefer all returned values to be entered into different cells in a column but I would be happy if they were all in one cell separated by a comma or whatever.
View 7 Replies
View Related
Jun 2, 2009
I am trying to concatenate the values of many cells, not the formulas in the cells, to avoid having to repetatively paste special, value. Perhaps something like this: =CONCATENATE(VALUE(AA2):VALUE(AQ2))
View 3 Replies
View Related
Mar 28, 2007
I am trying to do a spreadsheet for my boss and one piece of it has me stumped.
Based on same "die name" I need to return the minumum quantity available.
So, in column D i have a multitude of die names. Some of them match for those that match, I need to return in another cell the smallest quanity available in our die bank (column p).
View 10 Replies
View Related
Jul 10, 2009
How can i make a best XI team from a list of weekly scores in my Fantasy Football league?
Players are as follows:-
One goalkeeper - Top scoring goalkeeper per week
Four defenders - Top four scorers per week
Three midfielders - Top three scorers per week
Three forwards - Top three scorers per week
I need it to find the top scoring players in the above categories and then display them in a specified area.
I also need this to happen for each individual week, NOT cumulative. literally, the highest scoring XI players, as listed above, for the present week.
View 6 Replies
View Related
Jul 15, 2014
I'm trying to find a macro that will report how many values are returned in a pivot table. For example, if there is 5 values returned, I want it to return the value "5" in a cell If there are 10 values returned from the pivot table, I want to return the value "10" in a cell
View 1 Replies
View Related
Aug 27, 2009
I am trying to have a value returned in cell C3 if two conditions are met. In Cell C3 (Sheet 1), if the value in Cell A3 (Sheet 1) is listed in column A on sheet 2, and the 12031 is listed in column B on sheet 2, I need the value of Open to be returned.
View 3 Replies
View Related
Mar 4, 2006
I have two columns with multiple sets of data, based on the entered value I
need the corresponding header returned.
For example:
Column A Column B
Apples Green
Red
Granny Smith
Mutsu
Red Delicious
Oranges Naval
Manderin
Clementine
Tangerine
Now based on a response in another sheet I need to return either "Apple" or
"Orange".
=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B65535,0)) will work if I copy
Apple next to each apple type, but for other items the list of options can
grow dramatically.
View 9 Replies
View Related
Oct 20, 2008
Aged Debts & Comments
Comments consists of the following
Customer number, Date, Comment, Date, Comment, Date, Comment, Etc Etc....
Aged Debts is
Customer number, Current Outstanding, 30,60,90,120.. Comments.
I would like to bring back the last comment which would be the furthest right comment in "comments".
Ex. of COMMENTS
A B C D E F G
0001 17/10/08 Called Cust. 20/10/08 Payment Promised
0002 15/10/08 No Answer 17/10/08 No Answer 20/10/08 letter Sent
0005 15/10/08 Payment Promised
Ex of Aged Debt..........
View 9 Replies
View Related
Dec 7, 2009
I'm trying to copy Rows from a worksheet ("sheet1") in Workbook ("SourceData") to another workbook ("Final") and worksheet ("Regions") based on critieria selected in a listbox ("Listbox" located in a user form in "Final". The listbox selection is pasted in another worksheet, "Steps"). The trick is, the selection can be a single choice (Region2) or multiple choice (such as Region1, Region 3 and Region 7) and I'd want to copy any rows containing the selection criteria. I've got a bare bones start, but I can already see it's going to give me trouble. Here's what I've got:
View 9 Replies
View Related
Feb 10, 2010
Is there a formula to isolate observations in the same column (different values) and also all have the same column heading like the file attached?
View 2 Replies
View Related
Feb 11, 2009
What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.
ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023
View 4 Replies
View Related
May 1, 2014
Is there a way to collate multiple entries for the same record all into one column, as shown in the attached workbook?
View 12 Replies
View Related
Mar 14, 2014
I have a excel sheet with several columns and 2700 numbers in each column. In the first column there is standing from witch quarter to witch quarter of the day the values are (and this for several days in one sheet). I want to take the average of all the first quarters of the day, the average of all the second quarters of a day,.... But if I would do that by just sorting my table on the quarters and than manually make the formullas I would need to give in 800 formules. (way to much)
So I would like to find an easier way to take the average of column B for the valeus were A is "00:00 -> 00:15" (this are avery time 28 numbers in the month february)
In attachement you can find an example of my problem, although it isn't with real values.
View 5 Replies
View Related
Jan 24, 2008
I am trying to sum up the rows that have multiple values in one column.
Here is my curent formula THAT works
=SUMPRODUCT(($H$46:$H$5787="EO-Deal Processing-Closing")*($K$46:$K$5787="Submitted")*($I$46:$I$5787="2-medium"))
Now I also want to add the following
($K$46:$K$5787="Assigned")
How can I get the value I need so that column "K" I get returned both "submitted" and "assigned"?
View 9 Replies
View Related
Oct 23, 2009
Matching Multiple repeating values in one column with another.
I have a three columns of data that I need to map the requird Ids in Col A against multiple repeats in Col C. AS per data below ....
View 6 Replies
View Related
May 9, 2012
I have a column:
LABEL COUNT
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 16
15 empty cells
I need to fill the empty cell with the "series" (2 of 2, 2 of 16, 3 of 16). This is just an example of data - there are all different series - 1 of 12, 1 of 5, etc.
Anyway - was hoping to highlight the whole column and be able to auto fill instead of having to extend each series. I have tried straightforward fill series which doesnt work of course. I have tried go to special - blanks but cant find right combo to make it work.
Will it work or do I have to either do manually or with a macro?
View 2 Replies
View Related
Jan 31, 2013
I am trying to pull peoples names from 3 diferent tabs into one column in a separate tab. But if a name is shown more than once only display it once. How can I do this?
View 1 Replies
View Related
Sep 20, 2013
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?
View 8 Replies
View Related
Aug 1, 2014
I am working with an aging report where data is formatted in column. In the last column I would like to assign a value based on criteria from the adjacent columns (ex: If balance > $10,000 then value = "Hold").
My idea was that I would asign variable to the column, start a for next loop, and insert the criteria in an if then else loop. Is this the best way to go about it, should I be using case select, and how do best assign the variable to column so that I can loop down the entire report.
Here's an example of what I'm working with
Balance
Current
90
[Code]....
View 2 Replies
View Related
May 30, 2007
My problem is how do i get multiple column values at one shot.
For example in one excel sheet i have columns A,B,C,D,E and in A column i have all the Partner ID's and rest of the columns i have the data.
Now in other excel file I have Partner ID's which are not in order...now i want the data in all 5 columns according to partner id's from the previous sheet i need to do a vlookup function for five times to get the same data....is there any way that we can do it in one shot.
View 11 Replies
View Related
Dec 2, 2006
Option Explicit
Dim lastrow As Long, t As Long
Sub Method()
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 8).Value <> "" Then
If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _
6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20
End If
Next t
End Sub
Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.
View 5 Replies
View Related
Jul 6, 2007
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 3 Replies
View Related
Nov 29, 2013
I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.
What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.
Sample Workbook.xlsx
View 12 Replies
View Related
May 31, 2014
What i'm trying to do is i would like to compile in 1 column all duplicate values from multiple cells.
ex. A1 to 10 is numbered 1 to 10 respectively, B1 to B10 is numbered 6 to 15 respectively. which means in A1:B10 the duplicate values are 6,7,8,9,10. i could like these number to show automatically in C1 to C5.
View 9 Replies
View Related
Jun 26, 2014
I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.
I have tried different formulas, but fast running out of time
I have attached a modified sample from the main spreadsheet
I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.
View 7 Replies
View Related
Oct 13, 2006
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1()
Const NumRow As Integer = 3
Dim StartCell As Range
Dim RowNR, NewCnt As Long
Dim RowCount As Long
Dim Count As Long
Dim intRow As Integer
Dim bFmtComplete As Boolean
RowCount = Application.WorksheetFunction.CountA _
(Range("A1", Range("A" & Rows.Count).End(xlUp)))
bFmtComplete = False
RowNR = 2
Range("A1:J1").Select
' Rows("1:1").Select
Selection.Copy................
View 4 Replies
View Related
Jul 8, 2014
I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
View 3 Replies
View Related
Jan 24, 2014
I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?
In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.
View 14 Replies
View Related