I have one column of data, I would like to separate the ODD and Even row values into two separate column, such that the data will appear staggered.
ie. this is my data in column 1
1
2
3
4
5
6
7
8
would like to separate it into columns 2 and 3 as follows
1
.....2
3
.....4
5
.....6
7
.....8
I believe I need to use the ISODD, ISEVEN function somehow but can not figure it out.
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 have one column that has forename and surname information and I need to separate the surname into a separate column next to the forename.
Is there a formula for this?
I've tried the formula where you can request to move so many characters to the right, but it's difficult with all of the surnames being a different number of characters.
I am working with an excel spreadsheet. There is a column for "name", and then the next column is for medical condition.
If the same patient has multiple (say, 4) conditions, the rows for "name" are combined into a single cell(let's say, a cell going from row 2 to row 5, witha a single name in the combined cell) , with the next column having four different cells adjacent to the single cell (cell 2 has "bleeding", cell 3 has "fever", cell 4 has "diarrhea", and cell 5 has "rash"). Some patients have only single conditions, single cells.
Will this mess up COUNTS and COUNTIF?
Is there a way to split the combined cell2-5 for ''name" into four cells all containing the same name?
I know how to separate data into different columns i.e. Marry Johnson into two separate columns; however, I have data in different rows that I need separated into different column. See screen cast [URL]
Using excel 2003 on windows 7 64 bit
I'm back working on my estimate sheet again and hit another roadblock. I have a series of rows all separated by multiple spaces and would like to copy every one to the first empty column on a separate page sequentially until a certain condition is met (first time row starts with zero in column U, in this pic second row down would end routine).
The first column here is U on sheet "Partitions & Woodwork" so since this first row doesn't start with a zero, U10 - BC10 would need to be copied and pasted transposed into the first open column on sheet "Rebirth" (2nd pic below).
U V W X Y Z
It would be pasted transposed here from B2 downwards on sheet named "Rebirth". The next row that didn't start with zero would be pasted transposed starting at c2 and so on until the first time a row beginning at column U on sheet "Partitions & Woodwork" began with a zero (0).
The number of spaces between rows being copied varies on the partitions & woodwork sheet but the columns (U - BC) are a constant every time a row needs to be copied.
ID Name QTY Price
1 John 5 15
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125
1 Tim 7 26
4 Sue 10 100
I need macro to loop through all data and generate this table in another worksheet
1 John 5 15 Tim 7 26
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125 Sue 10 100
Each data in a row in the new table will occupy a different cell.
I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:
Pounds
=SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)
Pence
=MOD(SUM(E3:E8),100)
These work very well and give me the correct figures.
What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:
When added together the values of rows 3,4 & 6 are 7.25 (based on a value of "W")When added together the values of rows 7 & 8 are 5.00 (based on a value of "F")
I have tried to show this as an example in the image below:
A
B
C
D
E
[Code]....
I'm using Excel 2010 on Win 7 Pro.
I have an interesting problem I've been presented with and rather than try formula after formula I thought I'd propose it to you all (see attached sheet).
I have 7269 rows with 1930 distinct claims. I need to pull out the Primary issue (done that) then across from it put in each and every secondary issue (from col C). The largest # of claim lines is 89 (see E1). So in theory I need to find that claim and put all the secondary issues in the next 89 columns from row 4990 beginning in col H.
I've done a couple examples of what I'm looking for in rows 2, 4 and 5 and 8 but beyond that ...
So every place where there is a value in col F I'd like the list of secondary issues in the same row.
I have an excel sheet that I'm trying to format. Basically, the excel book contains sheets (with the months), and each sheet contains a list of clients, their package, their status, date assigned, and date updated.
I've made another sheet (Sheet2) that fetches everything and consolidating it to one sheet. Sheet 2 also cleans up the data to display only the items that are 'completed'.
The issue I'm having now is in splitting the list (assuming it has been cleaned up to show only the 'completed' items. I would need to split the list first by the date they were updated, and then if they were assigned the same date or not. In short:
--Get all items with date updated = Month1
----If date assigned = Month 1: List items under table 1
----If date assigned < Month 1: List items under table 2
I've used macro on the excel (and I'm not sure it's efficient enough. I'm not proficient with VB, so...). I'm not sure if it will work on Mac.
I need to separate out some rows by date into new worksheets, but I need the dates to be a range. Our cycles are separated into periods, which last about 1 month, so I need one new worksheet for each period. I was thinking about having like a cover sheet that defines the exact dates (like column A has the period title "P1, P2, P3,..." and column B has the start date and column C has the end date).
Then on the next sheet is where I would keep my Input data. So I need to separate the rows of the input data sheet out by the periods. The date for each data entry is kept in column M. I have attached a sample workbook explaining what I tried to describe above.
I need to separate multiple values separated by comma in a single cell into individual rows. However, I also need that the data in the adjacent columns of the original cell also to be repeated in the new rows. The problem is illustrated in the attached file.
that the table is just a sample and most of the tables I work with contain at least 15,000 rows,
I have a column within a spreadsheet that has data separated by 2 delimiters, a "" and a "/". (This data has been extracted from a linux-based file.)
For example: "1100789/3200899/6xlm-sgt-455-0987"
The items of data are from a bill of material (parts explosion) and the number preceding the "" is a quantity and the numbers preceding the "/" is a sub-part number of the main part number that is entered into a column to the left of this data string. (unseen in the example)
I need to take this string of numbers and place the quantity in one column, the part number in another column, then add a row and continue to populate each column until the data has all been separated, then move on to the next main part number row to continue the process.
for example:
1 100789
3 200899
6 xlm-sgt-455-0987
Is this possible with the data tools in a spreadsheet, or will I need to write a macro?
I am trying to divide one column of data into three. I can't change the order of the data because it is set up as a schedule at my work.
Here's how the info is sent to me, lets say columnA Row2:
500001
500003
500002
700002
700003
700001
800003
800001
800002
Here's what I need my spread sheet to do.....lets say in column's C, D, and E (all in Row 2)
C D E
500001 700002 800003
500003 700003 800001
500002 700001 800002
I have several large excel spreadsheets with different pieces of information that appear in a single column and I am looking for a way to seperate the data into multiple columns. I have read many different articles and how-tos and none seem to help with what I have...here goes:
I have a column that has a few thousand lines of info that looks like this:
Acme Stores
Name:
Smith, Steve
Reed, Tom
ShopRite Stores
Name:
Stadler, Fred
O'Neil, Tim
etc.
So, its a repetition of the store name, then a cell that just says 'name', followed by the people names associated with that store.
I am looking to somehow separate out the store names, leaving just the people's names in that column - but keeping the store names in a seperate column so the people names can be associated with the store name. The only thing that I think might be able to help, is that the 'name' field is constant and its always the cell below the store name. I don't know if that is something that is even remotely helpful here, but thats the only thing I can think of if someone knew some type of conditional command that would pick out a previous cell from one with the word 'name' in it.
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
im currently working on a project that needs to consolidate the data per given date. but the thing is, all of them are located into one column. i was able to separate each category into separate columns but now, i cannot align the data on a per date basis.
View 10 Replies View Relateda way to separate out data that has been all entered into a single column
code .....
The above information is a sample of the data. The data has been entered into 1 column, column A.
I want to separate the entries in to 1 column each for vehicle ID, full journal description, actual journal, EE code, Report total and equipment description.
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 been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
See the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.
I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.
I need to look up the name in E2 in the list in column A and if it matches then lookup name in F2 in list column b, if it matches then the corresponding number in column C is displayed in column G. If neither names are in the 2 columns the words"Not on lists" is displayed in cell in column G.
The other problem is one name is spelled two different ways I want it to look for both spelling before moving on to looking up the second name.
I started with this formula but I'm getting #N/A or "not on list" when they are on the list. I'm using ranges prod_sum is columns AthruC, Last_name is range BthruC. =IF(AND(VLOOKUP(F4,prod_sum,3,0),VLOOKUP(I4,last_name,2,0)),"not on list")
I am trying to write a formula to read the cells in column H individually if cell is blank write no to corresponding cell of column I, if the cell has any sort of data write yes to corresponding cell of column I
Currently H uses this formula ='name of column from sheet 1'!P:P
I imported data into Excel. The first row is a header row in A1 it is fine until the next header row in A12, A23, A34 all the way down to many rows. The problem is the header rows are overlap with other data by seperating with couple of spaces. I need a VBA to move a whole header characters to it own row just right after couple spaces. See the attached for more detail.....
View 2 Replies View RelatedI want to separate the following words into two rows. As the length of each word is different, so i could not use 'text to coumns' function.
for example:
Word1 : the board of directors of abc company limited (len = 45) in cell u13
Word2 : the board of directors of abcdefgh company limited (len =50) in cell u14
I use the formula to separate word 1 into two rows: return #VALUE
1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16
But i use same formula to separate word2 into two rows : successful
1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18
Answer : 1st row : the board of directors of abcdefgh company
2nd row : limited
I want a formula to do the following. Count all instances when column A contains S or A and column B = 1. The example below would equal 2 (ONES MEETING CRITERIA ARE IN BOLD).
Example
N 2
S 1
A 1
D 3
X 2
Need to adjust the length of a countif formula from a separate cell
something like this ?
=COUNTIF($BB$23:$BBH21),">0")
H21 being the cell i will change value to indicate rows
I have a long textBox with multiLine = true and enterKeyBehavior = true. I need for each new line in the textBox to go to a separate row in the worksheet. Right now the data is copied to the next row in the worksheet, but all of the data in the textBox is entered into one cell. Is there a way for the lines to go into separate cells?
Example of what i am trying to accomplish:
TextBox:
|------------------|
|blabla in line 1 |
|blabla in line 2 |
|blabla in line 3 |
|etc................ |
|etc................ |
|_____________|
then the VBA code would separate the lines into separate rows:
Worksheet:
Row 1: blabla in line 1
_______________________
Row 2: blabla in line 2
_______________________
Row 3: blabla in line 3
_______________________
Row 4: etc..
_______________________
Row 5: etc..
Let's say I have a document with 10 different sections in it. I download it from another piece of software so there is a header/etc on each page. Each page has the name of a company on it in the A column and the name of a section on it in the cell directly below the company name. (Lets call the sections candy, toys, medicine, etc.) I want to separate the information by creating a new sheet for every section and name it toys_sheet, candy_sheet, medicine_sheet automatically. How could I do this?
View 4 Replies View RelatedI'm using a fairly large spreadsheet to put all the cost and benefits of a large area development phased over different years and then calculate the NPV of the total project. The costs/benefits are on separate sheets and are divided into categories with headers. Every category has a summation row as last row.
Because the project is divided into subprojects I created a column which has a dropdown box (using the Validate function) in which I can attribute that cost/benefit to a certain subproject. Using IF functions and another dropdownbox on my Overview page I can get a insight in the total cost and benefits of the subproject I select in the dropdown box.
So far so good of course, but what I really want is not only to be able to get the Overview page per subproject, but also the Cost and Benefits pages. I was thinking about putting a filter on the column which has the dropdown boxes with the subproject number in them, but when I select a number I want all the headers and "summation rows" of all the categories to freeze/stay in sight, because otherwise the output of the filter is useless (for printing and evaluating) ...