Creating Formula for Pivot Tables

PtrtsGrl

New Member
Joined
Feb 7, 2014
Messages
12
Hi everyone,

I'm new here and am having some problems. I want to create a pivot table for work that tracks calls, contacts, email, voicemail, and meetings and track ratios for employees. The problem is that all of that information is contained in 1 column, and I can't figure out a formula to track each of those things for all the employees. I thought of creating a giant "if/then" statement, but it just became so complicated.

Can anyone help me / point me in the proper direction.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,779
Office Version
365
Platform
MacOS
how it is it all formatted in that 1 column ?
 

btomjack

Board Regular
Joined
Sep 13, 2013
Messages
229
Did you get this data from something other than an Excel file? Perhaps something that ends in .txt or .csv?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,779
Office Version
365
Platform
MacOS
is there a deliminator at all between the various information parts

can you give an example of how the data looks

if a csv file it should have commas

csv = comma separated value
and have commas between each field and then in excel that should change the columns and remove the commas
 

btomjack

Board Regular
Joined
Sep 13, 2013
Messages
229
That probably explains why it is all appearing in one column. What version of Excel are you using? When you open the file, is there a dialog box that pops up?

I use Excel 2010, so these instructions might not work for you if you have a different version. When the dialog box pops up (assuming there is one), press "Next", then make sure the check box for "commas" is checked, then click finish.
 

PtrtsGrl

New Member
Joined
Feb 7, 2014
Messages
12
That probably explains why it is all appearing in one column. What version of Excel are you using? When you open the file, is there a dialog box that pops up?

I use Excel 2010, so these instructions might not work for you if you have a different version. When the dialog box pops up (assuming there is one), press "Next", then make sure the check box for "commas" is checked, then click finish.

I'm using Excel 2010 as well and the column they are in is to explain all of their activities (call, leave voicemail, connect, etc).

Hope that helps/ makes sense

I can post it to this thread if that'll help
 

PtrtsGrl

New Member
Joined
Feb 7, 2014
Messages
12
what do you mean deliminator?

The column is titled "Type" and has various categories (Call, left voicemail, canvassing, etc)

I'm trying to figure out a way to include a formula so I can find a ratio for Connects (which will be total connects / (total connects + email + voicemail)

Hope that helps!
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,779
Office Version
365
Platform
MacOS
what is separatign the fields

so your example
Call, left voicemail, canvassing, etc
shows a comma between each item, thats called a delimiter

in your sheet , anything in between

would you also answer
btomjack
question
 

PtrtsGrl

New Member
Joined
Feb 7, 2014
Messages
12
Hi Etaf

My file does not have any commas, it has call or voicemail or the other categories in different cells, NOT commas.

Also, I'm running Excel 2010 (sorry I thought I responded, but somehow responded via quote).
 

Watch MrExcel Video

Forum statistics

Threads
1,095,827
Messages
5,446,725
Members
405,414
Latest member
wayne_p

This Week's Hot Topics

Top