VBA Macro to help find and replace values

[kccosavannah]

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:

[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!!

 

[ITWare2008]

Hi there,

I would like you answer a few questions in order to get a better understanding of what you want.

The quote and sub-quote numbers are in the same row?
Are the sub-quotes sorted in any way?
The quote numbers exists in two different sheets, sheet1 and sheet2? And you would like to put "Duplicated" in front of quote number which have sub-quotes in sheet1 for instance?

Vândalo

 

[Rick Rothstein]

Are those three sub-quotes in a single cell or in three separate cells?

What columns are the quotes and sub-quotes in?

Is there any other data on the sheet?

 

[kccosavannah]

@ITWare2008
How I posted it is exactly how it shows. Meaning the quote is in one column and the sub-quotes are in another column. Each one of them is in their own cell. There are blank cells below the quote and to the left of the other sub-quotes.
The sub-quotes are sorted from smallest to largest and so are the quote numbers. The sub-quotes are dependent on each quote.
The quote numbers do appear in both sheets as the example I provided was made from a pivot table made from the main data source. There is a column in the main data sheet that pertains to the "status" the quote is in, I want to change that columns data to "Duplicate" for that sub-quote.

Note: On the main data sheet, the same sub-quote number can appear more than once, but is always next to each other. So for example one sub-quote number may appear 4 or 8 times consecutively in one column and I will need to change all of those "status" column into Duplicate.

@Rick
On the pivot table, column A for Quote and column B for sub-quotes
On the main data sheet there is much more data. On the pivot table there is no more data except for filter headers which have already been applied.

 

[kccosavannah]

After giving it some thought I think the best track would be to do some conditional formatting.

If I rank the sub-quotes in order of largest to smallest, I could say that if there is a blank space in column A then the value in column B needs to be highlighted.

Then tell it to take what is highlighted, go to the main sheet, find that value and mark the appropriate cell as duplicate.

Thoughts?