Creating Formula for Pivot Tables

[PtrtsGrl]

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.

 

[etaf]

how it is it all formatted in that 1 column ?

 

[btomjack]

Did you get this data from something other than an Excel file? Perhaps something that ends in .txt or .csv?

 

[PtrtsGrl]

Did you get this data from something other than an Excel file? Perhaps something that ends in .txt or .csv?

Yes I have it in a .csv file. will that help?

 

[etaf]

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]

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]

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]

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]

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]

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).