Tables

  • Thread starter Thread starter freeriderxlt
  • Start date Start date
F

freeriderxlt

I would like to create a table that could be prepared from a data set.
An example of what I am trying to do is as follows:

Have the following:

Date Room Event
1/2/2008 100 ABC Corp
1/2/2008 101 ABC Corp
1/2/2008 103 ABC Corp
1/3/2008 100 ABC Corp
1/3/2008 101 ABC Corp
1/4/2008 103 XYZ Corp
1/4/2008 104 XYZ Corp

Would like to have prepared from the above data set

100 101
103 104
1/2/2008 ABC Corp ABC Corp ABC Corp
1/3/2008 ABC Corp ABC Corp
1/4/2008 XYZ Corp
1/4/2008
XYZ Corp

When I use a pivot table, it only lets me identify a calculated field
for the data and I would like it to simply show the text instead.
 
One way is to complement the pivot's easy "cross-tab" output with a
dual-criteria index/match in an adjoining area below to populate it as
desired with the text under "Event"

Let's take this pivot output from your sample data*,
assumed in A4:E7 in the pivot sheet
*assumed in Sheet1's cols A to C

Date 100 101 103 104
1/2/08 1 1 1
1/3/08 1 1
1/4/08 1 1

In an area further down on the same sheet,
Place this in say, B15:
=IF(ISNA(MATCH(1,INDEX((Sheet1!$A$2:$A$10=$A5)*(Sheet1!$B$2:$B$10=B$4),),0)),"",INDEX(Sheet1!$C$2:$C$10,MATCH(1,INDEX((Sheet1!$A$2:$A$10=$A5)*(Sheet1!$B$2:$B$10=B$4),),0)))
Copy across/fill down to E17 to populate. Adapt the ranges to suit. Then
ring the desired output with row/col headers in A15:A17, B14:E14 using
simple link formulas, viz. in A15, copied down: =A5, in B14, copied across:
=B4.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
 
One way is to complement the pivot's easy "cross-tab" output with a
dual-criteria index/match in an adjoining area below to populate it as
desired with the text under "Event"

Let's take this pivot output from your sample data*,
assumed in A4:E7 in the pivot sheet
*assumed in Sheet1's cols A to C

Date 100 101 103 104
1/2/08 1 1 1
1/3/08 1 1
1/4/08   1 1

In an area further down on the same sheet,
Place this in say, B15:
=IF(ISNA(MATCH(1,INDEX((Sheet1!$A$2:$A$10=$A5)*(Sheet1!$B$2:$B$10=B$4),),0)­),"",INDEX(Sheet1!$C$2:$C$10,MATCH(1,INDEX((Sheet1!$A$2:$A$10=$A5)*(Sheet1!­$B$2:$B$10=B$4),),0)))
Copy across/fill down to E17 to populate. Adapt the ranges to suit. Then
ring the desired output with row/col headers in A15:A17, B14:E14 using
simple link formulas, viz. in A15, copied down: =A5, in B14, copied across:
=B4.
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik










- Show quoted text -
Click to expand...

I am not understanding the instructions very well. You mentioned to
"adapt the ranges" I am not understanding with the ranges are
referring to so it don't know how to adapt them. From your example you
defined the table in Sheet1 columns A to C. are you referring to this
table? if true is the range A1:C8? I also don't understand this
section "Then
ring the desired output with row/col headers in A15:A17, B14:E14
using
simple link formulas, viz. in A15, copied down: =A5, in B14, copied
across:
=B4.
I understand simple link formulas but I don't follow the cell
references you are using as it relates to this example.

I appreciate the help, I am just not following completely.
 
Back
Top