Free Microsoft Excel 2013 Quick Reference

How to get unique value from two columns in a third colum

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

Post your answer or comment

comments powered by Disqus
How can I copy values from one cell in a column down to the next cell IF the
2nd cell is blank, For example:

A1 = 20
B1 =

I want the value of A1 to be copied to B1 if B1 is blank/contains a value of
Null, I have about 11,000 records like this, whe If the next cell in the
column is blank, then I need to copy the previous cells data into that cell.

How can I copy values from one cell in a column down to the next cell IF the
2nd cell is blank, For example:

A1 = 20
B1 =

I want the value of A1 to be copied to B1 if B1 is blank/contains a value of
Null, I have about 11,000 records like this, where: If the next cell in the
column is blank, then I need to copy the previous cells data into that cell.

Hi, everybody,Good morning!

How to creat an UDF getting unique values from multi worksheets ranges?
I have been being in the box of this question for a long time!
Would anybody like to smash it for me?

the Structure may be like:

End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The following Function GetUnik can get unique values from only ONE worksheets.

    Dim i As Integer, j As Integer 
    Dim Cell As Range 
    Dim NoDupes As Object 
    Dim a As Variant 
    Dim temp As Variant 
    Set NoDupes = CreateObject("Scripting.Dictionary") 
    For Each Cell In Rng 
        If Not NoDupes.Exists(Cell.Value) Then 
            NoDupes.Add Cell.Value, Cell.Value 
        End If 
    a = NoDupes.keys 
    For i = 0 To NoDupes.Count - 2 
        For j = i + 1 To NoDupes.Count - 1 
            If a(i) > a(j) Then 
                temp = a(j) 
                a(j) = a(i) 
                a(i) = temp 
            End If 
        Next j 
    Next i 
    GetUnik = a 
End Function 

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


How to get the value from cell of colume A and raw "C3+4", and input to B2? I
mean the fomula inputted in cell B2. Thanks.

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!



Pls. help. I'm having problem counting unique person in a number of Columns for each row. I only know how to count unique value if it is in 1 column.

WELDING 1 Allan Allan
WELDING 2 Allan Bob
WELDING 3 Bob Allan Bob
WELDING 4 David Carl
WELDING 5 Francis Francis Francis Francis
WELDING 6 Carl Ed Carl Carl
WELDING 7 Francis Francis Ed

NAME # of Welding (Formula in this column)

Allan 3
Bob 2
Carl 2
David 1
Ed 2
Francis 2

Sorry, I wanted to upload the file but I don't understand why I can't upload this time. I copied the example in this post from excel file and the values are not align properly.


The output of my excel model is a CashFlow (CF). I wrote a macro that runs the CF for a list of companies (as a loop, company A, then company B, etc) and storage (as values) the resutling CFs (one company = one worksheet) in a second file (which later is "saved as" whatever name the user defines). The worksheets of this second file are renamed with the name of the active company. The format for all of the worksheets is the same (of course, it comes from the Cash Flow output)

Now, I have been asked to create a "delta run", and I think that I have successfully created the code for most of the task. Basically I created the macro that after selecting the companies the user wants to run,
it goes and select the parameters for the first run, then
run each company in the loop while export the resulting CFs to a second file (lets call this second file A)
go back to the main model and re-set the parameters for the second run
run each company in the loop (again) and export the resulting CFs to a third file (lets call this second file B)
then creates a copy of the file A and rename it delta, and then reopen the file A
So now I have the 3 files that I need open (Delta, A and B) and what I need the macro to do is go to each worksheet in the delta file and make cell C16 (to say something) Delta!C16=A!C16-B!C16

ActiveCell.FormulaR1C1 = _ "='[A.xlsx]Company1'!RC-'[B.xlsx]Company1'!RC" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(I am sorry, but how should I use the code tags?)

Once I have the firs cell with the right formula then I can go and auto fill the rest of the cells in the CF
In a simple sentence: How can I insert functions (addition, subtract, etc) in a macro to relate cells from different files?

Thanks! and let me know if I wasn't clear in the explanation

