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.
