Pull Data From Different Columns Based On Info In Column "A".
I'm having a little trouble figuring out what formula to use for situation. I have 5 columns of information.
Column A = School # of current school
Column B = School 1 Name
Column C = School 2 Name
Column D = School 3 Name
Column E = School 4 Name
What I want to do is in Column F to look through a row of information and find the current school.
So:
If A="1", pull from B
If A="2", pull from C
If A="3", pull from D
If A="4", pull from E
I tried doing a VLOOKUP table, but I think I'd have to do one for every row, but I could be wrong. I also thought of INDEX(MATCH), but I couldn't quite figure it out.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Hiding Columns Based On Other Column Data
I'm trying to hide columns based on information in another column. If that column contains "A" or "B", hide columns "U" through "W". If it contains "A", "B", "C", or "D", hide columns "V" through "W". Etc, etc., so on and so forth... I've snipped the code I found earlier and modified it to (what I thought) was correct for my application, but it doesn't seem to do anything. (btw, should I get an error if it doesn't run correctly?) Here is the code as modified: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "P:P" Then If LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Then Columns("U:W").EntireColumn.Hidden = True ElseIf LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Or LCase(Target.Value) = "C" Or LCase(Target.Value) = "D" Then
View Replies!
View Related
Pull Info From 1 Worksheet Into Another - Not A One-to-one Relationship
I have one worksheet with 238 rows and another with 163 rows. I want to pull information into the sheet with 238 rows whenever there is a match to a record in the 163 rows. I tried VLookup, but as soon as it gets to a record in the 238 that doesn't exist in the 163 it gives errrors and stops. I've looked at =offset and =offset(match) but I'm not finding anything that deals with the situation of not always being a one-to-one (or even one-to many) relationship. Ultimately I want to pull the Category & Project fields into the records matching on IDNumb in Sheet 1 but Sheet 1 has more records than Sheet2. example......
View Replies!
View Related
Column Availability Based On Other Columns' Data
In the sheet I'm currently working on I have a column (column C) that uses a validation so that users can select from a number of call types (data tracking in a call center). I allowed for an "Other" option, with a free-form column (column D) dedicated to the "other" reason in case the list doesn't cover the type, but I'm running into a problem where people use the other column to describe call types already listed. Is there anyway to lock the Other column (column D) unless Other is selected in the drop down in the Call Type column (column C)? The sheet I'm working with contains confidential data so I can't post it, but if this isn't clear enough I can draw up a mock sheet.
View Replies!
View Related
Formula Required To Pull Some Info From Cell
i have a list of names which also contain e mails addresses after the names, they are displayed as (In column B onmy spreedsheet) Armani Stevens/GB/companyname/GB@soso what i need from the above is just the name (up tp the first backslash) so i would need Armani Stevens and disregard the rest. What i would then need to do is to take the name and then see if the names is in the list which is situated in column A, so to sum once i have Armani Stevens extracted from Coulmn B i would want to see if this name is in Column A Names are obviously of all different lengths and there is always a space after the first name and surname
View Replies!
View Related
Vba Select Rows Based On Info In A Column, Then Paste Into New Workbook
I'm using excel 2003. I would like to select and copy rows of data within a worksheet of a workbook and then paste those rows into a newly created excel workbook. That is the easy part. The complication is that I would like the macro to select the rows based on a particular string value that will be impossible to specify in a fixed way (i.e., it will change from project to project). Let's say the variable that distinguishes the rows is location, so some are from Tokyo, some New York, and some from Paris. Using vba, how can one 'splice' the data into (in this case, three) seperate workbooks based on the string/values of another variable? I am familiar with auto-filter, but in this case, I really do need to create seperate workbooks, rather than apply calculations to a selection of the data.
View Replies!
View Related
Pull Found Records From Table Based On Single Column
I have the following data :- SHEET 1 COL A_______COL B________COL C V1990_______J100_________U1212 H2323_______Y999 U2222 SHEET 2 COL A_______COL B________COL C U2222 I0000_______U8900________T67888 H2323 I need to search data from range defined A1.C3 and if any data in that range found in the sheet 2 that having the same records.
View Replies!
View Related
Pull Data From Worksheet Based On ID Number
I have 2 worksheets (Sheet1 and Sheet2). Sheet 1 contains around 3000 records with multiple fields (columns). Column A contains the ID number. Sheet 2 contains some records with different fields except for the ID number which is common. I need a macro to retrieve information from sheet1 and copy it to sheet 2 based on the ID Number; i.e the macro needs to get the ID number from sheet2, locate it in sheet1 and copy the data from column D in sheet1 for that particular record and paste it in sheet2.
View Replies!
View Related
Get Pivot Data: Pull Out The Data From The First Column(A), And Then Average
I have a Pivot Table, with lots of data in, what I want to be able to do is on a separate sheet pull out the data from the first column(A), and then average out the figures from Columns(B-E). When this has been done on the sheet (with the average data) in the next column, I then want to use Column(F) (from Pivot Table), and do a simple sum to work out 5% of the difference if it is greater than ZERO between Average Column and Column(F) (from pivot Table) Column(F) must be higher than the average in order to work out the 5%.
View Replies!
View Related
Function To Pull Out Data From One Column To Another Without Duplicates
Here is what i'm working with: http://img90.imageshack.us/my.php?im...uestionas8.jpg what i want it to do is pull out the stuff in column C and put it in column F. But i don't want it to duplicate it at all. So for instance in this case, submarket 1 would be cary-morrisville-apex / submarket 2: northwest wake / submarket 3: north wake etc. would this involve a VLOOKUP function but an if then function as well? I'm not really sure how to go about it.
View Replies!
View Related
Link Cell From 1 Sheet To Another: Pull Data Based On Time
I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working). What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.
View Replies!
View Related
Pull Data Out Of A Closed File Based On A Date Range
I am trying to make Excel pull data out of a closed file based on a date range. The closed file is a log where people input data and enter the day they are doing it. I need to pull this data in my file and make it print into my worksheets so I can pass a report on to others with only the data they request from the log. The range would be for 7 days and would have to be able to choose a few columns off this report.
View Replies!
View Related
Pull Data From Closed Workbook Based On Cell Values
I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file. Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous months name and thus source file name will be placed on cell J1. The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.
View Replies!
View Related
Pull Column Data (Sheet3) From Master.xls And Paste To Column 4, Sheet4 Of WorkingSS.xls
Pull Column Data (Sheet3) from Master.xls and past to Column 4, Sheet4 of WorkingSS.xls I'm assuming this would be done with VBA or a really exotic macro. The Funky Part would be that the WorkingSS.xls file column data is being copied/pasted too (WorkingSS1.xls or WorkingSS2.xls ect) the file may be different every time so I would need an insert in macro or VBA to "Choose File Please..." then continue. The Master.xls workbook has spreadsheet lets say "Sheet1" in which I need all the data in Column A (except the header or cell A:1) copied TO WorkingSS1.xls on Sheet4, Column B, but Column B already has about 6000 rows of info, so I need it copied to the very end of (A:6001 although it will be different everytime) or the first empty cell at the bottom of that column. next another Column from Master.xls workbook lets say "Sheet1" again in which I need all the data in lets say "Column B" copied to the WorkingSS1.xls on Sheet4, Column F. Caveat this time is that the data needs to copied to the same row as the first copy/past. So it would be pasted into F:6001. Double caveat is that the Column F contains no other data except for what we are about to paste in. I have several more steps of automation to be done here but this is the beginning and a big hump I need to get past. The rest I think I can do.
View Replies!
View Related
Enter And Highlight Data In Columns Based On Other Columns
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are... 1. Vacation Type (will only have a text value of either "Cold" or "Warm") 2. Vacation Started (will always have a date *x/xx/xxxx) 3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank) 4. Number of Days (currently has ALL truly blank cells) THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE Here's what I would like the macro to do... Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column "Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column. The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED. Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED. OR
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................
View Replies!
View Related
Able To Enter Info From Columns One And Two And Be Able To Choose An Option
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.
View Replies!
View Related
Match Two Columns Across Sheets And Based On Criteria Of Another Column
This is driving me nuts. What I have is: Sheet1: ColumnB - number stored as text (Work ID) Remaining Columns: - mixed data, some columsn numbers, some text Sheet 2 ColumnA - number stored as text (Work ID) ColumnB:ColumnAW - mixed data, some columsn numbers, some text CollumnAX - Dollar Values What I want is in Column F (Sheet1) a formula to: if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True
View Replies!
View Related
How To Pull The Duplicate Values From 2 Columns?
I have this problem I have 4 columns which contain data. The first column is a list which contains random values from the third column. Now what i want to do is to pull the duplicate values from the second list without agitating the data from the 4th column because it is corresponding.In the example i have attached i would like to take the values from column A and find them in column F then extract them together with corresponding value from column G
View Replies!
View Related
Sum Column Based On Other Columns Year & Month
I have the following variables in these columns Column 1: Ship (1064, 1065, 1066 as the field contents) Column 12: Date (21-Feb-08 as format) Column 13: Weld Length (1000 as format) Column 15: Defect Length (1000 as format) What I need doing is the following is in a single cell per month add up what the total weld length is as well as the defect length as I have Jan 08, Feb 08 etc on another sheet where these values will be returned. There is a seperate sheet for each Ship so would like a formula that I could ammend 1064 to 1065 etc
View Replies!
View Related
Sum Of 3rd Column Values Based On Nested If On 2 Other Columns
I have three columns as follows: ItemsWeekEnding Count a14/7/2007 10 a34/7/2007 3 a14/14/2007 11 a14/7/2007 5 a44/7/2007 6 a14/21/20078 a44/14/20072 a34/21/20071 a24/7/2007 6 a14/28/20077 a24/21/20074 a94/28/20079 I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example: a1 4/7/2007 15 as a1 for this weekend is occuring twice. I need the formula to do it in excel.
View Replies!
View Related
Find Columns By Name And Highlight Them Certain Colors Based On Text In Another Column
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are... 1. Vacation Type (will only have a text value of either "Cold" or "Warm") 2. Vacation Started (will always have a date *x/xx/xxxx) 3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank) 4. Number of Days (currently has ALL truly blank cells) THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE Here's what I would like the macro to do... Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column "Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column. The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED. A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED. OR..............................
View Replies!
View Related
Pulling Out Info Based On Text
I have a regular table of data and dependent on the info within a column of cells I would like the info to appear within one of 3 new sheets. BUT.... I can't figure it out because the info forms an irregular part of text within the cells in a specific column - Obviously if it was dependent on the whole of the text I could use Vlookup but it's not. If it needs further clarification, a column of cells may have, say, "blahblah234/PLblah" -> If this cell contains within the text "PL" then it should have the whole of the row in the new sheet, if not then blank.
View Replies!
View Related
Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in. I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View Replies!
View Related
Color Column Chart Columns Based On Thresh-hold Line
I want to show 8 members of staff's names, with their sales closing percentages. I want then to produce a chart showing their percentages as a column with a line showing 16% going across the chart showing who is above and who is below the target. Ideally i would also like to change the colour of the columns or background of those below the line to show who is under target.
View Replies!
View Related
Convert One Column Of Data To 257 Columns Of Data
I have exported a text file from a universe database and need to import it into excel. Below is an eg of one section. The text file contains approx 257 of these, all one below the other. Between each set of dotted lines is one user profile (not data all shown in eg) . Each one (user) is NOT the same amount of lines(rows) deep and only the dotted lines separate each user. A simple import places all data into one very long column. I need a way to place each user in there own column. I have user names as column headings the labels of the values below as row headings. I cannot change this. Manually entering the data took a very long time and i need to update this workbook regularly. In the text file, " the variable......: value " (eg UserCode.....: XYZ) sets are all lined up so that using a text editor, with column mode, I can delete all the data headings/labels up to the space after the colon. This leaves me with 7000 rows of values (eg XYZ) one column wide and various numbers of rows deep per user. Is there a way to import the data and seperate each user via the dotted lines or other so that each users data is contained in consecutive columns. 1 user profile per column. The standard import utility allows for column placement (delimted, fixed width). Can this be done on rows, sort of flip it 90 degrees and use the dotted lines as column markers. Just guessing now. Any and all assistance would be most appreciated. Another issue (not as important but still helpful) is that the "procudures barred" section of user profile can also be one row(line) or serveral. Problem is each Name...:Value set must be on one row(line) for everything to match in the work book. Using custom macros in my text editor i moved all the 'proc. barred' data to a single row. Alot of manual editting was involved and any ideas on this area would also be muchly appreciated. One EG user profile (not shown, but all the colons line up) ....
View Replies!
View Related
Combine Data Based On Data In 2 Other Columns
I have data in columns A:D with the headings Proposal, Sort_Code, Seq, Comm, and data in row 2:???, it is in the format below, / is to show a new column, I want to look at the proposal column (A) and the sort_code column (B) and then take all the data that is associated with it in the comm column(D) and combine it in column E on the first row of the proposal number. A Formula or macro would work for me. 0733596/001/1/line 1 0733596/001/1/line 2 0733596/001/1/line 3 0733596/001/1/line 4 0733596/002/1/line 1 0733596/002/1/line 2 0746715/001/1/line 1 0746715/001/1/line 2 0746715/002/1/ 0732387/001/1/ 0732387/002/1/line 1 0732387/003/1/..........
View Replies!
View Related
Counts Info Based On Multiple Conditions
I am trying to get a list which counts info based on multiple conditions but I can find no way round this one point I have a sheet which has all the games played in a particular division and the time that each goal was scored and I am trying to break down the following What I want is a list that has a column that has the result of how many times when team A were playing at home was a goal scored between the 45th and 65th minute column A = Home Team B=Away Team C= Time of First goal D=Time of Second goal E=Time of Third etc I have a countifs in place for counting the home team and counting if the first goal was scored in under 10 minutes (this is simple as obviously all the first goals are in column C) but what I want to do is be able to have a count of how many times a goal was scored between x number of minutes eg between 40-50. My problem comes when trying to count the goal times as some matches will have no goals and the cells will be blank and others will have multiple cells filled and the figure I want could be in any column and I also only want it to count only the amount of games a goal was scored between those times so if a game had 3 goals scored in between that time I only want it to count 1. The sheet has a lot more columns but this is the main sticking point.
View Replies!
View Related
Deleting Rows Based On Info In Certain Cells
I have a column that has different SIC codes in it (00045, 00046, etc). I would like to set up a macro or filter that would take cells in this column and delete each row that contains a certain code that I don't want. Example, I want to delete all the rows with SIC codes that have 00345, 00873, 00145, etc in a particular cell. I would like a macro that would look at each cell in the column and delete the rows that I don't want.
View Replies!
View Related
Average Based On Data In Two Columns
I am trying to include numbers in an average if two separate columns prove true. For example, I want to include all numbers in column "C" in the average if the cell in column "A" matches a name and the cell in column "B" is equal to the number 1.
View Replies!
View Related
Count Data In Column X Based On Conditions In Column Y
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2". In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
View Replies!
View Related
Formula To Move Info From One Column To Another
I need a formula that will take the value of one cell and put it in another cell other than the one in which I am typing the formula. I can't think of how to do this. I have two columns, A & B (no headers), of information. The columns are not identical, and column B has empty cells scattered throughout the column. For each empty cell in column B, I want to copy the same row of information into it from column A, as if I'm just scooting the information in column A that's on the same row as the empty cells in column B. This is simple with a cut/paste, but when there's thirty empty cells, cutting and pasting takes time. (What's really frustrating is that I've figured out a few formulas that will copy the information from column A but all the information gets pasted in the column in which I'm creating the formula (say, column F, for example). THAT doesn't help me because the empty cells in column b are still empty!)
View Replies!
View Related
Deleting Rows Based On Columns Data
I'm trying to write a macro that will look at a table of data, and if column J is blank, it will delete the entire row and shift the data up. Also, if there is a "C" in column C, it will delete that entire row as well. I tried the code below, but for some reason it is not deleting all of the rows that it should. Is there some kind of problem with the code that I'm not catching? It looks like it should work perfectly...
View Replies!
View Related
Import Comma Delimited Info In A Column
I have hundreds of email addresses listed in a Wordpad file- entries are separated by a comma. When I use the Excel Import function, all data is successfully imported into an Excel spreadsheet- BUT in a single row. I would like to have this info in a single column (A)- then I could easily alphabetize the entries and eliminate duplicates.
View Replies!
View Related
Sort Info In Column Into 50 Row Blocks
I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here... 2 2 2 2 33 33 33 4 4 4 4 4 4 etc. Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no. so that it looks like this 2 2 2 2 46empty rows here.......................
View Replies!
View Related
Find Value And Then Copy Info From Previous Column
I have a spread sheet with bunch of rows and columns. Columns goes all the way from A ... BB and rows from 1 to 40,000 The main focus is column Y and AC If there is a value of FALSE found in column Y then macro should go to previous row and copy the value that contains in column AC and then move down to the next row, , where the value FALSE was found and that's in column Y and paste that value in column AC the NUMERIC VALUE Below is the data ...
View Replies!
View Related
Copy Multiple Rows From One Book To Another Based On Info In Cell D
I have a workbook with a single worksheet that has about 2000 rows, columns A, B, C, D, E & F Cells in colums A, B, C, E & F all have very different information in them, nothing is similar in any of those columns that I can base a criteria on. Cells in column D however will have 1 of about 18 possibilities in them What I want to do is have another workbook with 18 worksheets, each worksheet named 1 of the 18 possibilities, and somehow magically pull the data from the first workbook and insert it into the correct worksheet in the second workbook, leaving the data in the first workbook intact. I update the first workbook several times a day, adding and deleting from it so would need to update as it goes, or be able to run the update as and when needed. My skills are limited to simple formulas inserted into cells and dragging them down!
View Replies!
View Related
Split Data In One Column Into Two Columns
For each cell in column A, I am trying to split the data between two new cells. The data in each cell is separated by a |. All information to the left of | should go in column B. All information to the right of | should go in column C. Column A Aa3 |AAA A3 |AA- A2 |A A3 |BBB+ Aa3 |AA Aa2 |AA+ Desired Results Column B
.Column C Aa3
AAA A3
..AA- A2
..
A A3
..BBB+ Aa3
AA Aa2
AA+
View Replies!
View Related
Trying To Build A Macro Filter Based On Data In Either Of Two Columns
I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!). I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.
View Replies!
View Related
|
TRACKER