I'm sure this has been asked before, and I have searched every avenue high and low before asking.
I have two columns that contain text number representing times, for my purpose I don't really need to change them to actual times, column A contains a start time and column B and end time, i am trying (and failing in VBA) to discern what would be an Early, Late or Night shift. Early commencing 600 (06:00) to 1059 (10:59), Night finishing after 300 (03:00) to 0800 (08:00). Shifts are no more than 12 actual hours in length and will result in either and E, L or N in another column as a result (no formula). Late runs between 1100 to 259, and not all shifts are full lengths (my problem).
what I need is VBA that will do the comparison between two columns in a single pass (I seem to currently be running four loops in my tests, which provide a result, but are quite variable.

Can someone please prod me in the right direction

Hello Everyone!

Here's my issue: I need to get a list of unique values from a particular column range (A1:A1000). But I only want the unique value if the cell 3 to the right (D1:D1000) = 'xyz'.

So to sum it up- Unique values from A1:A1000....where the matching cell D1:D1000 = xyz.

Any help on how to do this would be much appreciated!

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.


I have two files Book1 and Book2 (Book1.xls file path is "C:Book1.xls" and Book2.xls file path is "C:MacfolBook2.xls")

I am using VBA Code in Book1.xls and below shown code works only when the book2.xls is open and not when closed. So, can anyone please give me solution how to get the value from closed file using VBA Vlookup.

This Code Works only when the Boo2.xls file is open but my requirement is Book2.xls file should be closed but the values from it to be used without opening it.
MsgBox WorksheetFunction.Vlookup(Range("A2"), Workbooks("book2.xls").Sheets("sheet1").Range("A:B"), 2, False)

Raju A


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!

Hi, refer to my attachment,
How to make repeated value from database to fill in another column of the same row.


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 everyone,

I have long list of duplicate values with reference values next to it. I need to extract unique values such that the reference values get extracted too. Here is the e.g.
Coloumn A,,,,,,,,,,,,,,,,,,,,Coloumn B
NO8112 ........................$50

I am looking for the following result:

I was trying to use countif (A$2:A2,A2) but i find it limited in its ability to what I am trying to accomplish. It gives me unique values only from one list. However, I want to have unique values from list A with its corresponding values.

If anyone knows an easy formula to resolve my issue, kindly let me known. Thanks.


I am able to find the total no of filtered records using below macro.

Sub CountVisRows()

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range

MsgBox rng.Columns(1). _
SpecialCells(xlCellTypeVisible).Count - 1 _
& " of " & rng _
.Rows.Count - 1 & " Records"

End Sub

But I want to copy the cell by cell value and paste it in another sheet. How to read cell value from the filtered data? I don't want to copy the entire row or sheet.

Appreciate your help


Does anyone have any idea on how to determine the values under following
Given a list of numbers sorted by ascending order from top to bottom under
column A, I would like to determine the minimum difference on the range
within this list covering 50% of total numbers, and return the smallest
number within the range in cell B1 and the largest number within the range in
cell B2.
Does anyone have any suggestions?
Thank you in advance

1. A value on sheet 1 in Row "A" (Row A2:A156) needs to be matched to a "=" value on sheet 2 in Row "A" (Row A2:A6), and then Cells (J & x : AH & x) need to be copied from sheet 2 and pasted in sheet 1 row of the value matched Cells (J & x : AH & x)


How do i compare values from two sheet and copy and paste if values match


Can any body pls suggest me how to get filtered values from particular column on another sheet. Like what i want to do in attached worksheet is.. to get changing values for particular item on different dates to be listed on the other sheet.


i want to caunt unique values from a array (column c) but only where column F have the value "z569pon" and column G have the value "startet"

this formula count all uinque from the array =SUM(IF(FREQUENCY(B3:B2000;B3:B2000)>0;1))

and this =COUNTIFS(F3:F2000;"z569pon";G3:G2000;"startet") count all not the only the unique

is it possiblie to combine thise to formula?

a b c d e f g 1 total actual wood iron stone gold 2 WOOD 400 50 50 45 10 3 IRON 400 50 4 STONE 400 50 5 GOLD 400 50 Hi i'm having problems populating a table as above (the actual table is 20x bigger) at the moment the formula i'm using in e2 is =IF($C$2="",ROUNDDOWN(SUM(($B$2*5)/($B$2*7)*$B$2),0),ROUNDDOWN(SUM(($C$2*5)/($C$2*7)*$C$2),0))and the formula in f2 is =IF($C$2="",ROUNDDOWN(SUM(($B$2*5)/($B$2*25)*$B$2),0),ROUNDDOWN(SUM(($C$2*5)/($C$2*25)*$C$2),0)) as you can see i cant simply copy the formula across the columns as the ratio's are different for each cell so i created a master list,

wood 5
iron 7
stone 9
gold 13

so obviously the ratio between wood/wood is 1:1 and the ratio between wood/iron is 5:7 and the ratio between wood/gold is 5:13 etc but i don't know how to get the values from my list into the formula so that its just a simple ctrl-enter across the whole table rather than enter each one individually?????
any help greatly appreciated thanks in advance


i have a document which is added with leading Zero's in some of the

for eg., in the ZIPCODE column my value is 09281 but in the display i am
getting it

as 9281. But i want it with the original value. Can anyone tell me how to
get the

original values

Does anyone have any suggestions on how to determine the value from a list?


I would like to determine the second smallest value, but skip checking the
repeated one, such as in this case, I should select 25 and skip 22 because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance


i have to .xls files - and i need to get an number from another workbook.

My book nr 1 have this
A1: (Itemnumberr) B1...G1 (some text). H1 (item weight)

Booknr 2;
A1 (itemnumber)....B1....K1 (some text) L1 (item weight)

What funktion is my best solution to get the item weight from book 2 into
book 1


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