# Excel - COLUMN Function Problem - Expert Solution

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
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
Excelchat Expert 12/10/2018 - 12:13
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

## Click here to get your free Excelchat help session

Trusted by people who work at