Separating Second Occurrence

Sep 16, 2008

[data] ....

I'm having a problem seperating the prices in Colomn H into three seperate cells C,E & G
The first figure in c is easy enough if a little long (any possible tidier solutions?)
The second Im not happy with it is clumsy using the fixed lenght figure "5"
The last I cant figure out probably because Ive done the second incorrectly!
Finally I can not see why figures apear in away team.

View 9 Replies


ADVERTISEMENT

Calculate Only Last Occurrence Or Occurrence Before Specified Number

Dec 24, 2013

I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue

At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.

The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.

Result is this in the CDR

FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00

The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.

How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?

View 2 Replies View Related

Separating The Dates.

Feb 22, 2010

I have a form where users can input a Date by utilizing 3 drop down combo boxes. Once they've made their selection that Date gets written to a cell on the worksheet. If for some reason they need to change that Date, they would reopen the form and select a new Day month or year.

I would like the three comboboxes to show the D/M/Y previous written to the worksheet. But I would have to split up the date that has since been concatenated.

View 8 Replies View Related

Separating Out Address Using VBA

Jun 28, 2013

I have a spread sheet with thousands of addresses in them.

It does not hold the state however.

I need to separate each part of the address into it's own cell on the same sheet.

Keeping the first part ONLY of the address in the original place the address was in.

However not having the city, or zip in that cell.

For example:

E2 holds the address: 19505 45th Ave. W. Suite A, Lynnwood 90821

(There is always a comma before the city. However sometimes there is another comma before Suite...)

I want the end result to be:

E2: 19505 45th Ave. W. Suite A
I2: Lynnwood
K2: 90821

Then I would like it to loop through all the address's in the "E" Column until it has edited all the address's. It is sad to say but I have been working on this for over 48 hours... Can't believe no one online hasn't come up with a clear answer on how to do this. At this point I am looking for the code to make this happen in Excel VBA.

View 9 Replies View Related

Separating Data In A Cell?

Aug 20, 2014

I've been tasked with separating data within an Excel cell into it's own row. The difficulty I am having is, I need to keep the ticket number each piece of data within that cell is linked to. For example, I have Column A that has the ticket number, and column I has the serial numbers of each piece of equipment linked to that ticket number. I would like the serial numbers separated into their own row, but still keep the ticket number in which that equipment is connected to.

Here's an example: Column A Column I
Ticket # Serial Numbers on Account
23999866 PAFR06598067 - MOPCUDTAM

Would I need a formula to do this, or is this something I can do using one of the Ribbon commands?

View 9 Replies View Related

Separating Information In Cells

Aug 6, 2007

I have rows of cells with the following information..

Row 1, Column 1: Address City/State
Example: 111 Old Creek Road Stanton, VA 25523

Is there any way to separate the Address and the City/State? I would much rather the spreadsheet be in the following format.

Row 1, Column 1: Address
Row 1, Column 2: City/State

or ..

Row 1, Column 1: 111 Old Creek Road
Row 1, Column 2: Stanton, VA 25523

I'm just wondering if there's a fast way to do this, or if I have to go in and do it manually - the reason i'm looking for a faster way to do this is because I have approximately 3000 rows to do it to. :shrugs:

View 9 Replies View Related

Separating Or Sorting Data

Sep 22, 2008

for example:

Column A.

@yahoo.com
@hotmail.com
@live.com
@msn.com
@yahoo.com
@hotmail.com
@live.com
@msn.com

how can i separate all datas by domain without having to cut and paste them manually.

View 7 Replies View Related

Separating Cells In A Worksheet

Jan 2, 2009

Set up a worksheet so that it has 2 separate sections in the same worksheet ????? I am wanting to have 2 different sets of information in the same worksheet - and need the columns to be different sizes based on the information i want to put in. Is there any way to have this done - as I know that if you change the column width it does all columns - and i only want to do select columns. For example for section 1 - i would like to have the column widths as follows:

A1 = 20
B1 = 40
C1 = 10
D1 = 20
E1 = 40
A3 = 20
B3 = 40
C3 = 10
D3 = 20
E3 = 40

This is where i want to put details such as names, dates and results.

Then for section 2 - i would like to have the column width as follows:

A6 = 10
B6 = 45
C6 = 15
D6 = 30

This is where i want to have a set of questions to answer.

View 4 Replies View Related

Separating Recurring Names

Apr 25, 2009

Is there any formula or formatting that will list in a seperate table all names that occur equal to or more than a specified number of times?

eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table on a seperate worksheet.

Gus
Bob
Mike
John
Gus
Nick
Mike
Gus

So Gus and Mike would be listed in the other table on the other worksheet.

View 9 Replies View Related

Separating Numbers Into Different Columns?

Sep 21, 2013

I have a column with random times that are not seperated by any commas or spaces how can i seperate them into different columns. For example in A1 i have 12:3213:5420:32 this represent three different times 12.32 13.54 and 20.32 (each time has the same format as shown) and i want to display these as B1=12:32 C1=13:54 and D1=20:32 then do the same for the rest of column A?

View 3 Replies View Related

Separating Date And Time

Oct 29, 2013

I need some dates showing in the correct format.

I have two worksheets, the first will have a date as shown in the attached worksheet in cell A2. This would have been pasted as a value from another source. I need this to be seperated and to show as in cells D2 and E2.

Would be ok with either a formula or macro to do this.

View 2 Replies View Related

Separating Words In A Cell

Feb 26, 2013

I have a cell containing text like this

Ph(Off):* 5754112-5750441 ,* Cell:*0300-8406693*******,* Fax(Off):* 5712685*

I want to separate them into different cell though which every word or number contain his own cell

like
A1 = Ph(Off):* 5754112-5750441 ,* Cell:*0300-8406693*******,* Fax(Off):* 5712685
then i want it in this way
B1 C1 D1 E1 F1 G1
Ph(Off): 5754112-5750441 Cell: 0300-8406693 Fax(Off): 5712685

View 2 Replies View Related

Separating The Records Depending On The Value

Dec 22, 2008

I have attached 2 spreadsheet :-

1. BounceBackMaster.xls
2. BouncebackMails.xls

I need to excute the macro from master file asking the input file. Input file will be the Mails spreadsheet. In the master spreadsheet there will be "TAG" column, depending on value it need to check the value in the Mails spreadsheet "MessageContent" column after subject value. It need to group all tAG value, each sheet need to be created with TAG value and related need to be copied from "BounceBack" sheet to created sheet.

Example:

Step 1 :- Macro is executed from "BounceBackMaster.xls" and asked for input file, input file will be "BouncebackMails.xls"

Step 2 :- From "BounceBackMaster.xls", First TAG value will be readed " Monthly Account Statement" is check in "BouncebackMails.xls"........

View 3 Replies View Related

Separating TEXT Into Different Columns

Dec 2, 2008

Seperating TEXT in different columns placed at non-fixed location in a cell...

I have some data which contains people's name and the places ( i.e Cities) mixed which I need to seperate, however the challenging part there is no consistency in data as the city can appear first in the middle , last or even just the city name in the cell but it needs to be seperated ...

In Column B I have the following type of DATA ...

View 11 Replies View Related

Separating Information In A Cell

Sep 26, 2011

I can’t seem to work out how to pull data relating to a particular title from one spreadsheet, to another, separating the information on.

