How can I do this ?

E

Eric_in_EVV

I have a spreadsheet that looks like this:

C D E F G
H I
row 29 18-Jan 19-Jan 20-Jan 21-Jan 22-Jan 23-Jan
24- Jan
row 30 DISC
row 31 ed
row 32 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan
31- Jan
row 33 DISC
row 34 ed

What I need to be able to do is search for each occurance of the "DISC" and
then use the date (the cell above the "DISC") on a different worksheet in the
workbook that contains summary information. I need to be able to report each
date that the "DISC" code occurs.

I hope this all makes sense !

Thanks in advance for any suggestions !
 
M

Max

Here's a formulas play which presumes that there will be only a single date
with "DISC" per week

Assuming your source data as posted is in Sheet1, with dates commencing in
C29:I29, followed by C32:I32, etc (spaced at intervals of 3 rows)

In another sheet,
Place in any start cell, say in B2, normal ENTER, format B2 as date to taste:
=INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-3,,),MATCH(TRUE,INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-2,,)="DISC",),0))
Copy B2 down as far as required. B2 returns the 1st week's date for the
DISC, B3 returns the 2nd week's date, and so on.

Perhaps better with an IF(ISNA(..) error trap to return neat looking blanks
for any week without a DISC, use this instead in B2, normal ENTER:
=IF(ISNA(MATCH(TRUE,INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-2,,)="DISC",),0)),"",INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-3,,),MATCH(TRUE,INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-2,,)="DISC",),0)))

Above helps? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
E

Eric_in_EVV

Thanks Max -

This is a good start for me. The one issue still to work out is to
accomodate for multiple occurances of "DISC" per week. I doubt it will
happen, but can not rule it out with 100% certainty, so I need to allow for
it in the calculations.

Any thoughts on that would be greatly appreciated !
 
M

Max

Maybe try transforming the source data into a regular data table (data in
columns), then we can simply use autofilter

In another sheet,
In B2:
=OFFSET(Sheet1!$C$29,INT((ROWS($1:1)-1)/7)*3,MOD(ROWS($1:1)-1,7))

In C2:
=OFFSET(Sheet1!$C$30,INT((ROWS($1:1)-1)/7)*3,MOD(ROWS($1:1)-1,7))
Copy B2:C2 down as far as required. Format col B as dates to taste. This
tranforms the source data in Sheet1 into 2 continuous columns of data: col B
= dates, col C = where the corresponding "DISC" text may appear. Now you
could apply autofilter on col C, filter for: DISC in C1, then just copy the
filtered rows in col B (these will be all the dates that you seek) to paste
special as values elsewhere.
 
D

Don Guillett

You coud use a FINDNEXT macro to do this. See the vba HELP index.
msgbox c.offset(-1)
 
E

Eric_in_EVV

Thanks Don - I'll check out FINDNEXT too !

Don Guillett said:
You coud use a FINDNEXT macro to do this. See the vba HELP index.
msgbox c.offset(-1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top