Add value in cell next to a duplicate

[pook_666]

Hi team,

Hopefully a quick one for you geniuses.

I have a column of duplicate and non-duplicate values. I want to add a comment in the cell next to each if there is a duplicate or not.

For an easy example, see below where I have the below numbers in column A and I want to put in column B next to each number either "Yes" if there is a duplicate or "No" if there isn't.

Either a formula inserted directly into column B cells or VBA would be fine if anyone can help!

Thanks!

 

[sandy666]

maybe

Value	YN
1	No
2	Yes
3	No
2	Yes
5	No
6	No
7	Yes
7	Yes
7	Yes
10	No
11	No
12	Yes
12	Yes
14	No

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Group = Table.Group(Index, {"Value"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _, type table}}),
    YN = Table.AddColumn(Group, "YN", each if [Count] = 1 then "No" else "Yes"),
    Expand = Table.ExpandTableColumn(YN, "All", {"Index"}, {"Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    TSC = Table.SelectColumns(Sort,{"YN"})
in
    TSC

 

[Sanjeev1976]

Formula in cell B1
=IF(COUNTIF(A:A,A1)>1,"Yes","No")
and then copied down

 

[pook_666]

@sandy666 - sorry, I'm not entirely sure what you've done? Or where?

@Sanjeev1976 - I am getting everything as "Yes" when I use this formula?

 

[sandy666]

@sandy666 - sorry, I'm not entirely sure what you've done? Or where?

1. update your profile about Excel version and OS, don't forget to scroll down and hit SAVE
2. this is Power Query and it depends on what Excel version you have

more info

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[My Aswer Is This]

@sandy666 - sorry, I'm not entirely sure what you've done? Or where?

@Sanjeev1976 - I am getting everything as "Yes" when I use this formula?

The formula worked for me. Did you put the formula into B1
And then drag down into the cells below B1?

 

[pook_666]

@Sanjeev1976 & @My Aswer Is This - it's the spreadsheet as I am running an SQL script and just needing to do an F9 refresh and that solves it - thank you!

 

[My Aswer Is This]

The formula worked for me.

@Sanjeev1976 & @My Aswer Is This - it's the spreadsheet as I am running an SQL script and just needing to do an F9 refresh and that solves it - thank you!

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[mohadin]

Hi
What about

Sub test()
    Dim a As Variant
    Dim i As Double
    a = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), "No"
                Else
                    .Item(a(i, 1)) = "YES"
                End If
            End If
        Next
        For i = 1 To UBound(a)
            If .exists(a(i, 1)) Then
                a(i, 2) = .Item(a(i, 1))
            End If
        Next
        Cells(1, 1).Resize(UBound(a), 2) = a
    End With
End Sub

 

[Sanjeev1976]

@Sanjeev1976 & @My Aswer Is This - it's the spreadsheet as I am running an SQL script and just needing to do an F9 refresh and that solves it - thank you!

Glad we were able to help you with your query