Divide Uneven Data Into Columns

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


ADVERTISEMENT

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 View Related

Finding The Column With The MAX Height In Comparson Wth Othr Columns Of UNEVEN Height

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

Add Two Columns Together And Then Divide The Sum By Another Column

May 11, 2009

I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:

4+4/636 = 79.5

d2+f2 (divided by) h2 = 79.5

View 3 Replies View Related

Divide Two Columns But Show My Zero

May 24, 2006

I have to divide two columns. Sometimes there is a zero in one of the columns. Now, I realize that nothing can be divided by zero, but isn't there a way to show a zero instead of the #ref symbol???

View 2 Replies View Related

Converting Uneven Rows Of Data Per Customer Into 1 Row Of Data Per Customer

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

Divide Data Into Different Sheets

Apr 7, 2014

I have minutes of meeting and have to divide them into different sheets against completed, and different departments and dept HOD responsible for it. How do I do it without "Copy/Pasting" the code? Also I have to remove the ones that are done. Any macro or something that can be written for these.

format of sheet
Sl No. | Date | Remarks | Dept | Dept Head 1 | Dept Head 2 | Status

I have to get data into different sheets against Dept, Dept Head 1, Dept Head 2 and remove the "Done" status to different sheets.

How do I do it without manual labor?

View 1 Replies View Related

Divide Data Into Two Categories?

Aug 1, 2014

I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?

View 3 Replies View Related

How To Divide Data Into Categories

Aug 1, 2014

I am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?

View 10 Replies View Related

Divide Value In Column A By Data In B:AO Using VBA

Aug 15, 2013

I'm trying to divide the value in column A by the data in B:AO using VBA, however I need to repeat this for many rows (the values differ for each row).

There could also be a different amount of rows with data in each time, and there is also a single blank row after every 18 rows with data.

View 1 Replies View Related

How To Re-divide The Data At The Same Time Interval

Apr 18, 2009

I have 2 columns of data (value and time):

for example:

15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00

They are continuous data. so I want to divid the value into hourly data as follows.

15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00

View 9 Replies View Related

Value Of I In A For Next Loop (even/uneven)

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

Uneven Spacing On X Axis

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

How To Move Row Content With Uneven Spacing

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

Summing Uneven Blocks Of Rows

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

Payback & Discounted Formulas For Uneven Cash Flows

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

Divide By Zero Error

Apr 8, 2014

I have conditional formatting on a cell based on a formula, when I entered the solution of "if then 0 " but the cell doesn't see the result as a 0, if I manually enter the 0 the conditional format works.

I even tried putting the formula in another cell and then referencing to that cell, still didn't work...

The "divide by zero error won't trigger the conditional either......

[Code] ....

View 3 Replies View Related

Divide A Given Value Into Several Cells

Oct 10, 2008

If I have a value of lets say 53, I need it broken down into equal and whole numbers across the spread sheet with the last one being the remaining value.

Currently for example in cell F5 I have a value of 41 that I need to break down so
I have done the following:
In Cell P5 =IF(F5>=25,F5-21,F5)
Then in Cell Z5 =IF(P5>=25,P5-21,P5) and this goes on until I have zero value from the original value in the last cell can i not do this all in one formula and have the results populate in other cells.

View 3 Replies View Related

Divide By Zero Error

Aug 20, 2009

I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (M-F). The last column gives his weekly average.

She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come.

How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?

View 12 Replies View Related

All Cells Divide By 100

Feb 2, 2014

I have just started to try and learn VBA for excel. the resource I have been using are books and the WEB. Some how, not sure how there was/is some code where all enteries in all worksheets are divided by 100, i.e if I enter 2222 after clicking the enter button the result in the cell is 22.22. I have deleted all macros and code however the problem still exist.

View 3 Replies View Related

Can I Divide A Combobox Value By 2

May 21, 2009


If CheckBox1.Value = True Then
Range("e25") = ComboBox6.Value
ElseIf CheckBox2.Value = True Then
Range("e25") = ComboBox6.Value / 2
End If

Im getting an error on the second to last row, Ive never tried to divide using VBA before and I have no idea how to!

Also the amount im trying to divide will always be in the format hh:mm

View 9 Replies View Related

Divide By Zero Error. #DIV/0

Oct 16, 2006

I have a database that requires me to find the average of some data over several columns, however if one of the values I have to divide by is a zero I get a 'Can't Divide By Zero Error' is there any way to override this ? So that if a Divide by Zero Occurs, instead of returning a 'Can't Divide By Zero Error' it just replaces it with a Zero instead ?

View 6 Replies View Related

Divide Population Into Deciles

Jun 15, 2009

Excel has a QUARTILE function to divide a population into quartiles. I'm trying to do something similar but divide a population into deciles (i.e., every 10%). Is there any easy way to divide a large population into deciles and to assign a decile to each value in the population (i.e., top 10%, 11-20%, 21-30%, . . . bottom 10%)?

View 2 Replies View Related

VBA To Divide Rows Between Worksheets?

Jun 20, 2014

I have a data extract that gets generated at the end of each month that I post into the 'Full Extract' tab on my example spreadsheet.

writing a macro that will divide the various items in the 'Extract' based on the Reference, (some items may need to go into multiple sheets)

then draw down the formulas that need to be applied to the item.

View 2 Replies View Related

Divide Selected Cell By 100

Dec 16, 2009

I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).

These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.

View 9 Replies View Related

Function 2 Divide A Number (ie $1.00) By % (ie 25%) To = ($4.00)?

Sep 6, 2005

=1/25%

is it that you want a UDF

Function MyFun(a,b)
MyFun = a / b
End Function

Usage:
=MyFun(1, 25%)

Mangesh

"Gonecoastal1" <Gonecoastal1@discussions.microsoft.com> wrote in message
news:B257DADD-0042-4CF8-88DF-1D17AEAFF627@microsoft.com...

View 14 Replies View Related

Divide Only With Number Of Cells With Value>0

Aug 27, 2009

I really struggle with the following scenario:

A1=5 B1=4 C1=0 D1=0 E1=5

What I want to do is to divide the sum of those cells with the number of cells with a value > 0 (but all cells must be controlled to check if the value > 0).

In real life it's easy to see that I want to do 14/3 (A1>0, B1>0 and E1>0 equals three cells), but how to write this formula in Excel?

View 3 Replies View Related

Divide Two Cells If No Text In First?

Jan 6, 2012

How to divide two cells if there is no text in the first?

i.e.: if there is text display a 0 and if there is a number to divide it with a second number in another cell.

View 7 Replies View Related

Divide Two Groups Of Cells?

Sep 21, 2012

I am trying to divide the total of two groups of cells and I used the following formula:

=sum(B3:M3)/(B17:M17)

However, that is not a correct formula.

View 4 Replies View Related

Divide Without Using Temp Column

Feb 16, 2007

In A1 I have the value 60 I want it in 1:00:00 format

What I do is, I divide that by A1/24*60 and the copy that and paste-special-value on A1

Is there any way to convert the number 60 into 1:00:00 format without having to do calculation in temporary columns?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved