Uneven Data To Separate
Dec 13, 2012
I want to separate data in cols A,B,C to match my results in cols D,E,F,G .
Have tried using left , mid , right functions but data is not evenly matched .
The most important parts I need is the results in cols D , E and G
Sheet1 ABCDEFG1ABCResult AResult BResult CResult D23SurvivedJonathan Riddell3SurvivedJonathan Riddell 37RacquetorCraig Grylls7RacquetorCraig Grylls 42KodemaOpie Bosson2KodemaOpie Bosson 56-Clutha Lad-Shane Dye(2.5L), 6Clutha LadShane Dye2.568-Viceroy-Johnathon Parkes(3L), 8ViceroyJohnathon Parkes3711-Omana-Rory Hutchings (a)(5L), 11OmanaRory Hutchings589-Sunbeam-Matthew Cameron(6L), 9SunbeamMatthew Cameron6912-Zulou-Kelly Myers(7.8L), 12ZulouKelly Myers7.8105-The Sheriff-Hayden Tinsley(12.6L), 5The SheriffHayden Tinsley12.61110-Inazuma-Bridget Grylls (a3)(12.9L). 10InazumaBridget Grylls 12.9
View 6 Replies
ADVERTISEMENT
Oct 10, 2009
I would normally sort out using Text to Columns, but I can't in this case as the remaining elements can vary.
I have a list of names with Forename and Surname and then the elements after that vary.
Names are written in one cell under a single column like so:
John Smith 13/04/2000
James Smith 123 123 1233
Jane Smith 07122 12345
Another Smith AB99 9CD
All I want to do is end up with 3 columns of data,
Forname | Surname | Other
Is it possible to use a formula to make the breaks into each column at the first 2 spaces only, therefore the contents after the second space would just end up in one cell?
View 9 Replies
View Related
Aug 1, 2014
I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.
The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.
If the 1st digit in the Category is a 1 = Water.
If the 1st digit in the Category is a 2 = Waste.
There may only be a water spid or a property may have a water and waste spid.
What I want to do is have the information on 1 row rather than 2 rows
I have data in this format
SPID
Core
Category
Service Category
100000000151
100000000
151
1
[code].....
and I need to convert it into this format.
Core spid
Water
Waste
100000020
100000020101
100000020251
I have tried IF(lookup) and it works for the 1st row but I can't get it to look at the 2nd row.My data consists of 400,000 plus rows
View 3 Replies
View Related
Apr 23, 2014
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.
View 9 Replies
View Related
Jan 5, 2009
In my procedure I have a for ... next loop with "For i = 1 to Number"
How can I determine the value of i during this loop.
Whenever i is uneven some cell have to change color, if i is even cells remain unchanged.
I have most of the code figured out, except for this part
View 8 Replies
View Related
Jun 25, 2014
What I need to do is have the numbers on the x axis count up with a even increment. For example, 1,2,3,4,5. That part is easy and I can do that, the next part is not.
Next I need the X axis of my graph to have, what the graph I'm trying to replicate calls, a "squared scale". So the numbers need to look like what is above but be spaced in squared intervals.
It would look something like this: 1 2 3 4 5... note the spacing between each is increasing.
Below are pictures showing how the book does it by hand.
20140625_085909_zps204851fe.jpg
20140625_085922_zpsbc03f1bd.jpg
I think I could get it to look like this with a dummy series with the axis labels listed but I need to be able to have a point read off the actual values so the dummy axis wont work.
View 5 Replies
View Related
May 13, 2013
I have a spreadsheet which contains 6000 rows where column A1 lists a name, B1 an address, C1 an amount and B2 the 4-digit zip code and place. Now I want to move the content of B2 to D1. I can't use offset because the spacing of the zipcode cell is not always the same.
View 4 Replies
View Related
Apr 21, 2006
I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.
I am attaching a sample file which best explains what I am trying to achieve.
View 9 Replies
View Related
Nov 7, 2006
formula in excel to calculate payback and discounted payback periods with uneven cash flows.
EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444
View 8 Replies
View Related
Feb 24, 2009
Finding the Column with the MAX Height in comparison with other Columns of UNEVEN Height
I need two things :
1. I have several columns starting from Column B till Column F, each column having values starting from the third ROW.
Ex:-
Lets say Column B contains two values in B3 and B4, Col C three values in C3 C4 and C5, Col D four values D3,D4,D5 and D6, Col E two values in E3 and E4, Column F five values F3,F4,F5,F6 and F7.
So, the answer is F3:F7.
2.I am using 10^{4,3,2,1,0} in a particular portion of a formula, Now the number of elements in the array or in the Curly Braces depends on the number of Columns filled from Column B as explained in the Point 1.
Now, since I have five columns under consideration I have this order as mentioned here {4,3,2,1,0} , I would like to know whether I could make this dynamic, as in if there were only four columns then this would be {3,2,1,0} and if more this array could self-fill and expand..
If that's possible, then how do we use it in the formula, Is it by the virtue of the INDIRECT function?
View 14 Replies
View Related
Jun 30, 2014
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
View 5 Replies
View Related
Dec 3, 2013
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies
View Related
Nov 20, 2012
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
View 5 Replies
View Related
Oct 9, 2013
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
View 6 Replies
View Related
Feb 13, 2014
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
View 6 Replies
View Related
Feb 25, 2013
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
View 4 Replies
View Related
Nov 21, 2006
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.
I want to do this automatically.
I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).
Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.
Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet
There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
View 9 Replies
View Related
Nov 12, 2009
I've imported a large document to excel. It contains 118 rows of information. My problem is that all the information is in column A. Every cell contains long sentences with information. The information is divided by a comma-sign (,). What i want to do is to seperate the information by columns instead of a commasign. So for example:
...A....................................................B....C.....D
1 Div,Date,HomeTeam,AwayTeam,FTHG
Becomes:
......A....B ........C.............D..........E
1 Div Date HomeTeam AwayTeam FTHG
Is there any smart way to do this??
View 3 Replies
View Related
May 22, 2013
how to separate .csv data into different columns in excel?
e.g this .csv data
2012.09.07,07:00,125.78800,126.12500,125.76800,126.02300,6664
2012.09.07,08:00,126.02100,126.19800,125.93900,126.05000,4707
2012.09.07,09:00,126.05100,126.11300,125.96700,125.99800,4178
2012.09.07,10:00,126.00000,126.02900,125.71700,125.77800,6389
2012.09.07,11:00,125.78300,125.91400,125.62800,125.66400,5388
2012.09.07,12:00,125.66300,125.86900,125.04100,125.12300,10447
[Code]....
View 11 Replies
View Related
Jul 22, 2014
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:
View 11 Replies
View Related
Aug 2, 2012
C5=ACDefg-0087HzY
In D5, I want 1st 4 letters (EXACT)
In E5, last 10 letters (EXACT)
How to accomplish?
Ans
D5=ACDe
E5=gf-0087Hzy
View 2 Replies
View Related
Jan 28, 2014
I have a huge data and want to know the best VBA or formulas to separate to consecutive columns.
Example :
BERNATTE QUIGLEY 10 WESTBOURNE ROAD STOCKTON HTH WA4 6SE
J QUINN 100 CHRISTOPHER TAYLOR COURT FLAT 18 B30 1ER
JOAN QUINN LITTLE COURT CT6 6PT
PAMELA QUINN ROAIN BANKS COTTAGES CT4 5PU
[Code] ..........
View 3 Replies
View Related
Mar 28, 2014
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.
View 11 Replies
View Related
Oct 31, 2009
I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.
The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.
I was wondering is there a formula that will collect the data automatically.
As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.
Is there a formula or something that will enter the per day data?
I have just added some false data to show you how some things get recorded.
View 14 Replies
View Related
Jan 3, 2014
I'd like to copy information from a PDF into a worksheet. The information looks like this:
1 PAOZZ 5310-00-918-0482 80205 NAS1291-7 NUT.............................................................................. 1
2 PAOZZ 5305-00-995-2125 80205 NAS603-7P SCREW.......................................................................... 2
3 PAOZZ 5305-00-866-0937 80205 NAS603-8P SCREW.......................................................................... 14
4 PAOZZ 5306-01-106-8238 80205 NAS6603-3 BOLT ............................................................................ 8
When I paste it into excel it all gets entered into one column, I'd like to spread it out over 7 columns. Is there an efficient way to do that?
View 2 Replies
View Related
Jul 16, 2014
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..
View 4 Replies
View Related
Feb 10, 2014
I got a column of data in format as below:
Column 1:
ABC - AC123
ASD - DS335
BBC - KK311
SWT - TR735
TIM - DN849
Between two texts to be separate, there is a " - ", we can separate them with that symbol right?..The cells should be look like after separate shown belows:
Column 1:
ABC
ASD
BBC
SWT
TIM
Column 2:
AC123
DS335
KK311
TR735
DN849
What is the macro code to do this
View 3 Replies
View Related
Jun 26, 2009
I would like to put together a macro that would compare a list of part numbers against a database, and recommend alternative vendors for the part. The actual VBA I think I can handle, my problem is with the formula, array or whatever is needed to return the results I am looking for.
I have attached a sample workbook to this message that contains two worksheets. The "Sample Database" is an example of the format of the database (in Excel format) that I will be looking against. This data may be a hundred columns wide, and thousands of rows long.
The "Vendor Reference" worksheet shows an example of the data that would be returned if the "perfect" formula were entered into the cells. (I'll explain what I mean by "perfect" in a moment.) It also has an area designated for testing formulas to see what is returned. It's blank, because none of my tests returned anything but errors.
You will notice the column headings on the "Sample Database" worksheet. Columns A and B will always contain the part number and product description. The remaining columns will contain vendor specific data, with perhaps 25 different vendors, and 3 columns per vendor. These columns would be "PV", (for Primary Vendor), the vendor number, (010299, for example), and the Vend Part#.
If there is an asterisk, "*", under the "PV" column for that vendor, that means they are the "Primary" vendor. The cost from that vendor is listed under each vendor number. There may be instances where 2 different vendors are listed as primary. If that's the case, the formula can simply return the FIRST one found.
Here's what the "perfect" formula would return: .....
View 6 Replies
View Related
Dec 10, 2009
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.
View 4 Replies
View Related
Nov 30, 2009
I have a worksheet with 6,500 rows of data. In each row, there are up to 6 cells containing a name in "Last, First" format. I need to alphabetize the data from these 6 cells so they go across the row in order left to right, alpha by last name.
Example of my data layout:
Column 1 Column 2 Column 3
Row 1 Jackson, Bob / Anderson, Lisa / Cardinal, Mike
Row 2 Tomlinson, Steve / Dodge, Sarah / White, Brian
I need them to be in this order:
Column 1 Column 2 Column 3
Row 1 Anderson, Lisa / Cardinal, Mike / Jackson, Bob
Row 2 Dodge, Sarah / Tomlinson, Steve / White, Brian
Is there a formula I can apply that will pull from only the portion of the data before the comma in each cell? Or do I need to separate out all of the names so the first and last names are in separate columns for this to work?
View 14 Replies
View Related
