Unique Values from 2 Columns of a table to an array

[nikhilsharma]

Hi All,

One of my staff is responsible for maintaining calendars for doctors in excel.
The data is in a table with Doctor Name, Date, Site, Start Time, End time.



Sample spreadsheet attached. Please note its not a range but table.

I need help with achieving 2 things:

1. Pick up unique values from Doctor and Site Column into a 2 dimensional array.
From the attached example, the array should have the following values:

Array Column 1	Array Column 2
Doc 1	Site 1
Doc 1	Site 2
Doc 2	Site 1
Doc 2	Site 2
Doc 2	Site 3
Doc 2	Site 4
Doc 3	Site 1
Doc 3	Site 2
Doc 3	Site 4
Doc 4	Site 5

2. If there’s an overlap for a doctor’s time at any other site (same doctor, same date, overlapping time of any sorts), the conflicting rows should get color coded to show a conflict.
Please note: Start and end times excluded from this criteria. Example: one site ends at 11 AM and another starts at 11 AM, this is not a conflict and should not highlight the rows.

From the attached example, Row 6 and 7 should get highlighted for conflict.

Please help!
Thanks in advance!

 

[Marc L]

Hi,​

​

no need a code just using conditional formatting …​

​

For further help, as it has been brought to our attention that the same query has been posted on one or more other forums​

and you have not provided the required cross-post link(s) here.​

​

Read this to understand why we (and other sites like us) consider this to be important.​

Please see this forum rules below about cross-posting and adjust accordingly :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Nebu]

Hi:

Since I had worked on a similar request here is VBA code for this.

Sub blah()

Dim tbl As ListObject
    
    Set tbl = Me.ListObjects("Table1")
    i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

    With tbl.Sort
        .SortFields.Clear
        .Header = xlYes
        .SortFields.Add Key:=tbl.ListColumns("Doctor").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=tbl.ListColumns("Date").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
        .Apply
    End With
    
Dim dic As Object
Dim v As Variant
Dim Rng As Range
Dim j&
Dim sKey$
Dim tv As Date, tv1 As Date

    Set Rng = tbl.DataBodyRange
    v = Rng
    Set dic = CreateObject("Scripting.Dictionary")
    
    For j = 1 To UBound(v, 1)
        sKey = v(j, 1) & "|" & v(j, 2)
        tv = TimeValue(Format(v(j, 5), "hh:mm"))
        On Error Resume Next
        tv1 = TimeValue(Format(v(j + 1, 4), "hh:mm"))
            
            If dic.Exists(sKey) And (tv > tv1) Then
             dic.Add Key:=sKey, Item:=1
            Else
                dic(sKey) = dic(sKey) + 1
            End If
    Next j
    
   For j = 1 To UBound(v, 1)
        sKey = v(j, 1) & "|" & v(j, 2)
        Me.Range("F" & j + 1) = dic(sKey)
   Next j
 
 Set Rng = Me.Range("F2:F" & i)
 
 For Each Rng In Rng.Rows
    If Rng.Value > 1 Then
        tbl.DataBodyRange.Rows(Rng.Row - 1).Interior.ColorIndex = 6
    End If
Next

Me.Range("F2:F" & i).Clear

End Sub

 

[nikhilsharma]

Hi Marc,

Sorry, my apologies, I overlooked the cross posting part.
I am unable to find an edit option to edit this post to mention the cross post in there. Could you please help?

Hi,​

​

no need a code just using conditional formatting …​

​

For further help, as it has been brought to our attention that the same query has been posted on one or more other forums​

and you have not provided the required cross-post link(s) here.​

​

Read this to understand why we (and other sites like us) consider this to be important.​

Please see this forum rules below about cross-posting and adjust accordingly :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[nikhilsharma]

Hi:

Since I had worked on a similar request here is VBA code for this.

Sub blah()

