Free Microsoft Excel 2013 Quick Reference

Macro to Find unique values in three columns

I am a newbie to excel macro. I need to write a macro for finding unique values between columns A,B,C. That means I need maco to write values which are unique in these three columns to column D.

Can any one help me in this...

Thanks a million.

Post your answer or comment

comments powered by Disqus
Ya'll will be glad to know that I am almost finished with that "project". I need to know how to do one thing though.

How would I write a macro to find duplicate dates in a column and then copy certain data from one of the duplicate dates' row, paste it on the other duplicate date and delete the line it just finished copying from. I know that made a lot of sense. I attached an example that shows what exist and what it should look like after. Here is a simple example


03/11/2003 07:00 43 34
03/11/2003 07:00 --- --- 45
03/11/2003 13:00 12 34
03/11/2003 18:30 24 57
03/11/2003 18:30 --- --- 67
03/12/2003 00:30 35 23

The dates are in column B
The data are in columns C, D, E, respectively

The macro should recognize the duplicate dates (03/11/2003 07:00) and copy the data from the bottom duplicate date (03/11/2003 07:00), which is "45"

It should then paste this number in the cell directly above it

Then it should delete the line it just copied from.

It should then recognize the other duplicate date (03/11/2003 18:30) and do the same procedure

When finished it should look like


03/11/2003 07:00 43 34 45
03/11/2003 13:00 12 34
03/11/2003 18:30 24 57 67
03/12/2003 00:30 35 23

Is this possible? and if so, could someone please help me out!


Here's a better representation of what the before looks like and the after should look like.


I'm trying to write a macro to find a value in a column and get some data from it's row. The value can be repeated several times. I wrote the following macro, but it's working only to find the first value. After that I'm getting this error:

Run-time error '1004':
Method 'Range' of object '_Global' failed

Really I don't know what is going on. I'll appreciate any help.


Sub Aux_10()

 Dim myrow, i As Integer
 Dim rng, cell1, cell2 As Range

 Windows("Thickness Data.xlsx").Activate
 WorkOrder = Range("A20").Value
 cycles = Range("D18").Value
 LastRow = Range("A40000").End(xlUp).Row
 Set cell2 = Range("A" & LastRow)
 i = 1
 NextRow = 1
 FirstRow = 22
 For i = 1 To cycles
 Set cell1 = Range("A" & NextRow + 1)
 myrow = Range(cell1, cell2).Find(WorkOrder).Row
 NextRow = myrow
 Range("B" & myrow).Copy
 Windows("Thickness Data.xlsx").Activate
 Sheets("Sheet1").Range("A" & FirstRow + i).Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 End Sub

