Free Microsoft Excel 2013 Quick Reference

Unique Values from Two Columns


I have two columns which I've converted into tables.

I need a formula to fill in a third column, on a second sheet, which will pull all the unique values from the two tables.

I've got this array formula going, but it only works for one of the columns.


Thanks in advance!

Post your answer or comment

comments powered by Disqus
I have two columns in excel A and B. Now I need to create third column based on unique values from these two columns.

Please help me out on the same

I'm trying to find a way to extract unique data from two columns within an excel spreadsheet. There are nearly 70,000 records in each column. 2007 as you know has moved beyond the 2x16 rule. I'm trying to compare each record in both columns and if the data is in column a but not in column b I want to list it in column c. Any ideas on how to do this. I've tried a couple of MS solutions and they are able to identify the uniqe records but not place them into a separate column. I don't want to go record by record copying and pasting.

Thanks for you help and ideas!


The formula used to count the number of unique values in two columns in the same tab is easy and given by excel (2007) help, but how do I take two columns for two different tabs (or even workbooks) by a formula?

the formula I would like to use is something like;

of course this does not work. Can someone help me make the combined columns I am trying to do work?


I have a list of values that are not in order yet some repeat. I would like to create a column next to my current column of values that has each unique value from the orginal column. For example, say Column A has 20 values in Rows 1 to 20 and there are 7 unique values in Column A. I would like Column B to list the seven unique values within Column A in Rows 1 to 7 in the order they appear in Column A.

I have been looking for an entire day to find a solution to this. Found
'some' answers but not the thing I need.

I got a few workbooks with a lot of worksheets. For almost every workbook
there needs to be a list of unique values coming from different worksheet
(always in the same workbook). I would like to get a clue on how to do that.

So suppose worksheet 1, and worksheet 2 I would like to create a list on
worksheet 3 gathering all unique values from columns B (of both worksheet 1
and 2).

So far I have been directed towards VBA, some commercial add-ins and the
classic copy and paste.
But you can imagine that I do not like to pay for such a (in my opinion)
basic function, nor copy over 20.000 rows (risking a lot of errors and
'forgotten' rows) and VBA well... I'm not good at it...

I tried to filter data, but can only do that for a single column. And I
really need a list, so dropdown won't do...

Suggestions very much appreciated.

I have a workbook (Excel 2003) that gets data from a Database Query and returns values to sheet1/column 1 and sheet2/column 2. I would like to combine the values in column 1 of sheet3. By combine, I mean copy all values from sheet1/column 1 to sheet3/column 1, then once a blank cell is found on sheet1/column 1, append values from sheet2/column 1 to sheet3/column 1. See example below:

Sheet1/Column 1


Sheet2/Column 1


Sheet3/Column 1(Desired Result)


I'm guessing this would require VBA code to accomplish, unfortunately something I am not very experienced with. Any help would be greatly appreciated.

I have an Excel document that I want to count the number of rows that meet a text value in two columns.

Column B has a value of either "Open" or "Closed".
Column C has a value of either "High", "Med", or "Low".

I want to count the number of rows that are "Open" "High", "Open" "Med", and "Open" "Low".

I've been studying a number of different formulas, but I can't seem to get my head wrapped around this functionality.

Thank you to anyone who may be able to help me with this.


I need help with creating a formula that returns value(s) similar to data/advanced filter/unique records/copy to location. I have one column of sorted numbers with many duplicates. Without using the data filter, I want to create a formula on another sheet that returns a list of the unique values from that column (excluding blanks). I have stumbled through a combination of OFFSET, INDEX, MATCH and COUNTIF functions but can't seem to get a formula that works that I can also copy down the rows. Any Ideas?

I need to get the unique values between 2 columns. For example, if A has
2,3,4,5 & 7 and B has 1,2,3,4,and 5, I need something to tell me that B
doesn't have 7. I expect to run through it again so that I see that A doesn't
have 1. I do not wish to enter in the data to search for, but rather have a
column comparison and kickout the differences (similar to a diff command on
Unix for those that know it).

Any help would be appreciated. The advanced filter doesn't do what I need
as it would give me all values.

Microsoft Excel 2000:
Is it possible to count values from two columns. I know that to count
values from one column the formula is =COUNTIF(B2:B20,"NEW"), but I need to
count a value from a second column also. For example:
Column A Column B
NEW critical
NEW critical
CLOSED major
NEW major

I need a formula that will count the value for "NEW" in Column A that are
"critical" in Column B. The count for this example should be 2. The count
for NEW - major should be 1 and CLOSED - major should be 1. Can anyone help?

Hi, My problem is probably very easy for your guys, I just can't figure this out.
I have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.

