- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Is there a easy way to use a Excel Pivot table to manage text?
Posted on 3/26/21 at 6:53 am
Posted on 3/26/21 at 6:53 am
I have a large database in an excel spreadsheet that has numbers, text and dates. I know how to create/use a pivot table in excel that manages numbers very well but I am struggling with text. I started to use conditional formatting to convert text to a number and then a use a "if # = x, then "text" statement but it seems way to cumbersome to get what I want.
Is there a way to use excel effectively with text or is another program like Access out there that I can use?
Is there a way to use excel effectively with text or is another program like Access out there that I can use?
15
Posted on 3/26/21 at 6:56 am to Crow Pie
I'm a VBA wizard, but Access is soo early-mid 2000s.
Text is hard, you need to add sometype of code library with pre or suffixes.
Edt: meaning to that text, then you can run IF statements easily. That is the quickest work around.
Text is hard, you need to add sometype of code library with pre or suffixes.
Edt: meaning to that text, then you can run IF statements easily. That is the quickest work around.
This post was edited on 3/26/21 at 6:59 am
Posted on 3/26/21 at 7:02 am to LSU Coyote
quote:I am finding that out. It is essentially a very long list of safety and other technical certifications and I am trying to create a table that is visually easy for "non computer types" (aka..old managers) to see which of their employees have expired, or soon to be expired, certifications so they can schedule new cert classes.
Text is hard,
Posted on 3/26/21 at 7:05 am to Crow Pie
Describe from left to right the columns and their data in the dataset.
Also describe what you want the final outputs columns and values to say in the pivot table
Also describe what you want the final outputs columns and values to say in the pivot table
This post was edited on 3/26/21 at 7:06 am
Posted on 3/26/21 at 7:07 am to Crow Pie
Maybe you could add a column and capture certain text within the text fields and pivot using that? Like an if cell contains xyz text say yes or something. I may be misunderstanding what you’re looking for.
This post was edited on 3/26/21 at 7:10 am
Posted on 3/26/21 at 7:10 am to Swoopin
quote:
Describe from left to right the columns and their data in the dataset.
Also describe what you want the final outputs columns and values to say in the pivot table
This. It's difficult to help when we don't know what the current dataset is and what the intended outcome is.
Helping us with that will go a long way towards helping you.
Posted on 3/26/21 at 7:10 am to Crow Pie
quote:
conditional formatting to convert text to a number and then a use a "if # = x, then "text" statement but it seems way to cumbersome to get what I want.
Why not use VALUE(x) to convert text to a number?
Also use TYPE. If TYPE(x)=1, then "number", if TYPE(x)=2, then "text"
But like the other posters said, you need to provide more specifics on what you're trying to do.
This post was edited on 3/26/21 at 7:12 am
Posted on 3/26/21 at 7:13 am to Crow Pie
quote:
I am finding that out. It is essentially a very long list of safety and other technical certifications and I am trying to create a table that is visually easy for "non computer types" (aka..old managers) to see which of their employees have expired, or soon to be expired, certifications so they can schedule new cert classes.
Sounds like a conditional formatting solution. If date is before X/X/X than highlight cell as one color.
If after X/X/X than highlight cell as another color.
From there, just do some simple data manipulation and cut/paste to provide the info that your managers need.
This post was edited on 3/26/21 at 7:16 am
Posted on 3/26/21 at 9:14 am to UGATiger26
quote:this is what I am doing, I was just hoping to find a way to simply show what is in a cell regardless if it was text or numbers as conditionally formatting all text fields is going to take quite a bit of time
Sounds like a conditional formatting solution. If date is before X/X/X than highlight cell as one color.
If after X/X/X than highlight cell as another color.
From there, just do some simple data manipulation and cut/paste to provide the info that your managers need.
Posted on 3/26/21 at 9:15 am to Crow Pie
I am pretty close to a power user, and can even create macros and do some basic visual basic. That being said, I still cannot make fricking pivot tables.
Posted on 3/26/21 at 9:16 am to Jcorye1
quote:
That being said, I still cannot make fricking pivot tables.
First thing I do is revert it to the "Classic PivotTable" view. I've never liked the new view.
Posted on 3/26/21 at 9:22 am to GetCocky11
Maybe consider creating a unique key using concatenate to combine employee ID number and the text you are referencing.
Posted on 3/26/21 at 9:41 am to Crow Pie
Can you post a screenshot of what you’re working with, and then what you’re trying to accomplish?
I’ve used Pivot Tables with text and dates before and it wasn’t too much trouble. But maybe I’m not understanding what type of manipulation you’re trying to accomplish. For example, I used a pivot table to manipulate a list of orders, POs, descriptions, and types to determine what quantities are coming when, how many are coming past a certain date, etc.
I’ve used Pivot Tables with text and dates before and it wasn’t too much trouble. But maybe I’m not understanding what type of manipulation you’re trying to accomplish. For example, I used a pivot table to manipulate a list of orders, POs, descriptions, and types to determine what quantities are coming when, how many are coming past a certain date, etc.
Posted on 3/26/21 at 9:42 am to Crow Pie
The Chinese variant of a Pivot table:
Posted on 3/26/21 at 9:45 am to Crow Pie
Anytime there is an excel question the answer is Alteryx
Posted on 3/26/21 at 10:21 am to Crow Pie
Dates are a bit tricky but easy enough. First, you are aware of how excel sees dates as a number of days since 1/1/1900.
The issue sounds easy enough, just create a column that counts the number of days until expiration. =Today ()-cell with expiration date.
Or you could just conditional format the expiration date cell to turn red when today minus value is less than 30 or some other metric.
Okay sounds like you trying to use a pivot table as a dashboard.
The issue sounds easy enough, just create a column that counts the number of days until expiration. =Today ()-cell with expiration date.
Or you could just conditional format the expiration date cell to turn red when today minus value is less than 30 or some other metric.
Okay sounds like you trying to use a pivot table as a dashboard.
Posted on 3/26/21 at 10:22 am to Mingo Was His NameO
quote:
Anytime there is an excel question the answer is Alteryx
Preach
Posted on 3/26/21 at 10:47 am to Crow Pie
I’d make another row and use if commands to make a 1 or blank for the text you need, then create the pivot table for about to expire certs. Or create another row after that with If 1, then text about to expire.
Posted on 3/26/21 at 12:44 pm to Crow Pie
Sample of what I am working with.. there are 100 columns represents 25 different certifications and thousands of rows of employees
quote:Yes. I want each manager to only see his/her employees and not all the others. I want to use the slicer function so all they have to do is "click the box" appropriate to them. What you dont see on the sample is various locations and business units throughout the country
Okay sounds like you trying to use a pivot table as a dashboard
This post was edited on 3/26/21 at 12:49 pm
Posted on 3/26/21 at 4:06 pm to Crow Pie
Could you paste a mockup/sample of what results you're looking for?
Like if you had a pivot table by manager, that simply had number of expirations by their name, ex. 6, They could simply click on the 6 and drill down to the employees who have expired certifications...Would that work?
I used to maintain an Excel spreadsheet that was a random collection of pivot tables to make the spreadsheet look like a dashboard, But that was more for my own edification and not something published for others to interact with....
Like if you had a pivot table by manager, that simply had number of expirations by their name, ex. 6, They could simply click on the 6 and drill down to the employees who have expired certifications...Would that work?
I used to maintain an Excel spreadsheet that was a random collection of pivot tables to make the spreadsheet look like a dashboard, But that was more for my own edification and not something published for others to interact with....
Page 1 of 2
Popular
Back to top
Follow TigerDroppings for LSU Football News