I am searching for a formula that will enable me to count unique values on three rows(for example I have names in three columns that may repeat themselves (tech1,tech2,tech3)), but based on two other values. (ex: week# in one column and in another I have day or night work.) Can anybody help me out?


Hi. I need a way (can be a macro) to find duplicate words in a column, and then to copy those duplicated values to another column.

An example. My column looks like this:

Row1 - John Cage / Johnny Bravo / Rambo / Indiana Jones / Neo
Row2 - Frodo / Bruce Lee / Steve / Harry / John Cage

I need to find every single duplicate name, and then to copy them to another column. Problem is, I can't seem to find a macro that does that - since the macros I have look only for exact values in the rows, they don't recognize as duplicate anything in a row that simply has a partial identification with another row. For example, the macro I have does not recognize as duplicate the "John Cage"s in those two rows - because their respective rows aren't exactly equal, only two words in them are - the "John Cage"s. But I need to find those "John Cage"s.

Is there a solution for my problem?
Thank you for any advice you may be able to offer.

Hi everyone i am new in this forum and i need a solution for my excel problem.So here it is: I want to find unique values in columm A in a sheet named test1 nad column B in a sheet named test2 and put all of them(the unique values) in column a into a sheet named test3....Is this possible and how to do this with vba?
Thanks anyway..


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?


Howdy All,

I'm use the CountU function to count unique values in a column and it works

Now what I want to do is count the unique values in Column B which occur
between a specific date range in Column A.

Column A contains dates from 2005 through 2009.

Column B contains Order Numbers for each date.

I want to count the unique values in column B for only 2008.

Any Ideas?



Can anyone help me with this? i want a simple vba code to count unique values in a column(text as well as numbers).

thanks in advance for any help!

Is there a way i can have a macro find the unique values in these 3 columns and highlight them yellow.

They should find uniques using all 3 columns, not find them within each column.

I included a file which shows what i start off with and what it needs to look like. Also if it matters the contents in the cells are from links. ( i know that matters because you have to put look in values in the code ).

Thanks for any help this will help me out alot.

Hi All

I have a 'rota' worksheet that includes staff names and their rostered hours on all dates through the month.

On each date of the month I need excel to find that date in column A and then look across the row to find any cells that aren't blank. When the cell has a value I need the name above it in row 1 and the value (number of hours) itself to copy across to a 'daily activities' sheet. The date is autopopulating on this sheet in cell H5.

The name needs to drop into column A and the hours worked into column B. I would like the first cell to be A9 and then down from there with no spaces in between names. The other info on the sheet will then complete itself using the name that has been dropped in using VLookup.

I'm presuming I can use a macro to do this for me but am really struggling where to start with it, and how to ignore blank cells as they will change cells on a monthly basis.


Thanks in advance

I have Worksheet 2 that contains data. Row 5 contains the Manufacturer and Column A contains the product name. The remainder of cells contain a value. I need to start by looking for the lowest value in column B. If that lowest value is unique I want to return that value along with the header and product name to a row in Worksheet 1. (We'll say cells A1:C1). I then want it to look in column D of Worksheet 2 and repeat the process. If it didn't find a unique low value in column C, I would want the results from column D to appear in cells A1:C1. I would need this process to continue until it reaches column R.

I've wordsmith-ed my searches many times but have yet to find something that will help me with this. Is this possible to perform using VBA code?

I've uploaded a dummy spreadsheet. The first worksheet shows the results I should get back. The second worksheet shows the data. I've highlighted in yellow the items that are the lowest unique value in that column. The items in red are lowest, but duplicated in that column so I would not want to see them. (Normally they would not be highlighted in color)

I am wondering if anyone knows a way to change a list of values in a
column with either an Excel function or a macro. The column is in text
format and I have a list of text strings that I want to revise. I can
do it by filtering, but I may need to do the same process later. This
seems like a job for a macro, but am a novice at using them.

Thanks in advance


I want to get the unique values in a column for a particular value in another column.


Column X Column Y

1234 A
1234 B
1234 C
3456 A
5678 A
7896 A
7896 B

Here I want to find the values in column X whose value in column Y is "A".
But if the same field has B or C it should not be selected.
e.g. in this case my list should yield 3456 and 5678 because they have only "A" in column Y and not "B" or "C".

Need to create a Macro which convert the values in a column to Hyperlink.

When I am running the recorded macro it is always assigning the same link to all the hyperlinks.

I want when I click on 'A' it should open Sheet with the name specified in the column 'D'.
Click on A should open sheet 'AA'
Click on B should open sheet 'BB' and so on.

Please refer to the attached sheet.Book2.xlsm

I need to find a formula that will see identical values in a column of numerical data, and post a flag in a cell to warn the user to check those values for validity. Either a text warning such as "Check" or a change of cell colour if thats possible. Thanks in anticipation. Murray.

Hello can anybody help me with a macro to delete duplicate Values in a selected Column?

In Column A I have duplicate account numbers that I need to eliminate.



I dont need to eliminate the row, just any duplicate value that might occur.


Hi everyone

I want to be able to get the unique values in a column of my worksheet stored in an array or comma seperated string.

I am thinking- apply autofilter, then get excel to give me the values from the dropdown.

Dim r As Range 
Set r = Range("K1").CurrentRegion ' set up your range first
r.AutoFilter Field:=11 'What goes here now to get me all the unique values from the filter?

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am trying to print all unique values in a column after matching it with multiple criteria in an array.

For example,

Column A has (A,B,C,A,A), Column B has(1,2,3,1,2) and Column C has (X,Y,Y,X,)

I want to print all the unique values in Column C that match the criteria A (Column A) and 1 (Column B)

macro to find date format in a cell and delete that entire row

i have dates in a column and date keeps changing, i want that if
macro find date in a column , it deletes that entire row

help me
thank u so much everyone for ue hel

Message posted from

I have a table that has a column of states. I have formula that will give me
a list of all the unique values in that column:


I'd like to do the same with some vba code to create a new worksheet with
the values that it creates. Any suggestions?


Hi all,
My requirement is to find unique values in the whole excel sheet,i.e across all columns. I am aware of finding unique values in one column, but not in the whole sheet. My data would be like this
col 1 col2 col 3
sam andy sam
andy mark kathy
tim kim liz
kim liz mark

The output should be only sam,andy,mark,kathy,tim,kim,liz. I tried so many ways and searching in net. I am terribly sorry if this kind of question is already answered here. CAn someone help me in this regard.


My workbook prompts the user for his/her password via a macro using InputBox.

password = InputBox("Please enter your password")

Once entered, I need a macro to find that password in the hidden "Roster"
sheet of 200 users and return the user's name that is located in the next
column over.

I need to count unique values in one column and return the value in another sheet.
The return value sheet is OPA and the range sheet is ROPA. There are blank cells involved in the range. The range is A2:A65353

I found this code, could someone alter it for me so I can just plug it in?

Dim J As Integer 
Dim iNumCells As Integer 
Dim iUVals As Integer 
Dim sUCells() As String 
iNumCells = MyRange.Count 
Redim sUCells(iNumCells) As String 
iUVals = 0 
For Each Cell In MyRange 
    If Cell.Text > "" Then 
        For J = 1 To iUVals 
            If sUCells(J) = Cell.Text Then 
                Exit For 
            End If 
        Next J 
        If J > iUVals Then 
            iUVals = iUVals + 1 
            sUCells(iUVals) = Cell.Text 
        End If 
    End If 
Next Cell 
CountUnique = iUVals 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It is also instructed to put =CountUnique(MyRange) in the cell where I want the value returned.

Thanks a gazzilion

This is the rephrase statement from my earlier question about if(), i guess i confuse everyone with my example..Im so sorry...but this time i already placed the sample table.

Given the sample table, I need a help to take the value in C column if the employee number in columnE is = employee number in columnA. The formula will be placed in g2 and drag down: example E2 up to E10 has emp. No. of '10001' matching the emp. no. in A2 then I want the value of "Active File" to be placed in G2 until G10. Then G11 up to G14 shall have the value of "Inactive" since the value of C3 is "Inactive" for employee 10002 so on.

Thanks for helping

******** ******************** ************************************************************************>Microsoft Excel - Countif.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI10=
ABCDEFG1Employee No.Employee NameHire Status Employee NumberDependants NameRemarks210001Mohammad Ibrahim Al BibiActive File 10001Diana Geen Hani Al bibi 310002Sulaiman Khalid Al HomaidanActive File 10001Mohammad Ibrahim Al Bibi 410003Hassan Ahmad Yusuf Al SomaliInactive 10001Abdullah 510006****ud Musfer Al MaymouniInactive 10001Asmaa 610007Mubarak Fahad Al HajriInactive 10001Lamiya 710008Ibrahim Dawud Al BuainainActive File 10001Arwa 8 10001Sumaiah 9 10001Bushra 10 10001Abdulrahman 11 10002Sulaiman Khalid Al Homaidan 12 10002Latifa Saleh Abdullah Al Homaidan 13 10002Khalid 14 10002Juman 15 10003Hassan Ahmad Yusuf Al-Somali 16 10006****ud Musfer Al-Maymouni 17 10007Mubarak Fahad Al-Hajri 18 10008Ibrahim Dawud Al Buainain 19 10008Awatif Saleh Jasim 20 10008Salwa Ahmed Baga 21 10008Abdulrahman Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

No luck finding an answer? You could always try Google.