VBA Macro to help find and replace values

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:

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:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

ITWare2008

Board Regular
Joined
Apr 16, 2010
Messages
174
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,884
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Sep 30, 2014
Messages
82
@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

Board Regular
Joined
Sep 30, 2014
Messages
82
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,516
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top