kccosavannah
Board Regular
- Joined
- Sep 30, 2014
- Messages
- 82
I have been learning VBA macros through self teaching for a few months now and have written some great ones to assist in my work. I am now however at a point to where I can not figure out a macro to solve my current problem. I have tried searching but am not really sure what to search for...
I have a report that I do that I pull from a database and then I run a pivot table off of that. This is a report that runs from the first week of the year to whatever the current week is. So as you can see this report gets longer every week. Basically it is a list of quote numbers and associated with the quote number is a sub-quote number. Each quote should have one sub-quote. Sometimes the sales people are lazy and will write duplicate sub-quotes. The issue lies in that this throws the reporting off so sometimes I will see a quote with 3 sub-quotes. The sub-quotes number ascends in value so that the most current sub-quote has a higher number than the previous.
I need my macro to do the following:
1.Find the quotes with multiple sub-quotes
2. Take the multiple sub-quotes and copy those quote numbers, excluding the highest numbered
3. Go to the main data worksheet and find it then change a certain header to read "Duplicate"
Here is an example:
The bold is the quote number and the others are the sub-quotes, I need to keep the one ending in 221 but mark 220 and 977 as duplicates on the main data sheet. The quotes and sub-quotes are in separate columns.
I hope I explained it well enough to understand. I really like this forum and site as it has been an amazing help to me!
Thanks in advance!!
I have a report that I do that I pull from a database and then I run a pivot table off of that. This is a report that runs from the first week of the year to whatever the current week is. So as you can see this report gets longer every week. Basically it is a list of quote numbers and associated with the quote number is a sub-quote number. Each quote should have one sub-quote. Sometimes the sales people are lazy and will write duplicate sub-quotes. The issue lies in that this throws the reporting off so sometimes I will see a quote with 3 sub-quotes. The sub-quotes number ascends in value so that the most current sub-quote has a higher number than the previous.
I need my macro to do the following:
1.Find the quotes with multiple sub-quotes
2. Take the multiple sub-quotes and copy those quote numbers, excluding the highest numbered
3. Go to the main data worksheet and find it then change a certain header to read "Duplicate"
Here is an example:
Code:
[B]0007008168[/B] 0011617977
0011630220
0011630221
The bold is the quote number and the others are the sub-quotes, I need to keep the one ending in 221 but mark 220 and 977 as duplicates on the main data sheet. The quotes and sub-quotes are in separate columns.
I hope I explained it well enough to understand. I really like this forum and site as it has been an amazing help to me!
Thanks in advance!!
Last edited: