• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique Values from 2 Columns of a table to an array

nikhilsharma

New Member
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.

64821

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 1Array Column 2
Doc 1Site 1
Doc 1Site 2
Doc 2Site 1
Doc 2Site 2
Doc 2Site 3
Doc 2Site 4
Doc 3Site 1
Doc 3Site 2
Doc 3Site 4
Doc 4Site 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!
 

Attachments

Marc L

Excel Ninja
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 :​
 

Nebu

Excel Ninja
Hi:

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

Code:
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
 

Attachments

nikhilsharma

New Member
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 :​
 

nikhilsharma

New Member
Hi:

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

Code:
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

New Member
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 :​
I'd love to try the conditional formatting as well - could you please share the formula for it?
 

p45cal

Well-Known Member
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:

any more?
 

nikhilsharma

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

any more?
No. These are it. Thanks p45cal
 
Top