Locate Column that only has one value in excel

**FDibbins** · 10-11-2018, 02:09 AM

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**deephouse** · 10-11-2018, 01:31 PM

NickyC - Thats works but I want the opposite of it. I want to locate the column(s) that only has a single (distinct) value all across that column. That means that has 1 value Rob in all the cells in that column.

This formula is looking for all blank columns in the spreadsheet. I want the columns that has a single(Distinct) value in the spreadsheet.

Locate a column where the vale remains same in all cells and doesn't change. Hope this explanation helps.

Admin - I can't find the button for attaching a dummy file.

**NickyC** · 10-11-2018, 09:24 PM

ok try this:

Sub FindOnes()
Dim MyRg As Range, CCol As Range, CCell As Range, MyVal, ColC As String, IsDups As Boolean
Set MyRg = ActiveSheet.UsedRange
ColC = ""
For Each CCol In MyRg.Columns
    MyVal = ""
    IsDups = True
    For Each CCell In CCol.Cells
        If CCell.Row <> 1 And MyVal = "" And CCell <> "" Then MyVal = CCell
        If CCell.Row <> 1 And CCell <> MyVal And CCell <> "" Then
            IsDups = False
            GoTo nXtC
        End If
    Next CCell
    If IsDups = True And MyVal <> "" Then ColC = ColC & " " & CCol.Cells(1).Address(0, 0)
nXtC:
Next CCol
If ColC <> "" Then MsgBox "these columns have duplicate values:" & Replace(ColC, 1, "") Else MsgBox "no columns have duplicate values"
End Sub

**deephouse** · 10-12-2018, 07:21 PM

This works, thanks NickyC. You are awesome!!

Locate Column that only has one value in excel

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Locate Column that only has one value in excel

Re: Locate Column that only has one value in excel

Re: Locate Column that only has one value in excel

Re: Locate Column that only has one value in excel

Thread Information

Users Browsing this Thread

Similar Threads

Mimic Pivot - Locate Column Value (not unique)/Return Another Column Value (Summarized)

Locate all the instances of a list of values in one column in another column

[SOLVED] Locate first cell in column

macro to locate locate text string and copy/paste offset range

Locate Column through Excel ODBC

Locate max number from column A

About locate last cell in the last column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions