Finding Unique Values in two columns

[pmfresno]

Hi,

I would very much appreciate help on the following:

I would like to identify unique values in column A (Bill numbers) for a given date.

There are several rows with the same date in column B, and for a given date there may be several rows with the same bill number.

I would like a VBA sub that will identify all rows in Column B that match a user-defined date and copy only the corresponding unique values of column A.

Here is an example of the data:


Column A Column B
(Bill number) (Bill date)
GNYF008 11/01/2012
GNYF009 11/01/2012
GNYF010 11/01/2012
GNYF011 12/01/2012
GNYF012 17/01/2012
GNYF013 17/01/2012
GNYF013 17/01/2012
GNYF014 18/01/2012
GNYF014 18/01/2012
GNYF014 18/01/2012
GNYF014 18/01/2012
GNYF014 18/01/2012
GNYF015 18/01/2012
GNYF016 20/01/2012
GNYF017 24/01/2012
GNYF018 25/01/2012
GNYF018 25/01/2012
GNYF018 25/01/2012
GNYF019 25/01/2012
GNYF020 25/01/2012
GNYF020 25/01/2012
GNYF020 25/01/2012

Any suggestions of code I could use?

Thanks!

[rylo]

Hi

Any reason that you can't use the advanced filter?

rylo

[AlvaroSiza]

As rylo correctly pointed out, the advanced filter is preferable in this instance and is the route I took. Since you asked for code, I developed code. With that said, it's not entirely necessary.

**Disclaimer - I could not get the 'criteria' component of the advanced filter to work. Should someone point out the correct syntax, the for each loop I used would not be needed and the resulting code would be much simpler. (I take these on as puzzles, like others do suduko or crosswords).**

The following applies the advanced filter and sends the results to a temp worksheet. The resulting range is then analyzed for instances where the date entered in a named range in your workbook (which you will need to create) equals the date in the range. The corresponding Bill No. is then passed to the next available row in the original worksheet.


Always make a copy of your workbook and run the code on that copy. VBA can be difficult to undue and taking a few precautionary steps can save you a headache down the road. Please see my notes throughout for adjustment as needed.

Please Login or Register  to view this content.

[pmfresno]

Hi,

I could have used an advanced filter, but that wouldnīt really have helped me.

I need to create a virtual range containing the result of the advanced filter (unique bill numbers matching a given date).

Then, I would run a loop, pasting each of these values into a fixed cell in a bill form (sheet 1), and generating a pdf. I have an automated bill form that autofills all fields when user inputs the bill number.

So the loop would paste bill number, generate pdf, erase bill number, paste next bill number, etc... until end of range.

Thanks for the code! I donīt have much experience coding VBA, but I'll try to adapt it to suit my needs...

Thanks again!