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:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

ITWare2008

Board Regular
Joined
Apr 16, 2010
Messages
174
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
36,333
Office Version
  1. 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,351
Messages
5,528,192
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top