< Go Back

Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a workbook that is made up of 4 worksheets: ALL, Approved, Denied, Completed Essentially, in the ALL Worksheet, column K has Decision (Approved, Denied), I would like to move all with Approved to the Approved Worksheet and Denied to the Denied worksheet. ONce moved, I would like the rows to move up so their aren't blank rows. Also, on the Approved worksheet, there is a column M for Status. Once completed is selected in Column M, would like to move from Approved Worksheet to Completed Worksheet

Solved by I. U. in 31 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert 23/08/2018 - 08:50
Hi, welcome to Got it Pro-Excel!
Excelchat Expert 23/08/2018 - 08:51
According to my diagnosis, you need help with moving data with specific conditions to another sheet without spaces or blank rows, is that correct?
User 23/08/2018 - 08:51
Yes
User 23/08/2018 - 08:52
And need to be able to do it multiple times and add to the other worksheet as statuses change
Excelchat Expert 23/08/2018 - 08:53
I understand, will you be able to at least paste some data or create a dummy data so we can easily replicate what you have?
User 23/08/2018 - 08:53
sure-where?
Excelchat Expert 23/08/2018 - 08:53
On our shared sheet here
Excelchat Expert 23/08/2018 - 08:54
Or you can send the file over so I can work on it directly if it does not contain sensitive information.
User 23/08/2018 - 08:55
Column K - I need to move Denied to a new worksheet
User 23/08/2018 - 08:55
And anything with Approved - Phase 1
Excelchat Expert 23/08/2018 - 08:56
1 sheet for Denied, 1 sheet for Approved - Phase 1?
User 23/08/2018 - 08:56
Yes
User 23/08/2018 - 08:56
And then from there, anything on the Approved Phase 1 worksheet needs to be moved to completed once column M has Completed
Excelchat Expert 23/08/2018 - 08:57
Ok, let me do the first 2 sheets.
User 23/08/2018 - 08:57
Ok, thanks!
Excelchat Expert 23/08/2018 - 08:58
Give me a moment.
User 23/08/2018 - 09:00
ok
User 23/08/2018 - 09:04
Just checking in
Excelchat Expert 23/08/2018 - 09:05
Give me a moment, this requires a complicated formula.
User 23/08/2018 - 09:06
np
Excelchat Expert 23/08/2018 - 09:06
I can easily teach you the manual way of transferring files over using slicers instead of formula if you want.
User 23/08/2018 - 09:06
OK - if that is easier - I need to be able to do it repeatedly each day as statuses change/update
Excelchat Expert 23/08/2018 - 09:07
Ok, I can teach you that.
Excelchat Expert 23/08/2018 - 09:07
First, kindly extend our session. Don't worry it's free.
Excelchat Expert 23/08/2018 - 09:07
Ok, that's great.
Excelchat Expert 23/08/2018 - 09:08
Do you know how to create tables?
User 23/08/2018 - 09:08
Maybe, refresh my memory, just highlighting and naming?
Excelchat Expert 23/08/2018 - 09:08
Ok, so if you go to your data for example in the ALL sheet.
Excelchat Expert 23/08/2018 - 09:08
Click anywhere on your data
Excelchat Expert 23/08/2018 - 09:09
Then press Ctrl+T.
Excelchat Expert 23/08/2018 - 09:09
This is on your actual file not in our shared sheet.
User 23/08/2018 - 09:09
k
Excelchat Expert 23/08/2018 - 09:10
Did it prompted a Create Table window?
User 23/08/2018 - 09:10
yes
Excelchat Expert 23/08/2018 - 09:10
Ok, first I want you to check the My Table has Headers.
User 23/08/2018 - 09:10
k
Excelchat Expert 23/08/2018 - 09:10
then press Ok.
User 23/08/2018 - 09:10
k
Excelchat Expert 23/08/2018 - 09:11
Your data should now be a table.
User 23/08/2018 - 09:11
yes
Excelchat Expert 23/08/2018 - 09:11
Oh sorry, can I ask what MS Office version you are using?
User 23/08/2018 - 09:11
2018
Excelchat Expert 23/08/2018 - 09:11
That's great.
Excelchat Expert 23/08/2018 - 09:12
Ok, I want you to click on your table then go to Insert Tab.
Excelchat Expert 23/08/2018 - 09:13
Tabs are Home, Insert, Page Layout, Formulas, etc. on the top of your MS Excel.
User 23/08/2018 - 09:13
got it
Excelchat Expert 23/08/2018 - 09:13
Ok then on the Filters section. I want you to click Slicer.
User 23/08/2018 - 09:13
k
Excelchat Expert 23/08/2018 - 09:13
You should now see a new window with a lot of check boxes corresponding to your fields.
User 23/08/2018 - 09:14
Yes
Excelchat Expert 23/08/2018 - 09:14
You should see Type, Topic, Category, etc.
User 23/08/2018 - 09:14
yes
Excelchat Expert 23/08/2018 - 09:14
I want you to check only Decision which is your Column K
Excelchat Expert 23/08/2018 - 09:14
then press Ok.
User 23/08/2018 - 09:14
k
Excelchat Expert 23/08/2018 - 09:15
You should now see a new floating slicer window.
User 23/08/2018 - 09:15
Yes, I do
Excelchat Expert 23/08/2018 - 09:15
or your Decision slicer/filter.
Excelchat Expert 23/08/2018 - 09:15
From that you can easily click a decision then your table will be filtered instantly.
Excelchat Expert 23/08/2018 - 09:15
So for example, click Denied.
Excelchat Expert 23/08/2018 - 09:16
It will filter all the "Denied" Decisions.
User 23/08/2018 - 09:16
Ok, but this way won't move the data?
Excelchat Expert 23/08/2018 - 09:16
Yes, it won't but you can easily copy it now towards a new page.
Excelchat Expert 23/08/2018 - 09:17
This way you can also review it real time before you paste it over and since your data updates from time to time you can observe it real time.
Excelchat Expert 23/08/2018 - 09:18
Would that be a better solution for you?
User 23/08/2018 - 09:19
It may be. Thanks!
Excelchat Expert 23/08/2018 - 09:20
That's great! You can do that for your other sheets as well.
Excelchat Expert 23/08/2018 - 09:20
Would there be anything else I can assist you with regards to the original question and the solution provided?
User 23/08/2018 - 09:21
I don't think so, thanks!
Excelchat Expert 23/08/2018 - 09:21
If that's all, I'd like to wish you have a very nice day ahead of you. Thank you for using Got it Pro-Excel! Feel free to end the session and leave a rating if you liked our service.

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 privacy is guaranteed. Your session will not be used for blog unless you give us persmission.