For example, column A lists names, and column B lists tests scores for each person. However each person has done several tests, and the tests are listed in one cell, separated by a carriage return (not comma etc, so can't do a delimited function).

I want to create a new spreadsheet with names still in column A, but the test scores separated. So Column B = Test one, Column C = Test two, Column D = Test three, etc. In each column I would like only the test score listed (so not the test title - I will put this in the first row as the column header)

Is this possible?!

View 1 Replies View Related

Separating Data From Days?

Jul 29, 2012

What I am looking for is a macro that can separate the data introduced manually in the left table (see attach file) and order it in the table from the right hand side as I have done it manually in this case. The base of it is to have at the end all the trips separated from days (days going from 0:00 hrs till 24:00 hrs). Maybe is a bit confusing but in the example I have attached I presume is going to be easier.

View 3 Replies View Related

Separating Text Into Different Columns?

Jun 24, 2014

I want to separate the text into columns as in Table

Name
Name
Contract
Ref. No

[Code].....

View 5 Replies View Related

Detecting And Separating Integer

Dec 1, 2008

A1 : Style 998 45702B WRAP DRESS 1 0 0 0 1

Try to accomplish:

I would like to remove the "1" in between "Dress" and "0"

in other word remove integer from string but starting from right only.

B1 = should contain 1

View 9 Replies View Related

Separating Alpha And Numeric

Jan 21, 2009

I have data in column with combination of alpha-numeric like "Member # 308166 RCI Number 4801-60436". can I split this without using copy and paste?

View 9 Replies View Related

Separating Characters By Space

May 1, 2009

I have following kinds of texts in a column.

MISCELLANEOUS
EQUIPMENT
JBKU348597-3
T-225
75984

I want to convert above texts with space between every character as follows:

M I S C E L L A N E O U S
E Q U I P M E N T
J B K U 3 4 8 5 9 7 - 3
T - 2 2 5
7 5 9 8 4

View 9 Replies View Related

Separating Commas And Parenthesis

Jul 20, 2009

I'm trying to separate text that have commas in between. I've got a column that contains commas and a few cells in those columns have commas and bracket. The problem occurs when there are more than two values WITHIN in the bracket that are separated by commas. How can parse the text in such a way where what ever is within the bracket remains in tact? For example: Controls, Motors, Transformers (LVoltage, High Performance, Medium Voltage). The goal is to separate everything before a comma but for Transformers I would like it to remain as 'Transformers (LVoltage, High Performance, Medium Voltage)'.

View 9 Replies View Related

Separating Items With Comma

Sep 14, 2009

i have many many cells that have text like the following:

A,B,C,D,E,etc...

what i would like is to have it show like the following instead (within the same cell):

A
B
C
D
etc...

i know i can do this using ALT+Enter to make the separation manually....but there are many many cells....can this be done automatically?

also, it is possible that some cells would have only 1 item so in that case, it should be left alone....

View 9 Replies View Related

Vba, Separating Ranges With A Border

Feb 7, 2007

I have a spreadsheet of data, and all entries are grouped by different unique reference (three digit number). I need something in VBA so if the the unique reference in the columns are the same, to place a border over the ammending data. The actaull; spreadsheet is sorted ascending by the unique references so all the entries are already grouped together. EG - So if I ran the code it would put a border around the group of rows for unique reference 126.

Unique reference
126
126
126
126
128
128

I attempted using if activecell offset, but i dont think the logic works. I ran a macro for the border, it is below.

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With.........................

View 7 Replies View Related

Separating From Multiple Rows Into Columns

Mar 25, 2014

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.

View 4 Replies View Related

Separating Words In One Cell Into Many Cells

Jun 12, 2014

All words are in Cell A, separated by the ";" symbol. I want separate into different Cells. Example... Lead_ID in Cell A1, Application Data in A2, Date Purchased in Cell A3 and so on.

lead_id;application_date;date_purchased;first_name;last_name;email;day_phone;work_phone;
best_time;city;state;zip;addr;cred_rating;cred_score;yearly_income;prop_state;prop_type;purpose;
loan_amount;prop_value;ltv;mtg_bal1;mtg_bal2;services;credit_repair;foreclosure;debt_management;comments

View 5 Replies View Related

Filtering / Separating Specific Datasets?

Jul 10, 2014

All of the strings are in one column and are in General format. They contain a few different types of strings: some only numbers, some numbers separated by commas (which makes them NOT numbers according to ISNUMBER), some text only, some text and numbers (separated by comma), some are 3 numbers, each separated by comma, etc.

ldJBlt9.jpg

What I'm looking to do:I want each of these distinct types filtered out someway. I've already done ISNUMBER to filter out the values which are numbers only, no commas. Perfect.

For the next step, I'd like to filter out cells which have only numbers and commas. The comma itself makes them considered not numbers, so how do I add the condition that I'm looking for cells with digits and a comma? A wildcard?

Edit: I've found the following formula which can check if it contains a comma, but doesn't specify between digits or text. Just commas. =IF(ISNUMBER(FIND(",",A1)),"TRUE","FALSE")

View 1 Replies View Related

Separating Information For Text To Columns

Oct 2, 2008

I have a list of 2500 companies. In each cell there is the company name, street address, and phone number. And of course, each one is of varying length. I need a way to put the company name, street address, and phone numbers in seperate columns. I tried going to text to columns but it wouldn't work due to the varying lengths.

View 5 Replies View Related

Separating ODD And Even Rows Into Separate Column?

May 13, 2013

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.

View 4 Replies View Related

Separating One Column Of Data Into 3 Based On Its Value?

Jan 16, 2014

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

View 6 Replies View Related

Separating Text & Numbers In Different Columns

Jan 27, 2014

I have to separate text & numbers in different columns.

e.g. 349,910 American Capital Ltd. (a) 4,902,239

i want all 3 data in separate columns, there is a series of data like this wherein numbers are there both at begining & end & text in middle.

View 11 Replies View Related







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