I'm not sure what I'm trying to do is even possible, but figured this is the place to ask the question. I'm trying to compare a list of companies (column a) to a list of employee email addresses (column b) and post results (column c) that display all associated email addresses that are unique to company name. Since multiple employee email addresses can be associated with numerous company names.
View 2 RepliesI have attached an example spreadsheet with two tabs. The first tab (Matrix) shows raw data scores - there are 28 measures in Column A and Participants in Row 1. There are always 28 measures but participants can vary. What I need to happen is to sort each measure in a Lineup tab i.e. rank all the participants listed in the Matrix tab from lowest score to highest score for each of the 28 measures. Obviously this can be done manually but a VBA/macro solution will be so much faster and easier. Unfortunately I have to keep the data in this format so the macro will need to transpose the data to get into the formt in the Lineup tab.
View 9 Replies View RelatedI have a model that does a projection for 36 months ahead on a spreadsheet. The projection depends upon a randomising "volatile" function. When I press recalculation a new projection is generated. I want to capture the set of numbers that appears at month 36 through repeated recalculations written to a separate sheet. I want around 500 of these numbers.
I have no experience with macros so doing it with preset functions would be preferable. If it has to be a macro, the easier the better. If invoking the spreadsheet and outputting to a flat file is easiest that is fine too.
I wanted to replace Array(Array(1,1),Array… in TextToColumns.
http://www.ozgrid.com/forum/showthread.php?t=140770
http://www.excelforum.com/excel-prog...o-columns.html
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:
10000 X Street Louisville, KY 40291 is in cell E2
I would like it to read:
10000 X Street in cell E2
Louisville in cell F2
KY in cell G2
40291 in cell H2
They also did this with phone numbers (ie. desk# / cell# / fax#).
There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.
I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I have a column of repetitive data:
BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL
I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL
I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?
I have a file with a bunch of columns in it. Columns C and D have names in them. I want to filter on these two columns where the results show all rows where the selected name is in both.
Example, If I filter on Column C first on 2 specific names (Joe and Chris), it will not show the results in column D if that name was aligned with a different name in column C (Mark and Steve). I need all rows to come back.
Column C
Column D
Joe
Joe
Chris
Chris
Joe
Chris
Joe
Mark
Joe
Steve
Chris
I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?
[Code] .....
For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?
I have searched other postings but can’t seem to find the answer.
I was wondering if anyone had a solution on how to string multiple Vlookup results as a text string so that if I Vlookup “Pear” it will return “Red, Blue, Green” in adjacent cell
i.e
A B
1 Fruit Colour
2 Pear Red
3 Orange Purple
4 Apple Green
5 Banana Yellow
6 Pear Blue
7 Pear Green
8 Banana Black
9 Orange Green
10 Apple Black
Is Vlookup the right function to go with here?
I am trying to create a scatterplot from some data I have. I attempt this by selecting multiple columns using Ctrl and clicking on the letters above. This results in it plotting only the first selected column on the Y axis with only 1 2 3... on the X axis.
When I however select multiple columns by clicking on a column and dragging to more columns it does plot as I would expect with the first column as X values and the rest Y values.
I know it is possible to create a graph and add data manually but this is way slower and I think that it should be able to be done differently.
I'm using excel 2010.
I need to create hundreds of named ranges going down a single sheet.
The name of the first range is in cell a1 and is 13 columns wide and 7 columns high (a1:m7)
The next name is in a8 and the range is a8:m14 and so fourth
If it's easier on a separate sheet I can have a list of names I want in column A and then the cells they refer to in column B.
E.g.
A B
Range1 Sheet1!A1:M7
Range2 Sheet1!A8:M14
Range3 Sheet1!A15:M21
I am using a vlookup formula to link 3 pieces of data together. I would like to insert a space between the 3 pieces. Currently formula result is:
item1item2item3
I would like it to view as:
item1 item2 item3
not sure how to alter the formula to create the space....
I have some VBA (using Excel 2007) that deletes and re-creates a bunch (20-ish) checkboxes in a spreadsheet. Since i'm using the controls-based checkboxes, Excel defaults them to object names of "CheckBox1, CheckBox2, CheckBox3, etc" as I create them the first time around.
Later on, i clear all checkboxes and implement the SAME checkbox-creating code to re-create all or some of the checkboxes, but Excel doesn't number them in order the second time around! I end up with something like "CheckBox2, CheckBox19, CheckBox3, CheckBox12, etc."
What could be causing my code to create things out of order the second time around? Is there a way to reset Excel's Checkbox-numbering iterator?
The code i'm using to create checkboxes is:
Dim x As Long
Dim BoxCell As String
BoxCell = ""
x = 0
'j gets passed into the sub as a "Long" somewhere between the values of 5 and 35
See the attached:
1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.
Company Name, Year, Manufacturing, Non-Manufacturing
This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.
I am doing an ANOVA analysis of survey results for a set of products, and would like to present a chart to graphically represent the results.
View 1 Replies View RelatedI have two columns that represent constituents from a stock indices from two different years. I would like to find out what stocks appear in both years.
View 1 Replies View RelatedI have a list of names in column B, I can easily put this into a list
I also have a different list of names in Column C
These two list are feeding from sharepoint and are updated regularly
What I want to do is create a drop down list that will list all the names in Column B and Column C, while keeping the initial lists in there separate columns
I have been trying to find a formula to lookup both columns and return all the names into another column (E) using the formula below
=IFERROR(INDEX($B$2:$C$600,MOD(ROWS(E$2:E2)-1,ROWS($B$2:$C$600))+1,INT((ROWS(E$2:E2)-1)/ROWS($B$2:$C$600))+1),"")
the plan been to just put column E into my drop down list, but this formula is returning alot of blanks between Column B + C in the list (there might be 100 names in column B and only 30 in column C)
To try remove the blanks I tried another formula pulling the info into Column F,
=IFERROR(INDEX($E$2:$E$5000,AGGREGATE(15,6,(ROW($E$2:$E$5000)-ROW($E$2)+1)/($E$2:$E$5000<>""),ROWS(F$2:F2))),"")
But while this did remove the blanks (about 500 blanks) it left me with about 70 "0" errors.
I have a Master List with 3 colums The first column labeled location has a pull down menu with about 20 different options. The middle column labeled issues is just for text entry. The third column labeled contractor is another pull down menu with 18 options on it. I also have 18 other sheets within the workbook that correspond to the third column pulldown menu.
Heres what I need.
I would like to be able to enter info from columns one and two and be able to choose an option from column three and the info from columns one and two go to the corresponding sheet.
I have a list of data which is not in any particular order....
View 9 Replies View RelatedI'm trying to have a drop down list in a spreadsheet which combines the info from two columns.
For example:
A B
1 John 29
2 Sandy 40, etc
And the drop down list turns into
John 29
Sandy 40
Is this possible?
I'm trying to make a sheet to calculate my taxes. I would like to keep tax rates from previous year(s), and have them applied based on which year I input in a cell..
Let say that in cell B1 I write year 2013
In cell G1 I have year 2013 and in cells G2:G7 I have tax rates from year 2013.
In cell H1 I have year 2014 and in cells H2:H7 tax rates from year 2014
In cell B17 to B20 I would like to make a formula that multiplies cell B11 or B12 with information found in either column G or H based on input in B1.
I have attached a spread sheet as example.
I know I can make a formula based on IF(B1=2013;G*;IF(B2=2014;H*;etc..
conditional formatting in Excel. I have two columns with pertinent information. I need to know the following and format accordingly:
1. Is the number in column A positive or negative?
2. Is the number in column B less than 0.05 or 0.10?
I would then like Column C to just be highlighted a certain color depending on the combination... there are 5 possible combinations and I would like the cells to be formatted so that:
1. Positive and less than 0.05 - Bright yellow
2. Positive and less than 0.10 - Pale yellow
3. Negative and less than 0.05 - Bright green
4. Negative and less than 0.10 - Pale green
5. This "combo" just means the criteria wasn't meant... which is possible b/c sometimes Column A may have text instead of a number of b/c the number in column B is not less than 0.10. If either of these is true, I want the cell to remain blank.
is there a way to combine the info from several columns into 1 list?
All of my columns have information in rows 1-20, but I want it all to automatically combine into a list in column B on a new sheet.
This is Excel 2007
[URL]
I have a workbook which serves as a master database at work. it contains two sheets: current residents of our facility (let's call this Sheet1), and those that have left/been discharged (Sheet2). It contains 87 rows and 34 columns of info.
I'm looking for a macro that will do the following:
When I click on a cell in any row (i.e. I need relative references) on Sheet1, the macro will (once activated):
1. Select the info between column D and column AH (inclusive) on the row where the selected cell is... i.e if I click on cell F4 before the macro is started, it will select the info from D4:AH4.
2. It will COPY this info
3. It will paste the info into the first empty row on Sheet2, starting from column C (i.e if the first empty row is 200, it will paste the info from C200:AG200)
4. It will then have a popup asking for: a. 'Date Left' and b. 'Reason', with two empty fields to input the info into. 'Date Left' is (obviously) a date value and 'Reason' is a text string. Once OK is hit on this popup, the Date Left will be pasted in column A of the same row (in our example, A200), and 'Reason' will be pasted into column B (again, in our example B200).
5. The macro will then go back to Sheet1 and delete the info that was selected between Columns D and AH inclusive (in our example, D4:AH4)
6. The macro will then save the workbook.
If I'm using this function to find the specific text "EXPIRED" in the specified cells, how can I add additional cell ranges on the same sheet to this function?
D17:D34 as well as F17:F34
=IF(ISERROR(MATCH("EXPIRED",Homepage!D17:D34,FALSE)),?"OK","ALERTS")
I have a questions regarding extracting records using a partial text search in multiple columns.
I would like to do a partial text search of the letter "j" in the Name column, and search for the number "1" in the Phone column and extract the corresponding records.
The formula I have been using is as follows: =IF(ROWS(C$2:C2)>$B$2,"",INDEX(C$2:C$6,SMALL(IF(AND(ISNUMBER(SEARCH("J",$K$25:$K$29)),
ISNUMBER(SEARCH("1",$L$25:$L$29))),ROW($J$25:$J$29)-ROW($J$25)+1),ROWS(C$2:C2))))
However, the formula is not working correctly.
find my sample data below:
A
B
C
1
Partial Text Search
[Code]....
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx‎