Hi Guys!

How about this:


For example, I have the following data:
DATE---MENU---TIMEAVAILABLE
8/1-----Coke----5AM-2PM
8/1-----Spag----6AM-5PM
8/2-----Coke----8PM-9PM
8/2-----Spag----10AM-6PM

How can I print a report that looks like this:

MENU-------8/1-------------8/2
Coke--------5AM-2PM------8PM-9PM
SpaG--------6AM-5PM------10AM-6PM

Thanks a lot again for your help

--
Message posted from http://www.ExcelForum.com

Essentially you need to base a lookup on 2 columns, so the
normal lookup function (VLOOKUP) can't handle this. You
could use database functions (DGET) but I find them
cumbersome due to the need to set up multiple criteria
ranges.

Here's how I handle this type of problem:

1) On your data sheet with the original data, create a
calculated column that combines the criteria you need to
look up (in your case, date and menu item). In your case
you are using a date value and a text value, but you could
make it a text field by something like this (in cell D2
and copied down the list, assuming your list is in columns
A-C):

=TEXT(A2,"m/d")&":"&B2

(this would give, for example, "8/1:Coke" in
D2, "8/1:Spag" in D3, etc...

2) Now, to create your result table, you can find the
entries in the calculated column D from above that match
your criteria (date matches column header, item matches
what is given in column A). It can be done various ways,
but here is one way of writing a formula to do the lookup:
Assuming your table, as shown, has column headers in row 1
and the "MENU" column is A, in B2 put the formula:
=OFFSET(DataSheet!$C$1,MATCH(TEXT(B$1,"m/d")
&":"&$A2,DataSheet!$D:$D)-1,0)

(Note: I have referred to the sheet containing your list
as 'DataSheet'). The use of absolute vs relative
references is critical here, so if you use this be careful
to copy it properly! Because in this form you can copy
and paste this formula throughout the table and it should
work.

Just one approach, but I find it handy and hope it helps
with what you need to do...

K Dales



-----Original Message-----
Hi Guys!

How about this:


For example, I have the following data:
DATE---MENU---TIMEAVAILABLE
8/1-----Coke----5AM-2PM
8/1-----Spag----6AM-5PM
8/2-----Coke----8PM-9PM
8/2-----Spag----10AM-6PM

How can I print a report that looks like this:

MENU-------8/1-------------8/2
Coke--------5AM-2PM------8PM-9PM
SpaG--------6AM-5PM------10AM-6PM

Thanks a lot again for your help!


---
Message posted from http://www.ExcelForum.com/

.

arjcvg,
This macro will do it.
provided that there is no repeated Menu item for single date.
Assumed that Date column has real dates (excel dates)
Data is in "Sheet1" starting from cell A1 with headers in row 1.
there is an empty sheet "Sheet2" in the workbook.
The data can be sorted.

Run the macro from "Sheet1"

Cecil

Sub Report()
Dim i As Long
Dim j As Long
Dim k As Long
Dim LR As Long
Dim LRUM As Long
Dim l As String
Dim Rng As String
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:F" & LR).Sort Key1:=Range("A2"), _
Order1:=xlAscending, Key2:=Range("B2"), _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Sheet2").Select
Sheets("Sheet1").Range("A1:A" & LR).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A2"), Unique:=True
LRUM = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & LRUM).Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Sheet1").Range("B1:B101").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True
LRUM = Range("A" & Rows.Count).End(xlUp).Row
Rng = Range("A1:A" & LRUM).Address
j = 2
For i = 2 To LR
If Sheets("Sheet1").Range("A" & i).Value < Cells(1, j).Value _
Then j = j + 1
l = Sheets("Sheet1").Range("B" & i).Value
k = Evaluate("Match(" & Chr(34) & l & Chr(34) & "," & _
Rng & ",0)")
Cells(k, j).Value = Sheets("Sheet1").Range("C" & i).Value
Next i
End Sub


"arjcvg " wrote in message
...
Hi Guys!

How about this:


For example, I have the following data:
DATE---MENU---TIMEAVAILABLE
8/1-----Coke----5AM-2PM
8/1-----Spag----6AM-5PM
8/2-----Coke----8PM-9PM
8/2-----Spag----10AM-6PM

How can I print a report that looks like this:

MENU-------8/1-------------8/2
Coke--------5AM-2PM------8PM-9PM
SpaG--------6AM-5PM------10AM-6PM

Thanks a lot again for your help!


---
Message posted from http://www.ExcelForum.com/

Guys you're great! Thanks for all the help

--
Message posted from http://www.ExcelForum.com