I have a problem, using a macro I have already filtered a data set for
a unique value using the autofilter method. I now want to obtain the
unique values from another column within this range. When I try and
apply and advanced filter to generate the unique values it automtically
takes off the advanced filter ruining my filter range.

Is there a way I can do this while still using the advanced filter

Hiii all,
Plllease helpp. what is the method to concatenate values from two columns??
I want to obtain the values from three different columns in excel and want to check if those values are concatenated and present exactly in the fourth coulmn .

For eg.If column 1 has-PT50
column2 has-456
and column3 has -abc

in column 4 must contain PT50456abc.Otherwise itmust be colored in red. can we use '&' for it.? Please find the code.Its showin error.

Sub newone()

Dim res As String

For i = 4 To 8

res = "PT50" & Cells(i, 3).Value&Cells(i, 5).Value&Cells(i, 15).Value
If Cells(i, 16).Value <> res Then
Cells(i, 16).Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With

End If

Next i
End Sub

Hi Guys,

I've put together this code (with alot of help from this site may i add!), it uses the column number to populate a combobox with the all the unique values within the column. I want to apply the bubble sort method to ensure that the items are arrange alphabetically (or numerically). I just dont know how to mix the two together...

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim dic As Object, x, r As Range
Dim headcolnum As Long
Set ws = Worksheets("DATA")
Set dic = CreateObject("Scripting.Dictionary")
headcolnum = "20"
With ws
    For Each r In .Range(Cells(1, headcolnum), .Cells(65536, headcolnum).End(xlUp))
        If Not IsEmpty(r) Then
            If Not dic.exists(r.Value) Then
                dic.Add r.Value, Nothing
            End If
        End If
End With
    x = dic.keys
    Me.ComboBox1.list = x
End Sub
And the bubble sort, from a previous post (from RoyUK):

 'Two loops to bubble sort
   For i = 1 To cColl.Count - 1
        For j = i + 1 To cColl.Count
            If cColl(i) > cColl(j) Then
                'store the lesser item
               vTemp = cColl(j)
                'remove the lesser item
               cColl.Remove j
                're-add the lesser item before the
               'greater Item
               cColl.Add vTemp, vTemp, i
            End If
        Next j
    Next i
    'fill combo
    For i = 1 To 4
   For Each vItm In cColl
      Controls("ComboBox" & i).AddItem vItm
    Next vItm
I don't think its very difficult to do, I just cant make it work, so any help would be appreciated!

Thankyou in advance,


Code Excel.xlsxHi,

Please help.

I have a value in a cell G1 = 1000.

Column B1 has value 99,35,48,25,96 etc (going down)

Column C1 has 65, 1000, 86,45,32 etc (Going down)

Column A1 has 101,102,103,104,105 etc.

The value in G1 is dynamic means it can be 1000 or 86 or 48 or 99 etc.

Please help in find a formula such that when G1=1000, it has to search for 1000 in Column B1 and C1, and give the corresponding row value in Column A1 in another cell F1. In this case, I should be getting 102 in cell F1. If G1=45, the value from A1 should be 104 in cell F1. If its 25, A1 should be 104 in cell F1. If its 99, A1 should be 101 in cell F1.

Please Help.

Thanks a lot. Been bugged down for two hours.

Also, if there are multiple cells having the same value that is seeked, the value in F1 should be able to show the corresponding value in Column A for the First Exact Match. For Eg, if Column B1 has 1000,1000,1000,1000,1000 and Column C1 has 1000,1000,1000,1000,1000, the Cell F1 should show 101 in F1. If its 89,1000,1000,89,100 in B1 and 85,100,1000,1000,1000, F1 should be able to show 102.

Thanks a lot.

Sorry been trying to upload the file but I am not able to do it from my system. Will upload once i get home from office.

Please find attached the excel sheet. Thanks..

I need to retrieve all the unique values on from column C on a sheet called "Enter Data Here", and copy them to column B on a sheet called "Combo"

The advanced filter will not work for me, because I need the list of unique values to auto-update so that I can create a pivot table from the sheet named "Combo".

Is there a Macro that can be used for this? Or a formula that does not require immense processing times?


Hello everyone, this formula problem has been twisting my brain for a while. I have two nonadjacent columns, one containing numbers, the other alphanumeric data. As an example:



Please help.

I have a value in a cell G1 = 1000.

Column B1 has value 99,35,48,25,96 etc (going down)

Column C1 has 65, 1000, 86,45,32 etc (Going down)

Column A1 has 101,102,103,104,105 etc.

The value in G1 is dynamic means it can be 1000 or 86 or 48 or 99 etc.

Please help in find a formula such that when G1=1000, it has to search for 1000 in Column B1 and C1, and give the corresponding row value in Column A1. In this case, I should be getting 102. If G1=45, the value from A1 should be 104. If its 25, A1 should be 104. If its 99, A1 should be 101.