Dim tbl As ListObject
   
    Set tbl = Me.ListObjects("Table1")
    i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

    With tbl.Sort
        .SortFields.Clear
        .Header = xlYes
        .SortFields.Add Key:=tbl.ListColumns("Doctor").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=tbl.ListColumns("Date").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
        .Apply
    End With
   
Dim dic As Object
Dim v As Variant
Dim Rng As Range
Dim j&
Dim sKey$
Dim tv As Date, tv1 As Date

    Set Rng = tbl.DataBodyRange
    v = Rng
    Set dic = CreateObject("Scripting.Dictionary")
   
    For j = 1 To UBound(v, 1)
        sKey = v(j, 1) & "|" & v(j, 2)
        tv = TimeValue(Format(v(j, 5), "hh:mm"))
        On Error Resume Next
        tv1 = TimeValue(Format(v(j + 1, 4), "hh:mm"))
           
            If dic.Exists(sKey) And (tv > tv1) Then
             dic.Add Key:=sKey, Item:=1
            Else
                dic(sKey) = dic(sKey) + 1
            End If
    Next j
   
   For j = 1 To UBound(v, 1)
        sKey = v(j, 1) & "|" & v(j, 2)
        Me.Range("F" & j + 1) = dic(sKey)
   Next j

Set Rng = Me.Range("F2:F" & i)

For Each Rng In Rng.Rows
    If Rng.Value > 1 Then
        tbl.DataBodyRange.Rows(Rng.Row - 1).Interior.ColorIndex = 6
    End If
Next

Me.Range("F2:F" & i).Clear

End Sub

Thanks Nebu. Appreciate the quick response. Could you please help with the unique value solution as well?

 

[nikhilsharma]

Hi,​

​

no need a code just using conditional formatting …​

​

For further help, as it has been brought to our attention that the same query has been posted on one or more other forums​

and you have not provided the required cross-post link(s) here.​

​

Read this to understand why we (and other sites like us) consider this to be important.​

Please see this forum rules below about cross-posting and adjust accordingly :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

I'd love to try the conditional formatting as well - could you please share the formula for it?

 

[p45cal]

Sorry, my apologies, I overlooked the cross posting part.
I am unable to find an edit option to edit this post to mention the cross post in there. Could you please help?

I think probably including the links in a message would do.
This is what I've come across:

Unique Values from 2 Columns of a table to an array

Cross Posted on Chandoo Forums as well Hi All, One of my staff is responsible for maintaining calendars for doctors in excel. The data is in a table with Doctor Name, Date, Site, Start Time, End time. Sample spreadsheet attached. Please note its not a range but table.

www.excelforum.com

Unique Values from 2 Columns of a table to an array

Hi All, One of my staff is responsible for maintaining calendars for doctors in excel. The data is in a table with Doctor Name, Date, Site, Start Time, End time. Please note its not a range but table. I need help with achieving 2 things: 1. Pick up unique values from Doctor and Site Column...

www.mrexcel.com

any more?

 

[nikhilsharma]

I think probably including the links in a message would do.
This is what I've come across:

Unique Values from 2 Columns of a table to an array

Cross Posted on Chandoo Forums as well Hi All, One of my staff is responsible for maintaining calendars for doctors in excel. The data is in a table with Doctor Name, Date, Site, Start Time, End time. Sample spreadsheet attached. Please note its not a range but table.

www.excelforum.com

Unique Values from 2 Columns of a table to an array

Hi All, One of my staff is responsible for maintaining calendars for doctors in excel. The data is in a table with Doctor Name, Date, Site, Start Time, End time. Please note its not a range but table. I need help with achieving 2 things: 1. Pick up unique values from Doctor and Site Column...

www.mrexcel.com

any more?

No. These are it. Thanks p45cal

 

[Nebu]

Hi:

Codes added for unique records.

Thanks

 

[nikhilsharma]

Hi:

Codes added for unique records.

Thanks

Awesome! Thanks a ton!!