Help with advanced match, Cut, insert, Sort VBA.
I have been doing VBA for a couple months now at my work updating and making worksheets better but what is needed on this one workbook is way to advanced for me and I really need some help. I have attached an example sheet.
New items print everyday and a worker keys them into the sheet below the yellow line. There are hundreds listed. When an item is ready to go out for service the worker will put a date in column E above the yellow line, as seen in the example as 10-May-2018. Worker then has to cut and paste the items from below the yellow line to above the yellow line under the date but only the ones that are ready to go, not all will bit. What I would like to happen is- When the worker lists a date in column E the sheet locates any rows with that same date but in column K (pick up date) and then cuts and paste the row under the date the worker has listed in column E but it must insert and not paste because the rest of the sheet needs to shift down. Some of the issues are the sheet is ever changing and growing. There are thousands of records above the yellow line and it grows weekly so being specific to row numbers is impossible. Also, in a perfect world, When the items cut and paste below the date listed in E we need them alphabetized by column G but only for that dated section and again row numbers cant be referenced because it is ever changing. I am not sure that part is even possible so if it isn't then just help with the first part would be amazing.
When items cut from below the yellow line and paste in the proper manifest dated section we need the rows where the info was cut from to delete so we don't have a bunch of blank lines through data.
PLEASE help! and thanks in advance. I have tried several things but run into issues with column E having the dates and the 2 letter INV code. Also cut , insert into the same sheet seems to be difficult and finding examples to even get an idea from has been so difficult.
test-for-help.xlsx
New items print everyday and a worker keys them into the sheet below the yellow line. There are hundreds listed. When an item is ready to go out for service the worker will put a date in column E above the yellow line, as seen in the example as 10-May-2018. Worker then has to cut and paste the items from below the yellow line to above the yellow line under the date but only the ones that are ready to go, not all will bit. What I would like to happen is- When the worker lists a date in column E the sheet locates any rows with that same date but in column K (pick up date) and then cuts and paste the row under the date the worker has listed in column E but it must insert and not paste because the rest of the sheet needs to shift down. Some of the issues are the sheet is ever changing and growing. There are thousands of records above the yellow line and it grows weekly so being specific to row numbers is impossible. Also, in a perfect world, When the items cut and paste below the date listed in E we need them alphabetized by column G but only for that dated section and again row numbers cant be referenced because it is ever changing. I am not sure that part is even possible so if it isn't then just help with the first part would be amazing.
When items cut from below the yellow line and paste in the proper manifest dated section we need the rows where the info was cut from to delete so we don't have a bunch of blank lines through data.
PLEASE help! and thanks in advance. I have tried several things but run into issues with column E having the dates and the 2 letter INV code. Also cut , insert into the same sheet seems to be difficult and finding examples to even get an idea from has been so difficult.
test-for-help.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ejgid -
Thank you, I wouldn't have an issue separating the items into 2 sheets if i could accomplish what I need. I opened the sheet you attached and it didn't do any moves when I entered a date though. The only issue with having the 2 sheets is I still don't know how to make the items from the input sheet move to the correct location on the service sheet. Because the service sheet has thousands of records. We use the service sheet to create a weekly log that we print and give to the process server. So each week is separated. It doesn't just show when something was serviced. The more important info on it is the date by which is was picked up, column J. So like I need all items with the same date in column J grouped together and then of those like items alpha by column F, name. I know how to do VBA for a 2 tier sort, as well as get items to move from one sheet to the next ,so I could do that. But if I went that route, with having the items move from the input sheet to the service sheet and then have the service sheet sort by column J and then F, I don't know how to separate each different pick up date in column J with, say, 2 empty rows. So that we have a break in-between each chunk of pick up dates. Currently the worker makes the break manually and inputes a date in column E to be the "header" of the manifest for the pickly pickup. So we have a manually made separation between the weekly pick up dates. Any assistance would be great!
thanks again!
Thank you, I wouldn't have an issue separating the items into 2 sheets if i could accomplish what I need. I opened the sheet you attached and it didn't do any moves when I entered a date though. The only issue with having the 2 sheets is I still don't know how to make the items from the input sheet move to the correct location on the service sheet. Because the service sheet has thousands of records. We use the service sheet to create a weekly log that we print and give to the process server. So each week is separated. It doesn't just show when something was serviced. The more important info on it is the date by which is was picked up, column J. So like I need all items with the same date in column J grouped together and then of those like items alpha by column F, name. I know how to do VBA for a 2 tier sort, as well as get items to move from one sheet to the next ,so I could do that. But if I went that route, with having the items move from the input sheet to the service sheet and then have the service sheet sort by column J and then F, I don't know how to separate each different pick up date in column J with, say, 2 empty rows. So that we have a break in-between each chunk of pick up dates. Currently the worker makes the break manually and inputes a date in column E to be the "header" of the manifest for the pickly pickup. So we have a manually made separation between the weekly pick up dates. Any assistance would be great!
thanks again!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ejgil -
This is EXACTLY what I need in everyway except one thing which I hoped to be able to change myself but wasn't able to.
The items above the yellow line show a weekly manifest dating back about 2 years (when we started tracking our service). So this line of code
If ws.Range("K" & rw) = ws.Range("E2") Then... when it references E2 the date that column K should compare to will not always be in E2. It will be different every time because weekly the list grows. I attached a different example. The most recent weekly pick up is closest to the yellow line but if you scroll up you will see weekly manifest going backwards. I tried changing the code you had from E2 to just E and it didn't like it.
Otherwise this is exactly what I need. I need it to match a date found in column E with any of the same date dates in column K and then move the items with that date from column K to below the matched date in column E. The sort and renumber feature for column A is perfect .
Is it even possible for it to move the K items based on a date listed in E but not referencing a specific cell and also only look for a date and not the 2 alpha characters that can also be listed in column E.
Again thank you so much for helping with this.
Kira-Knight-test-for-help-2.xlsm
This is EXACTLY what I need in everyway except one thing which I hoped to be able to change myself but wasn't able to.
The items above the yellow line show a weekly manifest dating back about 2 years (when we started tracking our service). So this line of code
If ws.Range("K" & rw) = ws.Range("E2") Then... when it references E2 the date that column K should compare to will not always be in E2. It will be different every time because weekly the list grows. I attached a different example. The most recent weekly pick up is closest to the yellow line but if you scroll up you will see weekly manifest going backwards. I tried changing the code you had from E2 to just E and it didn't like it.
Otherwise this is exactly what I need. I need it to match a date found in column E with any of the same date dates in column K and then move the items with that date from column K to below the matched date in column E. The sort and renumber feature for column A is perfect .
Is it even possible for it to move the K items based on a date listed in E but not referencing a specific cell and also only look for a date and not the 2 alpha characters that can also be listed in column E.
Again thank you so much for helping with this.
Kira-Knight-test-for-help-2.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ejgil -
I love that, everything is principle is possible. Your change works but see my 2 examples first would be how I would do the upcoming week to go out for service and the second is what happens when I hit move data.
Example 1
To prepare this weeks manifest I would skip a row from last weeks manifest, just so there was some separation between the weeks and then in E26 I would type 19-June-18. I would then hit merge and center with F26 (only using row 26 for an example as the row will change weekly) . I would then go below the yellow line and put in a pick up date of 6/19/18, in column K, for each item that is ready to go. I would then hit the "Move data button".
Example 2
shows what happens when I hit that button.
I have to upload example 2 separately I guess. It won't let me upload 2 files.
Again thank you so much for taking time out of your days to assist me in this.
Kira-Knight-test-for-help-example-1.xlsm
I love that, everything is principle is possible. Your change works but see my 2 examples first would be how I would do the upcoming week to go out for service and the second is what happens when I hit move data.
Example 1
To prepare this weeks manifest I would skip a row from last weeks manifest, just so there was some separation between the weeks and then in E26 I would type 19-June-18. I would then hit merge and center with F26 (only using row 26 for an example as the row will change weekly) . I would then go below the yellow line and put in a pick up date of 6/19/18, in column K, for each item that is ready to go. I would then hit the "Move data button".
Example 2
shows what happens when I hit that button.
I have to upload example 2 separately I guess. It won't let me upload 2 files.
Again thank you so much for taking time out of your days to assist me in this.
Kira-Knight-test-for-help-example-1.xlsm
ASKER
here is example 2
Kira-Knight-test-for-help-example-2.xlsm
Kira-Knight-test-for-help-example-2.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PERFECT! thanks so much.
Glad to help.
Then close the question by accepting the answer.
Then close the question by accepting the answer.
You would have your table of items (the data below the yellow line). The pivot would then have a selection option to choose a date and the table would update with the items with the selected date.
See attached. I have copied data to separate sheet and the summarised in Pivot Sheet. The Pivot can include as many columns as you would like from the source data. Pivot tables are designed for summarising numerical data against various text criteria but there's no reason why you can't use it just as an extract from the source data for a particular criteria, ie your pick up date.
With the pivot table, there is no need for visual basic; you just need to refresh the table. The data source can be set to be dynamic so that it always includes all data on the source sheet.
test-for-help.xlsx