how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?
I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:
A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...
What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with
A1: A-dec International Inc. A2: A. Bellotti A3: A. DEPPELER S.A.
I have 300 rows worth of data that looks similar to this, all organized in one column:
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Snider Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.
TSB..... in left column and NS...... in right column. This example includes 3 items as shown above but the single cell could contain more or less than 3 items
What I got going on is a program I use for reporting is shooting out information.. while most of the information it sends to an excel workbook is sectioned by cell, there is a good chunk of information that's dumped into 1 cell. Is there a macro, or something, I can do to separate the information in this single cell, into separate cells? Auto Merged Post Until 24 Hrs Passes;Here is an example of the report. You'll notice a bunch of information in the 'Description' section. What I want to be able to do is run a macro that will seperate the 'Item Number', 'URL', 'Serial Number', 'Model Number', and 'Details' information into seperate cells.
I'm doing our training schedule for 2010. Some course we have are multiple date (ie. September 12 - 13) some are singular (ie. January 9). We have a date column. For the single dates it automatically converts to date format. For the multiple date is stays as text format. this is very confusing when we sort by date as every course that has multiple dates doesn't sort properly (because it's in text format). Is there a practical way to go about doing this, so if someone were to sort by date it would would sort properly -whether it's multiple or single dates? Let me know if anyone has any ideas. I'd prefer not to have 2 columns - column 1 being (course start) and column 2 being (course end)..but not sure if this is possible.
I have a data table on worksheet "Data" which has 2 dates (start and end) stored in columns in yyyymm format: (example 201204)
I also have a row/column table which is supposed to sum up balances which match both the start date in the column, and end date in the row, so that all unique combinations of start and end dates will have their own cells.
I have a file which contains a list of all employees and the extra vacation time they have purchased by manager. I need to create a separate workbook for each manager saved as whatever is in the Manager Field, in a folder that I specify. I would like to have a VBA macro that does this.
No cells contain formulas, but I do need the formatting to be the same.
I have attached a small sample of the file that needs to be split. Since there are three managers in this file there should be three workbooks created.
I am receiving a new report from an external system. Unfortunate the system delivers many information within one cell, and it is not possible to reconfigure. These information are separate by a semi-colon or a semi-colon + space. separate the information in separate cells?
I have Column D containing multiple values that I need to split. The structure is:
Text Text (XYZ) 123 AB C Text 456 C Text Text (RST) 7 A CD EF Text 22
I'd like is to split the cell using a macro rather than formulae so that the resulting cells can be filtered.
The output would be:
Text Text (XYZ)|123|AB|C Text|456|C Text Text (RST)|7|A|CD|EF Text|22|
So the first split would need to be when the number is reached, the second split after the number and then subsequent splits each time a space is encountered (although there may not be any values after the number!).
I was just not able to figure out how to write a macro for splitting the cell data into multiple rows with the other column values being duplicated. Just elaborating my problem, its like i have a column in which i have multiple values separted by a comma, So i need to divide all these values on basis of a comma and then copy this value to a new row below, along with the previous column values same for this new row. in my file cost center column is to be worked upon. Can this be done by a macro ?
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project Benefit Type Delivered or Enabled Benefit
PJ1 Financial Delivered Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered Financial - Enabled Tech - Delivered Tech - Enabled Green - Delivered Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows
Example (I have the below in a single Cell as column headers) NCM Server Mgmt VLAN Site ID
Next Line down is the data (Each row in a single cell) Enabled 10.10.10.0 50 TEST SITE 1 Enabled 10.10.20.0 50 TEST SITE 2 Disabled 10.10.30.0 50 TEST SITE 3
How I could achieve this as I have a number of projects where this would become useful
I know you can use delimiters but with spaces between the values I just can't fathom a way forward.
I have a situation where I have source data formatted like this:
Document ID Document Name Author
[Code]....
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
I have to take a range of phone numbers that I have to port from another carrier and manually type them out or copy into another speadsheet, removing duplicate numbers. For example, I have the following numbers:
I am trying to do something that sounds fairly straightforward but have not been able to achieve the final result.
I have a sheet which has multiple range addresses stored in different cells and so far I have been able to pass the Range address to Range variables by looping through a range array. Now I would like to select these multiple ranges. My code looks as below;
VB:
Dim Cnt As Integer Cnt = Sheets("Sheet1").(Range("1040").Value ' Cnt holds the the total number of ranges to be specified in Sheet1 Dim RStr(1 To 80) As String ' This will hold the range addresses specified in Sheet1 in cells C1041 to C1120. Upto 80 range addresses could be specified. [SIZE=2] Dim Rng(1 To 80) As Range ' This will store the ranges based on the range addresses stored in RStr() Dim i, j As Integer,
[Code]...
I am not sure on how to select multiple ranges using range array hence tried Union() however it is obviously not correct and throws an error.
Find Multiple instances of Numeric Criterion in Row & Return To a Single Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows. Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across each single Row in the Dynamic Range "Data" and have the Results returned to a New Sheet in a single column.
NEW Sheet: The Numeric Criterion is housed in G5. The matched criterion should be returned to the New Sheet starting at G7. Duplicate instances in the same Row should ALL be returned to the same cell in Column G on the New Sheet.
Sample Data Layout: Columns I J K L M N O P Q R Row No.76 1 0 1 1 0 1 1 1 0 1 Row No.77 2 2 3 2 1 2 2 0 0 0 Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario: Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet: Row No.7 Column G (Cell G7) 1111111 Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be returned to the same cell G7. In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it should be returned to cell G8.
I am looking for creating a dynamic meeting log/calendar for my team. There's a couple of elements to my questions below:
I have a log with columns such as "Meeting Date", "Client", "Attendee" and have also set up month calendars on separate tabs which automatically update the dates according to a "Year" toggle using Janoffset, Feboffset, etc.
What I would like to do is have these calendars automatically populate a cell beneath each date with any meetings on that day. The client name and then a space then the attendee in brackets would be sufficient and with each meeting having its own line in the cell).
I have gotten a Vlookup working but have run into two snags: 1) where there is more than one meeting on a single day 2) I can only return one piece of information in the cell or otherwise have it blank, I can't have the client name AND attendee.
Is there any way to lookup all of that information in the one cell or is this just a pipe dream?
I have attached my workbook which contains my workings so far and in the first cell of Jan shown the "ideal" format. I'd like the data in.
The following IF statement isnt working, so im missing something. If A2 says YES, i want the cell with this statement to be blank. However, if A2 has a date, I want this cell to be that date + 45 days. How can I make this correct?