Question on macros

[Edwin Gasparraj]

I have the following table

          Aaron      Bart      Carson        Dan
Angela  01/01/99   02/03/99   04/23/99    03/05/99
Beth    10/24/99   08/21/99   01/17/99    04/30/99
Carol   05/06/99   11/17/99   08/06/99    12/09/99
Daphne  01/28/99   04/17/99   05/24/99    04/15/99

What I want to do is to create another table with
three columns.  The dates in the first one. The
corresponding girls name in the second and the
corresponding guys name in the third.

Is there a macro which I can write which would do this.
Please respond ASAP.  Thank you.

-Edwin.
 
 
 
 

[RUSS BARTOLI]

A challenging problem.

 

Here's my result:

 

	GAL	GUY
01-Jan-1999	Angela	Aaron	1
17-Jan-1999	Beth	Carson	2
28-Jan-1999	Daphne	Aaron	3
03-Feb-1999	Angela	Bart	4
05-Mar-1999	Angela	Dan	5
15-Apr-1999	Daphne	Dan	6
17-Apr-1999	Daphne	Bart	7
23-Apr-1999	Angela	Carson	8
30-Apr-1999	Beth	Dan	9
06-May-1999	Carol	Aaron	10
24-May-1999	Daphne	Carson	11
06-Aug-1999	Carol	Carson	12
21-Aug-1999	Beth	Bart	13
24-Oct-1999	Beth	Aaron	14
17-Nov-1999	Carol	Bart	15
09-Dec-1999	Carol	Dan	16

 

 

I placed your original table in A1 (blank cell).

 

The range name TBL is the date grid without headers.

 

I first filled numbers down the 4th column.

 

Column A formula:

 

@SMALL($TBL,D8)  in A8 copied down

 

Column B formula in B8 copied down:

 

@INDEX($A$2..$A$5,0,@MOD(@MATCH($A8,$TBL,0),4))

 

(A2..A5 are the gals names in your table)

 

Column C formula in C8 copied down:

 

@INDEX($B$1..$E$1,@QUOTIENT(@MATCH($A8,$TBL,0),4),0)

 

(B1..E1 are the guys names in your table).

 

phew!

Edwin Gasparraj wrote in message <3985E218.66D3183E@remove_this.sdrc.com>...

[Rata]

In Excel, you could do it this way.
Put your table in cells A1 to E5.
Put this formula in cell A7
=ROW()-6
Put this formula in cell B7
=OFFSET($B$2,(A7-1)/4,mod(A7-1,4))
Put this formula in cell C7
=OFFSET($A$2,(A7-1)/4,0)
Put this formula in cell D7
=OFFSET($B$1,0,MOD(A7-1,4))
Copy these 4 formulae (use Fill Down) down to row 22. This should give the
numbers 1 to 16 in column A, the dates in B, gals in C and guys in D.
If you have a larger table, just adjust the formulae where it has a "4".

HTH

Peter Hewett
"Edwin Gasparraj" <edwin.gasparraj@remove_this.sdrc.com> wrote in message
news:3985E218.66D3183E@remove_this.sdrc.com...

[Edwin Gasparraj]

Thanks Russ and Rata..
Even though Iwould have liked Russ's sorted list,
I had trouble getting the column C formula to work..
Rata's was simpler even though it did not sort the dates.
Thanks to both of you.

-Edwin.

[Marshall Price]

Edwin Gasparraj wrote:
: Even though Iwould have liked Russ's sorted list,

: I had trouble getting the column C formula to work..
: Rata's was simpler even though it did not sort the dates.

I'm not familiar with Excel, but couldn't you copy the dates to a
column, appending the original cell address to each, then sort, and then
use the cell addresses to generate the men's and women's columns?
The procedure could easily be made independent of the size of the array.

--
Marshall Price of Miami, Florida
d021...@dc.seflin.org