I am working on a workbook, it's not my workbook so I can't change how it's laid out.
I am trying to figure out a way that excel will check 2 different columns for a code that is manually entered, then enter the codes description in another cell.
It's basically a deposit and expense ledger.
One column has expense codes and names, the other deposit codes and names.
I want to be able to enter the code letter into a cell in the ledger and excel will enter the code description into another cell.
I have a spreadsheet that has a resource table, project stage table and an approx 50 different project sheets. (The 50 sheets are duplicated layouts, just different project names)
Based on the data on the first two sheets I would like to populate the individual project sheet.
For example:
1.In the individual project sheet there are 5 stages in each quarter. 2.The project stage table sheet tells you want stage the individual project is in for the relevant quarters. 3.The resource table sheet tells you how many resources are required for that stage.
Based on this information, I would like to populate the individual project sheet with the information.
E.g. If the project is in the 1st stage, it would then go to the resource table and take number of resources allocated for that stage and populate the “relevant” field in the individual project sheet with the correct value.
I've attached the spreadsheet to hopefully better illustrate this.
I have some data which i want to split, i have tried " Text to columns "on excel but couldnt find a way it works.In one cell my data is exactly like this:
Name of product Code of product
Loc.: here is the location expire date:
I would like each one of these itens on one column, like
A b c d Name of product code of product Loc.:here is the location expire date:
I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:
B B B-A B-A B-V B-V
Column B contains: THR THR-MATT HF-MATT HS-HS-THR HS-MATT
I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.
Today this data is inserted into one cell, in one column, but in 50 000 rows (in 9 different sheets!). I would like to separe it into separe columns so I could run calculations on the data. Separating it manually would take me at least 1 year,
I have a 45 page spreadsheet with over 3500 contacts and the data is currently listed in individual cells as seen below:
John Smith Director of Business Travel Sales ABC Hotel 1200 Market St. Philadelphia, PA 19107 Phone (215) 555-1234 Fax (215) 555-4321 jsmith@abchotels.com www.abchotels.com
I want to convert the each item [data] above into separate columns so I can then save it as a CSV file and then export th data into an email list; but I have no idea how do to this.
I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.
Example - Sheet "Gp Trg Plan" Column B Column D Tower Module 1 Tower Module 2 Tower Module 3 Building Module 1 Building Module 8 Street Module 6
Ideally the row will look like.
Example - Sheet "Status WS"
Column A Column B Column C Column D Column E Column F Column G Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date
There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.
I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the . Normally instead of that tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.
DSL Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.
D-VHS Connects a digital audio/video cable for use with some D-VHS digital VCRs.
E-Mail Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this: DCM1005-1 DCM1005-10 DCM1005A MTR1005-1 MTR1005-10 MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas) A1, B1, C1, D1 DCM1005-1, DCM, 1005, 1 DCM1005-10, DCM, 1005, 10 DCM1005A, DCM, 1005, A MTR1005-1, MTR, 1005, 1 MTR1005-10, MTR, 1005, 10 MTR1005A, MTR, 1005, A
I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.
creating a macro or implicating one into the workbook to run for future reporting.
i have a long column pair of data, each entry in its own cell:
10/5/20088:30:00 AM 10/5/20088:46:00 AM 10/5/20089:14:00 AM 10/5/200810:18:00 AM 10/5/200810:42:00 AM 11/5/20088:30:00 AM 11/5/20088:46:00 AM 11/5/20089:14:00 AM 11/5/200810:18:00 AM 11/5/200810:42:00 AM 12/5/20088:30:00 AM 12/5/20088:46:00 AM 12/5/20089:14:00 AM 12/5/200810:18:00 AM 12/5/200810:42:00 AM 13/5/20088:30:00 AM 13/5/20088:46:00 AM 13/5/20089:14:00 AM 13/5/200810:18:00 AM 13/5/200810:42:00 AM 14/5/20088:30:00 AM 14/5/20088:46:00 AM 14/5/20089:14:00 AM 14/5/200810:18:00 AM 14/5/200810:42:00 AM
how can i program a macro to 'split' this column according to date? please refer to the attached picture as an example. i know this is probably a simple question but please bear with me i'm still new to excel programming.
I have repetitive task of copying and pasting. Macro to sort some data.
I have time values (sorted in order) in Column A and a value in Column B.
Sheet1 Â AB1TimeValue 200:00:002300:00:002400:00:001500:00:002601:00:004701:00:002802:00:001902:00:0021002:00:0031102:00:0041202:00:0011302:00:002
I need separate the time each hour of data into columns. So the above would become
Sheet1 Â ABCDEF1TimeValueTimeValueTimeValue 200:00:00201:00:00402:00:001300:00:00201:00:00202:00:002400:00:001Â Â 02:00:003500:00:002Â Â 02:00:0046Â Â Â Â 02:00:0017Â Â Â Â 02:00:002
There are a different number of rows of data for each hour and sometimes there might not be any data for a specific hour.
I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:
The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.
A B C Row 1 November Row 2 1 5 15 Row 3 1 0 2 Row 4 0 1 3
November is in A3
If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2
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‎
I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.
I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.
U:U AQ AR AS AT AU AV to Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT Mary-------some text--------some more text John-------<blanks>---------just text here Sue--------just some here-----<blanks> Dave-------something--------something else Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.
But, they are not the same number of data. They vary.
Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.
Now on Tab 4, I want combine the data from all three tabs into one column (in column A).
So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.
Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears 9999 2004/5 5 4 9999 2005/6 7 8 9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
ID 9999 FY Apples Pears 2004/5 5 4 2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
I am in the process of creating a Phone Roster for my unit. On Sheet1 is their administrative data (Name, Position, Phone Number, etc.). What I am attempting to do is use a formula to find the persons name for that position and place it in the appropriate position and then their phone number below.
Admin!H:H contains the positions Admin!B:B contains their name Admin!N:N contains their phone number
I believe the following is what I need =LOOKUP("Operations NCO",Admin!$H:$H,Admin!$B:$B) and it works, but only for the first person on Sheet 2. After that it is random. If there a different formula I need to use to make this work?
When I get the phone number I used =LOOKUP("Operations CO",Admin!$H:$H,Admin!$N:$N) and the same thing happens. It works for the first person and everything else is incorrect.
A problem is the Admin sheet is listed alphabetically, not by position, so I cant use a specific cell to copy the data. I need the formula to find the position, then find the name and phone number of the person in said position.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
