Display Corresponding Data For Certain Values Of 1 Column
Jul 11, 2009
Is there an excel formula that can give me the offset value of each ocurrance of repeated data in a range of cell? I know there might be something in VBA, but I'm not quite familiar with it, and the spreadsheet will be passed on to others who are not VBA versed and would not know how to troubleshoot if the code blew up somehow.
I was told that i might be able to use Sumproduct with Offset, but not sure how to go about it. I've attached a sample file that explains how I would like the output to be and where.
I have a column that contains Gender values ( M or F). How do I create a formula that first counts the number of M or F then tells me the ratio of M to F?
I am not very smart in Excel. I have problem with pulling data from the following range of rows.
A 1 Apple 2 Banana 3 4 Grapes 5 6 Pomegranates
Expected Result: Apple, Banana, Grapes and Pomegranates
scenario: One or more than one of any of the 6 rows can be blank. I need a formula or procedure that pulls together data from A1:A6 automatically and adds "and" between the last two values. See the expected result above for an example.
data validation to display a list of values. The problem am facing is .. this list has a lot of names & it becomes difficult to scroll through when i need to search for a particular name.. Is it possible that when I press a key on my keyboard, the name starting with that letter is highlighted in the drop down list.. This is not happening automatically using data validation... Is there a work aorund for this?
Basically, I have a simple - but large (48Mb) sheet with UK mileage distances between towns. As the are 1,000 towns and therefore 1,000,000 cells of data it is a little tedious to scroll to the desired town. see sample attached (this is not the real file obviously!)
What I would like to do is type in a start and end point, and have Excel look for the intersection and display the value of this.
I have a problem with this workbook, i know how to do it by nesting if functions but i want a neater way to do this. I have tried if(index(match)) type formula and haven't got it to work.
I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.
I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.
I started the formula in P2 of the GL Detail-2012 tab. File attached.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,))). Not working.
I would like to display the column header of the row in which a value other than 0 exists in a cell for each instance that value exists in an array spanning 3 columns. So the result cell could be any of the three column headers.
I started the formula in P2 of the GL Detail-2012 tab. (FILE TOO BIG)
I have about 3000 of these values per column. I need to sort the data so that column B and E are the same values. But the cells to the left and right of each B and E need to sort with it. Plus where there is a missing consecutive value I need a blank cell added...
On the attached worksheet, our guys are regularly inspected for performance. Column 'H' shows the average of their grading from each inspection (shown in cols L,P,T etc) What I would like to develop is either a macro button that when pressed sorts them all into order according to their average score in Col H, with the highest score at the top, or to have the sheet do this automatically as the data is entered. Obviously all of the data in the engineers row (all of his inspection history) will need to move up and down accordingly, but not get left behind, or mixed up with someone else's records.
I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?
This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score.
I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players.
I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players.
I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?
I have a spreadsheet that track orders. I want to be able to add a bit code to my current VBA script that sorts the data based on the values in a particular column (smallest to largest).
Current table looks like this:
Order Number Date Quantity Colour Priority
548562 12-09-2012 56 VBA formula 2
[code]....
I want to add be able to sort the data, smallest to largest based on Column E (tittle Priority)
Any way to search for a value down column A, another value across roW 1, and tells what value lies in the cell at their meeting point. 4 example, I'm looking For "47" somewhere in column a, and also the number 53 somewhere in row 1, and I need to know what value is in the cell where those to meet.
I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....
I would like to copy and paste the values from a worksheet (HS-Detail) in a file to different tabs depending on the value in column A (Regions). I have a macro that works but it pastes the formulas and it takes quiet a while to run due to the number of records. How can I alter this to paste just the values and speed up the macro?
The Table : Column R represents a list of services
Column L is the full price Column N is the discounted price ( my example reflects no discount in this case) Column C is an associated code , in some cases the code is n/a Starting at Row 13 ........
I've come across the post by Dangelor but can't reply to it directly so have started a new thread quoting the code. I'm trying to select entire rows of data based on specific values in a column and then paste those rows to a new worksheet. This code loops 10 times and creates 10 new sheets. Any chance someone could explain some of the code to me and adapt it to suit my situation?: Data will be in sheet 1 ("Data List"). I want search down the rows and if the value in column 2 is "1" copy that row to the sheet named "Heat 1", if the value is "2" then copy that row intt the sheet "Heat 2", etc.
Sub FindandCopyRows() Dim Data As Variant Dim DataFound() As Variant Dim iValue As Integer Dim j As Long Dim i As Integer Application. ScreenUpdating = False For iValue = 1 To 10 With Worksheets("Main") 'change name as needed .Select Data = .UsedRange.Value End With Redim DataFound(1 To UBound(Data)) For j = 1 To UBound(Data, 1) On Error Resume Next......................
on the syntax of things in VBA. here's a description of what i'm trying to do:
1) loop through a particular column ("N"), and compare the values (if not blank) with corresponding cells in another column ("V"). i.e. - comparing N1 to V1, N2 to V2, etc...
2) run If, ElseIf statements to display appropriate values in different column ("O"), but on the row that corresponds to the row the values in Step 1 are compared. i.e. - if N1 > V1 then cell O1 = value; if N2 > V2 then cell O2 = value; etc...
here's an example (but obviously not in proper syntax). hopefully someone can help me convert it to Excel VBA:
Function CalculateFR()
Dim Col1 As Column = ColumnN Dim Col2 As Column = ColumnV Dim Col3 As Column = ColumnO Dim Cell As Cell Dim IndexValue As Integer
For Each Cell In Col1 If Col1 > Col 2 Then IndexValue = 5 Else If Col1 < Col2 Then IndexValue = 4 End If Next Cell
I have a thread in here called "Inputing data values automatically based on data value in another column". I have determined that I need to use the VLookup function.
The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:
While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.
The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).
I have a macro that copies three sections of data from a word document and pastes it within the Excel worksheet. The column of data is pasted in a single column (data to be space-delimited and evaluated later), and what I would like the MACRO to do is separate the three sections of data based on the starting and ending values of the section and place them in different columns.
Section 1 : starts with the word "Team" Section 1 : ends with the word "City"
Section 2 : starts with the word "Location" Section 2 : ends with the word " Date"
Section 3 : starts with the word "Member" Section 3: ends with the word "Age"
So section 1 would be pasted into cell C1, section 2 pasted into G1 and section 3 pasted into J1.
Each section will have vary in number of rows between the starting/ending values, so to cut and paste the section based on the starting and ending value is vital.
I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.
