Question description:
This user has given permission to use the problem statement for this
blog.
help me please. I am trying to narrow down a spreadsheet by amount and then also my column
Solved by F. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/10/2018 - 11:27
Welcome to excelchat!
Excelchat Expert
12/10/2018 - 11:27
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
12/10/2018 - 11:28
Please tell me details about the problem.
User
12/10/2018 - 11:28
i have a problem with a large spreadsheet which I need to whittle down by a column - its about 20000 rows and have different amounts and columns
Excelchat Expert
12/10/2018 - 11:28
It seems like a cleanup of rows task.
Excelchat Expert
12/10/2018 - 11:29
In that case, I'm going to need the excel file.
User
12/10/2018 - 11:29
the first thing i need to look at amounts grouped by supplier and remove any that are below 10K in total and then look at whats remaining
Excelchat Expert
12/10/2018 - 11:29
I understand.
Excelchat Expert
12/10/2018 - 11:30
Please send me the file to analyze further.
User
12/10/2018 - 11:30
hmm
User
12/10/2018 - 11:30
its confidential
Excelchat Expert
12/10/2018 - 11:30
That is going to be a problem.
Excelchat Expert
12/10/2018 - 11:31
In this kind of clean up task, it is essential that I am capable of working with the file.
Excelchat Expert
12/10/2018 - 11:31
It's unfortunate but otherwise it is going to be very tough for me to help you.
User
12/10/2018 - 11:31
hmm
User
12/10/2018 - 11:31
how do u work?
User
12/10/2018 - 11:31
is this free?
Excelchat Expert
12/10/2018 - 11:32
Usually there is a charge.
Excelchat Expert
12/10/2018 - 11:32
If you are getting a free session then it might be for a promotional offer.
User
12/10/2018 - 11:32
so what is it?
User
12/10/2018 - 11:32
a fee or not
Excelchat Expert
12/10/2018 - 11:34
As I have said, usually there is a charge and the user is charged before entering a session. If you were able to enter this session without paying a charge then it means you will get this session for free of charge. But you might be charged if you come back later with a different problem.
User
12/10/2018 - 11:34
i see
User
12/10/2018 - 11:35
whta do u do with the excel spreadsheet we send you?
Excelchat Expert
12/10/2018 - 11:36
We work on the file as per the need of the user. then send back the finished file and delete it from our end.
User
12/10/2018 - 11:36
how do i sen dit /.
Excelchat Expert
12/10/2018 - 11:36
To upload a file, please click on the paperclip icon just right to this chat box.
User
12/10/2018 - 11:39
i need to look at suppliers by column(directT)(n) with a total value over 10K
[Uploaded an Excel file]
Excelchat Expert
12/10/2018 - 11:40
I'm downloading the file right now..
User
12/10/2018 - 11:40
columns f and c are correctly filtered
User
12/10/2018 - 11:41
THNKS
User
12/10/2018 - 11:41
would you email me once you've done it ?
Excelchat Expert
12/10/2018 - 11:42
Okay, I have opened the file.
Excelchat Expert
12/10/2018 - 11:42
I have a couple of question to make sure that I understand what you need.
Excelchat Expert
12/10/2018 - 11:42
I will send back the file here. If we run out of time, then I will have our support team to mail you the file.
User
12/10/2018 - 11:42
ok
Excelchat Expert
12/10/2018 - 11:43
Just so that you know, we can extend this chat for another 20 minutes.
Excelchat Expert
12/10/2018 - 11:43
You will asked shortly, please select YES to extend.
User
12/10/2018 - 11:43
ok
Excelchat Expert
12/10/2018 - 11:44
Now coming back to the problem, you have mentioned, you need to look at suppliers by column(directT)(n) with a total value over 10K
User
12/10/2018 - 11:45
yep
Excelchat Expert
12/10/2018 - 11:45
Can you please elaborate it a bit more? which column indicates amount?
Excelchat Expert
12/10/2018 - 11:45
What do you mean by "columns f and c are correctly filtered"?
User
12/10/2018 - 11:45
column I is amount
User
12/10/2018 - 11:46
no need to unfilter those 2 columns
User
12/10/2018 - 11:46
f and c
User
12/10/2018 - 11:46
I need to see the spend by column N as per supplier
User
12/10/2018 - 11:46
suppliers over 10K
User
12/10/2018 - 11:47
column N is department
Excelchat Expert
12/10/2018 - 11:47
Can you tell me a row number that should be deleted?
User
12/10/2018 - 11:47
deleted?
Excelchat Expert
12/10/2018 - 11:48
You wanted me to remove rows? Or I misunderstood you? please correct me if I'm wrong.
User
12/10/2018 - 11:48
i need a pie or some other chart to show by column N
Excelchat Expert
12/10/2018 - 11:48
I interpreted narrow down as to delete rows.
User
12/10/2018 - 11:48
the spend for each deptartment
User
12/10/2018 - 11:48
per supplier
Excelchat Expert
12/10/2018 - 11:48
Oh I see.
User
12/10/2018 - 11:49
would a pivot table work?
User
12/10/2018 - 11:49
or a formula?
Excelchat Expert
12/10/2018 - 11:49
Okay, I think this is the time when I should ask you to show me 1 or 2 sample result you are looking for.
User
12/10/2018 - 11:49
i dont know
User
12/10/2018 - 11:49
thats why i am asking you
User
12/10/2018 - 11:50
for suppliers over the amount of 10K
User
12/10/2018 - 11:50
i need to to look by dpeartmetn(column N)
Excelchat Expert
12/10/2018 - 11:51
Is this a some sort of homework/task given to you with a set of instructions?
User
12/10/2018 - 11:51
should i use a pivot table?
User
12/10/2018 - 11:51
or do it on this excel
Excelchat Expert
12/10/2018 - 11:51
Just to make it clear, the sentence "i need to to look by dpeartmetn(column N)" does mean anything yet.
Excelchat Expert
12/10/2018 - 11:51
So I'm not sure what exactly you need and thus can't suggest anything.
User
12/10/2018 - 11:52
break down the spreadsheet by column N
User
12/10/2018 - 11:52
Looking at supplier column (with total values over 10K)
User
12/10/2018 - 11:52
makes sense?
Excelchat Expert
12/10/2018 - 11:53
Column N is department and column A is supplier, you need to see the departments for each supplier for which the sum of amount is more than 10K?
User
12/10/2018 - 11:53
yes
User
12/10/2018 - 11:54
if not possible - then just descedning amounts
Excelchat Expert
12/10/2018 - 11:54
Are you absolutely sure that is what you need?
User
12/10/2018 - 11:54
yes
Excelchat Expert
12/10/2018 - 11:54
It is possible.
Excelchat Expert
12/10/2018 - 11:54
We can use pivot table.
User
12/10/2018 - 11:54
grouped by suppliers
User
12/10/2018 - 11:54
yes
User
12/10/2018 - 11:54
how>
User
12/10/2018 - 11:54
only for values > 10K
User
12/10/2018 - 11:55
total values
User
12/10/2018 - 11:55
per supplier
Excelchat Expert
12/10/2018 - 11:55
Pivot table or formula. both would work.
User
12/10/2018 - 11:55
can u do it for me please
User
12/10/2018 - 11:55
and email it
Excelchat Expert
12/10/2018 - 11:55
Yeah, please give me 10 to 15 minutes.
User
12/10/2018 - 11:56
ok
Excelchat Expert
12/10/2018 - 11:56
I will send the file here. But if you need to go, I will have support team to email it to your registered email.
User
12/10/2018 - 11:56
i am here
User
12/10/2018 - 11:56
there is 10 min left on this
Excelchat Expert
12/10/2018 - 11:57
Please stay with me.
User
12/10/2018 - 11:57
ok
Excelchat Expert
12/10/2018 - 11:57
If needed we can extend for another 20 minutes.
Excelchat Expert
12/10/2018 - 11:57
Although we only have that one extension left.
User
12/10/2018 - 11:58
you said its easyish to do
User
12/10/2018 - 11:58
with pivt
User
12/10/2018 - 11:58
pivot
Excelchat Expert
12/10/2018 - 11:58
Yeah. I'm creating the pivot right now.
User
12/10/2018 - 11:58
okay
User
12/10/2018 - 11:58
pls send thro once donw
User
12/10/2018 - 11:58
done*
Excelchat Expert
12/10/2018 - 11:58
I will, please stay with me.
User
12/10/2018 - 11:59
:)
User
12/10/2018 - 12:00
and can you please send an explanation with the file
User
12/10/2018 - 12:00
thanks
Excelchat Expert
12/10/2018 - 12:00
Of course, the explanation is also a part of our service.
User
12/10/2018 - 12:00
ok
Excelchat Expert
12/10/2018 - 12:06
Thanks for waiting, I'm sending back the file.
User
12/10/2018 - 12:07
ok
User
12/10/2018 - 12:11
have u sent it?
Excelchat Expert
12/10/2018 - 12:11
It is still being uploaded.
User
12/10/2018 - 12:11
ok
User
12/10/2018 - 12:12
was it diffficult?
User
12/10/2018 - 12:12
how did you do it/
Excelchat Expert
12/10/2018 - 12:12
https://drive.google.com/file/d/1xVqNohR2EFdQfxZ8SNTzIeTn1omrjv4p/view?usp=sharing
Excelchat Expert
12/10/2018 - 12:13
Here is the link to the file. Please download and check.
Excelchat Expert
12/10/2018 - 12:13
Please confirm that it is what you were looking for.
Excelchat Expert
12/10/2018 - 12:14
What I did was methodical, you can do it easily if you know how to do it.
Excelchat Expert
12/10/2018 - 12:14
I will share the steps once I get the confirmation that I was able to address your problem.
User
12/10/2018 - 12:15
tryig to download it
Excelchat Expert
12/10/2018 - 12:15
Sure. I'm here.
User
12/10/2018 - 12:16
i see amounts below 10K as well
Excelchat Expert
12/10/2018 - 12:16
please click on the filter on sum of amounts
Excelchat Expert
12/10/2018 - 12:17
the 3rd filter icon on column B.
Excelchat Expert
12/10/2018 - 12:18
[Uploaded an Excel file]
User
12/10/2018 - 12:18
i have
Excelchat Expert
12/10/2018 - 12:18
please see the screenshot.
Excelchat Expert
12/10/2018 - 12:18
Once you are there, click on the numbers filter and then click on greater than
Excelchat Expert
12/10/2018 - 12:19
Have you found it?
User
12/10/2018 - 12:20
yes
User
12/10/2018 - 12:20
why is there a Direct T under Direct T
User
12/10/2018 - 12:20
ignore that
User
12/10/2018 - 12:20
i just saw
Excelchat Expert
12/10/2018 - 12:20
Okay.
User
12/10/2018 - 12:20
if i now had to use that data
User
12/10/2018 - 12:20
on a pie chart
User
12/10/2018 - 12:21
do i read it off the pivit table?
Excelchat Expert
12/10/2018 - 12:21
Yeah.
Excelchat Expert
12/10/2018 - 12:21
Just so that you know, we are in the last 5 minutes of the chat.
User
12/10/2018 - 12:21
i saw
Excelchat Expert
12/10/2018 - 12:21
So the instruction for pivot table is --
User
12/10/2018 - 12:22
yes please
Excelchat Expert
12/10/2018 - 12:22
1. Select all your data, click on Insert from excel ribbon, then click on Picot Table.
Excelchat Expert
12/10/2018 - 12:22
This will bring a popup table, just click on OK.
Excelchat Expert
12/10/2018 - 12:23
2. Now you will see a pivot table builder, there first tick the Direct(T) box and then tick supplier ID box
Excelchat Expert
12/10/2018 - 12:24
Make sure that these two are showing in the Rows box.
User
12/10/2018 - 12:24
i am following
User
12/10/2018 - 12:24
and loving it
User
12/10/2018 - 12:24
yh
Excelchat Expert
12/10/2018 - 12:24
3. Tick the Values box and make sure it is showing in Values box.
Excelchat Expert
12/10/2018 - 12:24
It should also read sum of values in values box.
Excelchat Expert
12/10/2018 - 12:25
3. Now tick the boxes of Contract Status and Comment and drag them to the Filter box.
Excelchat Expert
12/10/2018 - 12:26
Now you will see two differ filters at the top of pivot table. Apply the necessary filters that you have set on your data in column C and F.
User
12/10/2018 - 12:26
i await your email as well with these instructions - thanks a million :)
User
12/10/2018 - 12:26
we cutting off soon
Excelchat Expert
12/10/2018 - 12:26
4. Now set your mouse pointer just right to the cell where is says SUM Of values
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.
