Question description:
This user has given permission to use the problem statement for this
blog.
I need to calculate the number of female board members and directors for each company, also the % of females and how they compare to number and % of men in the same firms. Only small firms should count and only board members and relevant associating functions.
Solved by K. D. in 41 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
06/05/2018 - 01:49
Hello
Excelchat Expert
06/05/2018 - 01:50
hello!
Excelchat Expert
06/05/2018 - 01:50
how are you?
User
06/05/2018 - 01:50
Great, thanks for asking
User
06/05/2018 - 01:50
Yourself?
Excelchat Expert
06/05/2018 - 01:50
I'm good :) thanks
User
06/05/2018 - 01:51
Good to hear
Excelchat Expert
06/05/2018 - 01:51
are you able to share your worksheet where you need to do the counting?
User
06/05/2018 - 01:51
Yes
User
06/05/2018 - 01:51
https://drive.google.com/open?id=1JJ8oglbnREdjCv71ilV7bks0SBkjgQw3
User
06/05/2018 - 01:51
This is the link
User
06/05/2018 - 01:51
It's quite a big file
Excelchat Expert
06/05/2018 - 01:51
okay, will download it now
User
06/05/2018 - 01:51
But it needs to be sorted
User
06/05/2018 - 01:51
I tried sending it
User
06/05/2018 - 01:51
Sorted
User
06/05/2018 - 01:51
But it didn't work
Excelchat Expert
06/05/2018 - 01:52
I see, how many MB is this?
Excelchat Expert
06/05/2018 - 01:52
it's still loading as we speak
User
06/05/2018 - 01:53
Alright
Excelchat Expert
06/05/2018 - 01:54
Would you know how big the file is?
User
06/05/2018 - 01:54
Without deleting the unccessary columns
User
06/05/2018 - 01:54
I believe it is 50MBs?
Excelchat Expert
06/05/2018 - 01:55
are you able to send me a trimmed down version of the worksheet?
Excelchat Expert
06/05/2018 - 01:55
i mean excluding the unnecessary columns. If that wouldn't be much of a bother, that is
User
06/05/2018 - 01:56
Sure I will give it a go
User
06/05/2018 - 01:56
This
User
06/05/2018 - 01:56
Link
User
06/05/2018 - 01:56
Should work
Excelchat Expert
06/05/2018 - 01:56
Great! Thank you!
User
06/05/2018 - 01:56
https://drive.google.com/open?id=15mcn8FYI75ruThtJnulEvcxNnveUFeL2
Excelchat Expert
06/05/2018 - 01:58
Anyway, have you tried using pivot table for this?
Excelchat Expert
06/05/2018 - 01:58
still downloading it now... but would like to probe further so we may do something while we wait
User
06/05/2018 - 01:58
Yeah I don't really know how to use pivot tables haha
User
06/05/2018 - 01:59
Btw what is the name of the file you are downloading? This one is 16MB
Excelchat Expert
06/05/2018 - 01:59
small companies filtered
Excelchat Expert
06/05/2018 - 01:59
how about take a sample slice of the data set and send it to me via attachment?
Excelchat Expert
06/05/2018 - 02:00
Oh, never mind
Excelchat Expert
06/05/2018 - 02:00
completely downloaded just now
User
06/05/2018 - 02:01
Alright perfect
Excelchat Expert
06/05/2018 - 02:01
okay, let's do this together. i'll detail the steps by steps now
Excelchat Expert
06/05/2018 - 02:01
are you on excel?
User
06/05/2018 - 02:02
Yeah, but I have trouble opening both files that's also a problem
User
06/05/2018 - 02:02
It' way too slow
User
06/05/2018 - 02:02
Don't think my laptop
User
06/05/2018 - 02:02
Can work with files this big
Excelchat Expert
06/05/2018 - 02:02
aww that's unfortunate
Excelchat Expert
06/05/2018 - 02:02
are you okay if i send to you the final product instead
Excelchat Expert
06/05/2018 - 02:03
could you please see the document preview on the right
Excelchat Expert
06/05/2018 - 02:03
just want to make a few clarifications before i proceed with it
User
06/05/2018 - 02:03
Would you be able to calculate the metrics I need?
User
06/05/2018 - 02:03
Sure
User
06/05/2018 - 02:03
Alright
Excelchat Expert
06/05/2018 - 02:04
yeah, just want to clarify what you need to see in the output table
User
06/05/2018 - 02:04
Yeah I see you just wrote Company Name
Excelchat Expert
06/05/2018 - 02:05
is this in line with your expectation?
Excelchat Expert
06/05/2018 - 02:05
please edit as you deem fit
User
06/05/2018 - 02:07
Yeah
User
06/05/2018 - 02:07
This is perfectly fine
User
06/05/2018 - 02:07
I would only add
User
06/05/2018 - 02:07
Overall board size
User
06/05/2018 - 02:07
So the total of M + F
Excelchat Expert
06/05/2018 - 02:08
like this?
Excelchat Expert
06/05/2018 - 02:08
do i have to include only specific functions?
Excelchat Expert
06/05/2018 - 02:09
like exclude traders in the count
Excelchat Expert
06/05/2018 - 02:10
haha, my excel just crashed too
Excelchat Expert
06/05/2018 - 02:10
wait, i'll restart
User
06/05/2018 - 02:11
No worries
User
06/05/2018 - 02:11
But yes
User
06/05/2018 - 02:11
Other functions should be excluded
User
06/05/2018 - 02:11
Other company sizes as well
User
06/05/2018 - 02:11
Only small are relevant
Excelchat Expert
06/05/2018 - 02:12
how do i filter small company size?
Excelchat Expert
06/05/2018 - 02:13
there are three columns available though
Excelchat Expert
06/05/2018 - 02:13
no company size - has this been filtered to exclude big companies already?
User
06/05/2018 - 02:13
Oh sorry
User
06/05/2018 - 02:13
Everything
User
06/05/2018 - 02:13
Is already
User
06/05/2018 - 02:13
Filtered
User
06/05/2018 - 02:13
In this doc
User
06/05/2018 - 02:14
So only the count is to be done
Excelchat Expert
06/05/2018 - 02:14
Got it! thanks
Excelchat Expert
06/05/2018 - 02:14
will get back to you asap. this might take a while
User
06/05/2018 - 02:16
Sure
User
06/05/2018 - 02:16
Thanks a lot btw!
Excelchat Expert
06/05/2018 - 02:19
could you please confirm this?
[Uploaded an Excel file]
Excelchat Expert
06/05/2018 - 02:19
Are you sure about the managing director?
Excelchat Expert
06/05/2018 - 02:20
and the GM?
User
06/05/2018 - 02:20
Yeah that looks fine
Excelchat Expert
06/05/2018 - 02:20
okay, got it
User
06/05/2018 - 02:21
Actually sorry
Excelchat Expert
06/05/2018 - 02:21
yes?
User
06/05/2018 - 02:21
MD cn be included
Excelchat Expert
06/05/2018 - 02:22
I thought so :) Okay
User
06/05/2018 - 02:22
Thanks!
Excelchat Expert
06/05/2018 - 02:26
Please see attached
[Uploaded an Excel file]
Excelchat Expert
06/05/2018 - 02:26
sorry i can only send the results as the file is too big
Excelchat Expert
06/05/2018 - 02:27
Hi! Are you still there?
User
06/05/2018 - 02:28
Yeah, still here!
User
06/05/2018 - 02:28
Opening the file now :))))
Excelchat Expert
06/05/2018 - 02:28
Good, thanks
Excelchat Expert
06/05/2018 - 02:29
any luck so far?
User
06/05/2018 - 02:29
Yeah just opened it
User
06/05/2018 - 02:30
It
User
06/05/2018 - 02:30
Looks
User
06/05/2018 - 02:30
Perfect!
Excelchat Expert
06/05/2018 - 02:30
Happy to hear that!
User
06/05/2018 - 02:30
Thanks a lot!
Excelchat Expert
06/05/2018 - 02:30
You're welcome :)
Excelchat Expert
06/05/2018 - 02:30
If you don't have any more clarifications at this point, you may end the session by hovering over the TIME REMAINING section of this chat window and select END SESSION
User
06/05/2018 - 02:30
Could I just bother you with one really
User
06/05/2018 - 02:30
Simple thing?
Excelchat Expert
06/05/2018 - 02:31
sure, what about?
User
06/05/2018 - 02:31
Actually no worries
User
06/05/2018 - 02:31
I will do it
User
06/05/2018 - 02:31
Myself
User
06/05/2018 - 02:31
Thank you very much!
Excelchat Expert
06/05/2018 - 02:31
Haha okay :))
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.