Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Collecting data into a cell! (Excel2000>)

    Hi all,

    I'm busy with a project that is straining my brains. Please could someone help me with another formula. Is it possible to collect data into a cell based on date? See the attached file. There are 3 courses without a trainer allocation. All 3 courses are on the same day. How can I get all 3 courses displayed in the table in the appropriate date?
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collecting data into a cell! (Excel2000>)

    It looks like you're trying to create a database in Excel. Access might be a better tool.

    If you want to do it in Excel: do you want to list courses where the date in column F is the start date, or where the date in column F is between the start and end date?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collecting data into a cell! (Excel2000>)

    Yes, where the date in the F column is the start date!

    This is part of that project where you helped me with the course on the intersection of the name and date yesterday. The problem with Not Yet Assigned, is that there could be 2 or 3 courses linked to this "name" and theirfore it only displays the 1st one, based on the function you set up yesterday. So what I am trying to do now is to set up a seperate area next to the cross-reference that will list all the not yet allocated courses next to the date applicable!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collecting data into a cell! (Excel2000>)

    I notice that there are duplicates in the list in columns A:B, for example Microsoft Outlook 2000 Level 1 occurs 3 times on March 1. Will that be the case in the real data too? If so, would you like unique items in column G, or will you allow duplicates in one cell?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collecting data into a cell! (Excel2000>)

    If I understand what you are after, you might check out the functions in <post#=395235>post 395235</post#> to lookup all the "matches" within the range.

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collecting data into a cell! (Excel2000>)

    Hi Hans and Steve.

    The VLookupAll function is doing the job well. (Thanx Steve). However Hans raised the question if I need ALL the values returned or a UNIQUE value returned. The VLookupAll function is returning them All. Is there a way to make it unique.

    See the latest attachment!

    Many Thanx
    Regards,
    Rudi

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collecting data into a cell! (Excel2000>)

    You can use the VLIndex function in the thread to get an individual item. Using 1 as the index will act like the normal vlookup, using 2 will get the 2nd occurence, 3 the third, etc

    You can then extract each one separately into different columns in the same row (if this is what you are after).

    If that is not what you are after, could you elaborate?

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collecting data into a cell! (Excel2000>)

    Without rewriting the functions, you could use Data | Filter | Advanced Filter to copy unique records to another range, and use that as lookup range.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collecting data into a cell! (Excel2000>)

    That is a great suggestion Hans, but I cannot use it. The data I'm compiling is from a live SQL Database that is imported into excel and refreshed regularly. It will complicate the matter to have to refilter for unique records each time before the refresh. I'm not too sure if you may have any other ideas of getting a unique entry of each course into the cell. (I'm already very happy with the progress we've made. This will just be a bonus cherry on the cake if the values can be unique!!!!)

    Steve,
    If you have any ideas too. I think the idea is clear for you based on your previous query! VLIndex is not what I'm needing!

    THANX A STACK GUYS - YOUV'E BEEN A HUGE HELP SOFAR!!!!
    Regards,
    Rudi

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collecting data into a cell! (Excel2000>)

    Ah, I see what you are after now, and the problem (I am a little slow sometimes...)
    I created a new function for you:

    <pre>Function VLookupUniqueAll(vValue, rngAll As Range, iCol As Integer, _
    Optional sSep As String = ", ")
    Dim colUnique As New Collection
    Dim i As Integer
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo errhandler

    Set rng = Intersect(rngAll, rngAll.Columns(1))
    On Error Resume Next
    For Each rCell In rng
    If rCell.Value = vValue Then _
    colUnique.Add _
    rCell.Offset(0, iCol).Value, _
    CStr(rCell.Offset(0, iCol).Value)
    Next rCell
    On Error GoTo errhandler

    For i = 1 To colUnique.Count
    VLookupUniqueAll = VLookupUniqueAll & sSep & _
    colUnique(i)
    Next i

    If VLookupUniqueAll = "" Then
    VLookupUniqueAll = CVErr(xlErrNA)
    Else
    VLookupUniqueAll = Right(VLookupUniqueAll, _
    Len(VLookupUniqueAll) - Len(sSep))
    End If
    errhandler:
    If Err.Number <> 0 Then VLookupUniqueAll = CVErr(xlErrValue)
    End Function</pre>


    Change your G2 formula to use the new name:
    <pre>=VLookupUniqueAll(F2,$B$2:$B$11,-1)</pre>


    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collecting data into a cell! (Excel2000>)

    Could you change the query used to get data from SQL Server to return unique values only? (SELECT DISTINCT instead of SELECT)?

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collecting data into a cell! (Excel2000>)

    Thanx Steve. It works well. Only one value of each course is returned. Brilliant.
    Hans, that is also a good suggestion that never occured to me. However, as Steve has provided the function, that is not necessary anymore. Thanx.

    Now for the LAST question.
    See the latest attach. Is it possible to have the lookup supply the values ONLY if it is Not Yet Allocated. The course that Tim and John train is also in the column. I suspect it should be in some IF statement, but arrays may come into play here????
    Regards,
    Rudi

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collecting data into a cell! (Excel2000>)

    This is not "generic", change the line

    <pre> If rCell.Value = vValue Then _
    colUnique.Add _
    rCell.Offset(0, iCol).Value, _
    CStr(rCell.Offset(0, iCol).Value)</pre>

    to:
    <pre> If rCell.Value = vValue <font color=red>And _
    UCase(rCell.Offset(0, 2).Value) _
    = "NOT YET ALLOCATED"</font color=red> Then _
    colUnique.Add _
    rCell.Offset(0, iCol).Value, _
    CStr(rCell.Offset(0, iCol).Value)</pre>


    will handle it specifically. You could add optional paratmeters to compare the "offset column" (2 in this case) to an optional value ("NOT YET ALLOCATED", in this case) or even a cell reference ($G$1) or whatever...

    Steve

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collecting data into a cell! (Excel2000>)

    Beautiful

    Steve your'e a magician. Thank you!
    Regards,
    Rudi

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collecting data into a cell! (Excel2000>)

    No, I am a Chemist (though sometimes it only seems magical...). Remember: Chemists have Solutions!

    Steve

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •