excel lookup question 2

[smiley0q0]

i have 2 columns, [DATE] and [ID] each date can have 1-15 ID's associated with it. i need to list the dates across, and the ID's underneath each date.

ie.
7/21/03 7/22/03 7/24/03 7/25/03
TL8945 TKSJ23 ZDL213 SKDJBN
JBNDK2 IIE397 UG9U93 45A43V
BKDBKA 35SSDH
AD34JN
RNKJ34


any ideas? do i need to use a loop to find each date, then then take the cell next to it? I've tried vlookup as well as Hlookup, and i've even tried the array formula thing
{=SUM((data!$C$2:$C$91=$C$6)*(data!$E$2:$E$91=$A8)*(data!$D$2:$D$91))}
column E, i did a count of the dates in column c, so i could just say pull the id where the date is equal to c6 and the number corresponding to where the formula is in the list for that date.
column C was my dates column and column D was my ID column
but that just keeps giving me a #VALUE! error.

any help would be greatly apreciated...

Thank you

Smiley

 

[GlennUK]

To do this using formulae would be very VERY complicated.

I recommend using a VBA macro.

See if this is any use to you ...

Option Base 1
Sub DoLists()
Dim DatesSoFar()
Dim NumDates As Integer
NumDates = 0
For Each c In Range("C2:C99&quot
' find out if the date has been seen before
ifound = 0
For isearch = 1 To NumDates
If c.Value = DatesSoFar(isearch) Then
ifound = isearch
Exit For
End If
Next

' test to see if this is a new date ( when ifound
' is zero, then the date is a new one )
If ifound = 0 Then
' add the date to the list of dates
NumDates = NumDates + 1
ReDim Preserve DatesSoFar(NumDates)
DatesSoFar(NumDates) = c.Value
Cells(1, 4 + NumDates) = c.Value ' do column heading
' now set ifound to be the same as end pos in array
ifound = NumDates
End If
' now put the corresponding ID into the appropriate column
' target columns are E, F, G etc etc
' find the first blank cell in the column ( i.e. after the last non-blank cell )
Cells(65000, 4 + ifound).End(xlUp).Offset(1, 0).Value = c.Offset(0, 1).Value
Next
End Sub


Regards, Glenn.

 

[Zathras]

Here is a variation of my VLOOKUPNEXT User-Defined function that might be able do the job for you.
[blue]

Function VLOOKUPNEXT(lookup_value, table_array As Range, _
           col_index_num As Integer, last_value)

[green]

' Extension to VLOOKUP function.  Allows for finding
' the "next" item that matches the lookup value.

[/color]

Dim nRow As Long
Dim bFound As Boolean
  VLOOKUPNEXT = ""
  With table_array
    For nRow = 1 To .Rows.Count
      If .Cells(nRow, 1).Value = lookup_value Then
        If bFound = True Then
          VLOOKUPNEXT = .Cells(nRow, col_index_num).Value
          Exit Function
        Else
          If .Cells(nRow, col_index_num).Text = last_value Then
            bFound = True
          End If
        End If
      End If
    Next nRow
  End With
End Function

[/color]
If your data are in columns A1:B13 (with column headings in row 1), you can set up the output area like this:
[blue]

D1: 7/21/03
E1: 7/22/03
F1: 7/24/03
G1: 7/25/03
D2: =VLOOKUP(D$1,$A$2:$B$13,2,0)
   copy from D2 to E2:G2
D3: =VLOOKUPNEXT(D$1,$A$2:$B$13,2,D2)
   copy from D3 to D3:G6

[/color]
Finally, you can copy and paste special values and then delete the original columns.

 

[smiley0q0]

GLENN!!!!

[red]YOU ARE A LIFESAVER!!! [/red]

[green]THAT CODE WORKS PERFECTLY.[/green][blue] EXACTLY WHAT I NEEDED.[/blue]
​

I have been working on getting that stupid thing to do what i needed it to since friday!!! was just about ready to trash it. Thank you very much.

very fast, neat and efficient.

thanks for the notes in the code too.

Smiley

 

[GlennUK]

My pleasure.

Glad you liked it. A little bit of code can be tons easier than massive formulae sometimes.


Cheers, Glenn.

 

[davidmunoz]

I would just create a pivot table that would catagorize each date with the associated ID accordingly. Then all you have to do is keep entering date and ID sequentially going down and update pivot.

Hope this Helps!