Question description:
This user has given permission to use the problem statement for this
blog.
I'm creating a dynamic sentence that has multiple categories and metrics. The problem I have is the sentence needs to be more dynamic based on the categories and metrics for punctuation purposes. If there are 2 metrics in 1 category and 3 in a 2nd category I need the sentence to tell when to use a comma vs and &
Solved by Z. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
29/05/2018 - 10:04
Hello there, welcome to Got It Pro.
User
29/05/2018 - 10:05
Hi, did my problem make sense, kind of hard to describe
Excelchat Expert
29/05/2018 - 10:05
yes.
Excelchat Expert
29/05/2018 - 10:05
Not completely, but I get the idea.
User
29/05/2018 - 10:05
so here would be the example, I ran out of characters in my initial question
Excelchat Expert
29/05/2018 - 10:06
What confuses me is the difference between the category and metrics.
Excelchat Expert
29/05/2018 - 10:06
Sure.
Excelchat Expert
29/05/2018 - 10:06
Best if you could write it on the Excel Onilne panel on the middle of the screen.
User
29/05/2018 - 10:07
so say you are ranking metrics between cities. The metrics are "Sales", "Revenue", "Headcount" and those metrics are ranked into groups... the groups are "TOP10, Middle, BOT10"
User
29/05/2018 - 10:08
so in March, say NYC is Top 10 in sales and revenue, bottom 10 in heacount
User
29/05/2018 - 10:08
in April, say NYC is Top 10 in all 3
Excelchat Expert
29/05/2018 - 10:08
Alright.
User
29/05/2018 - 10:09
I need the sentence to know that for March it should be "NYC Top 10 in Sales and Revenue. Bottom 10 in Headcounts" omitting the "middle because there is no value"
Excelchat Expert
29/05/2018 - 10:09
Alright.
User
29/05/2018 - 10:09
on the April data it would read "NYC Top 10 in Sales, Revenue and headcount" and omits the other 2
Excelchat Expert
29/05/2018 - 10:10
Okay I get it.
Excelchat Expert
29/05/2018 - 10:10
Please allow me a few moments to present a solution.
User
29/05/2018 - 10:10
no problem... so assuming the name NYC is in cell c9... it would start with...
User
29/05/2018 - 10:11
=C9&" - Top 10 in"
Excelchat Expert
29/05/2018 - 10:11
Okay.
User
29/05/2018 - 10:11
the next part is what is holding me up
Excelchat Expert
29/05/2018 - 10:12
We'll figure it out in no time.
Excelchat Expert
29/05/2018 - 10:13
At first I would want you to take a look at the Excel Online panel on the middle of the screen.
Excelchat Expert
29/05/2018 - 10:14
Does the metrics and categories seem to be okay?
Excelchat Expert
29/05/2018 - 10:14
*Do
User
29/05/2018 - 10:14
so I did it a different way... looks like you are doing a grid for each metric... I did the city and then each metric... can I paste something into the excel sheet?
Excelchat Expert
29/05/2018 - 10:14
Sure.
Excelchat Expert
29/05/2018 - 10:14
That would be really helpful
User
29/05/2018 - 10:15
so pretend thats NYC.. and has some different metrics
User
29/05/2018 - 10:15
so I guess for the NYC example I would do...
Excelchat Expert
29/05/2018 - 10:16
Alright.
Excelchat Expert
29/05/2018 - 10:16
Thanks a lot for this example.
User
29/05/2018 - 10:17
no problem.. so what I did from there was basically
Excelchat Expert
29/05/2018 - 10:18
=F2&" - Top 10 in"
User
29/05/2018 - 10:20
so my thought was to do something like that and then for excel to look for any cells that arent blank
User
29/05/2018 - 10:22
still doesnt help me place a comma vs a &
Excelchat Expert
29/05/2018 - 10:22
will get to that in a moment.
Excelchat Expert
29/05/2018 - 10:22
WIll it be blank or a 'NO"
User
29/05/2018 - 10:23
should really be omitted, so I went with a blank
Excelchat Expert
29/05/2018 - 10:23
Alright.
Excelchat Expert
29/05/2018 - 10:25
Please have a look at E6.
Excelchat Expert
29/05/2018 - 10:25
Already figured out for an all positive scenario.
Excelchat Expert
29/05/2018 - 10:25
Now to work on the issues with , and &
User
29/05/2018 - 10:28
ok, so with what you have, I get it, I guess the only change would be that F2/G2/H2 should b e F2, F3, F4
User
29/05/2018 - 10:28
so if I can adjust what you did...
User
29/05/2018 - 10:29
there
Excelchat Expert
29/05/2018 - 10:29
On it.
User
29/05/2018 - 10:31
still with me? Just checking in, got to run in about 15 min
Excelchat Expert
29/05/2018 - 10:34
Yes.
Excelchat Expert
29/05/2018 - 10:34
It will be done by then.
User
29/05/2018 - 10:34
cool, thanks
Excelchat Expert
29/05/2018 - 10:36
You're welcome.
Excelchat Expert
29/05/2018 - 10:40
Could you please have a look now?
User
29/05/2018 - 10:42
so... close but I think its a row/colum thing again... also, it doesnt seem to know to use an & for what you have in there now
Excelchat Expert
29/05/2018 - 10:42
Yes, I missed it as well.
User
29/05/2018 - 10:42
because if its 2 of the 3 metrics it would be "metric 1 and metric 2"... vs all 3 being "metric 1, metric 2 and metric 3"
Excelchat Expert
29/05/2018 - 10:43
This really complicates things. But don't worry. I will sovle it.
Excelchat Expert
29/05/2018 - 10:43
*solve
Excelchat Expert
29/05/2018 - 10:43
If you can't be here I will email it to support and they will send it to you.
Excelchat Expert
29/05/2018 - 10:44
Check back with the Got It Pro website when you're back and you will find it.
User
29/05/2018 - 10:44
ok great, yeah got to run. Yeah this is complicated, im pretty good overall and I could make a giant nested if statement I just feel like theres a better way
User
29/05/2018 - 10:44
sounds good, thanks alot
Excelchat Expert
29/05/2018 - 10:44
No problem.
Excelchat Expert
29/05/2018 - 10:44
Thanks for using Got It Pro.
Excelchat Expert
29/05/2018 - 10:45
Have a good day.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