Please Help.

Thanks a lot. Been bugged down for two hours.


I have a large spreadsheet with two columns of data (one name and the other type - see attached sample spreadsheet). I am looking to extract unique values from the name column if the type is "Adult". Next, I want to then alphabetize the unique values. How would I do that in Excel using only formulas (no VBA).

Many thanks in advance.

Hi there. Been trying to work this one out for a while, but have hit a brick wall. Any ideas / help would be appreciated.

From the example data below, which summarises a series of meetings that we have hosted with different firms attending, i'm trying to get a formula to work out how many unique 'Group' meetings there were.

i.e. from the data below, 4 firms attended a group meeting at 12:00 on 17/10/2007, so that needs to be counted as one unique group meeting.

I can use sumproduct and countif (or frequency) to work out how many unique values there are within one column, but I can't work out how to sum up the number of unique entries across two columns (date and time).

Please note that this needs to be done as a sumproduct because the sample data below comes from a larger data set.

Any thoughts?

Date StartTime MeetingFormat FirmName
17/10/2007 09:00 One-on-one Firm 1
17/10/2007 10:30 One-on-one Firm 2
17/10/2007 12:00 Group Firm 3
17/10/2007 12:00 Group Firm 4
17/10/2007 12:00 Group Firm 5
17/10/2007 12:00 Group Firm 6
17/10/2007 13:30 Group Firm 7
17/10/2007 13:30 Group Firm 8
17/10/2007 13:30 Group Firm 9
17/10/2007 15:00 One-on-one Firm 10
06/03/2008 09:00 One-on-one Firm 11
06/03/2008 10:30 One-on-one Firm 12
06/03/2008 12:00 Group Firm 13
06/03/2008 12:00 Group Firm 14
06/03/2008 12:00 Group Firm 15
06/03/2008 12:00 Group Firm 16
06/03/2008 12:00 Group Firm 17
06/03/2008 13:30 Group Firm 18
06/03/2008 13:30 Group Firm 19
06/03/2008 13:30 Group Firm 20
06/03/2008 13:30 Group Firm 21
06/03/2008 15:00 One-on-one Firm 22
16/04/2008 09:00 One-on-one Firm 23
16/04/2008 10:45 One-on-one Firm 24
16/04/2008 12:00 Group Firm 25
16/04/2008 12:00 Group Firm 26
16/04/2008 12:00 Group Firm 27
16/04/2008 12:00 Group Firm 28
16/04/2008 12:00 Group Firm 29
16/04/2008 12:00 Group Firm 30
16/04/2008 13:30 Group Firm 31
16/04/2008 13:30 Group Firm 32
16/04/2008 13:30 Group Firm 33
16/04/2008 13:30 Group Firm 34
16/04/2008 13:30 Group Firm 35
16/04/2008 15:30 One-on-one Firm 36

Hi All,

I have Four Columns, which has duplicate values in it. I need a macro where it searches for unique values and pastes in the new worksheet.

Please find attached data sheet it has two tabs one with data and second one with the desired output. I have data in about 5000 rows

Thanks for your help in advance!!!


I have 4 columns of data. Column F contains property #, Column G contains Line of Business, Column H contains Department and Column I contains a $ Amount. I have 300 rows of this data and what I am trying to do is to sum (from column H) up all unique values for column F, G, and H combined.

So if 2 rows in different parts of the spreadsheet have as an example:

Row 1 - Property 17851 - Line of Business 11 - Department 100 $ 500.00
Row 300 - Property 17851 - Line of Business 11 - Department 100 $ 500.00

I want the $ amount in column I add up to $1000 and be able to show that in another tab so it shows 17851-11-300-$1000.00

The complicated part is I need to do that for each unique code combinations of F Through H.

Any help here would be greatly appreciated.

Trading Add-in For Excel|| Convert/Migrate Databases

I have a list of cost center codes (SMH1020, 5K20...). They are from an export from another database. There are numerous duplicates in the list which is very long. I want to extract the unique values from that list(Column A) and place them in a different list(Column B). I want this to be dynamic so if I past in a different list of values it will automatically update the unique list.
I have tried auto filter but it is not a dynamic function. You have to redo it every time you add something to the list.
I have tried other formulas listed elsewhere but they don't seem to work.

Any help would be appreciated.

Hello All,

I have about 10,000 rows in columnA sheet2 that I want to automatically filter on unique values and then create a dropdown in cell B1 on Sheet1 with those unique values. I want to accomplish this using vba with out having the data validation reference any cells if possible. Can you have data validation reference an array that you create based on unique values in a column on another sheet? Help would be greatly appreciated! This will be an addition to a long macro so the smaller/less time consuming the better.

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