Automation Formula almost complete

[pboost1]

Hello everybody. I am trying to work on a formula that will automate
the printing of information I need. The information is on the homepage
of the worksheet (sheet1). The page that will be automated is on sheet
3 of the worksheet.

Column A has this formula:
=IF(ISERROR(SMALL(L7:L112,1)),"",SMALL(L7:L112,#)), where # is replaced
by the 1st, 2nd, 3rd .l.. place in the column.

Column B has this formula:
=IF(ISERROR(VLOOKUP(D7,homepage!$A$3:$E$111,5,FALSE)),"",VLOOKUP(D7,homepage!$A$3:$E$111,5,FALSE)),
where this formula pulls the info from the homepage based on the number
in column D.

The formula in column D is: =IF(A7=(""),"",LOOKUP(A7,L6:L25,J6:J25))

The formula in Column J is:
=IF(MONTH(homepage!D3)=MONTH(F1),homepage!A3," ")

The Final Formula I am using is in column L:
=IF(J7=(""),"",VLOOKUP(J7,homepage!1:65536,4,FALSE))

The problems I am getting is, sometimes the same information is pulled
for the same day three different times. (ie I have three bills to pay
on the same day). I would like the formula in column B to pull the
information based on the item number for the date that I need to do
what I need to do. Then when the next month comes, new information
needs to be pulled.


Any help will be greatly appreciated.

 

[Guest]

Here's one guess at what you're after and a non-array formulas approach to
get there ..
(Desired results are closely aligned to what you described in your post)

A sample construct is available at:
http://www.savefile.com/files/2243119
Auto-Extract Lines by date into a new sheet.xls

Source data assumed in sheet: homepage, from row3 down
with key col D = dates

In a new sheet,

Let's say the date of interest will be input in F1, eg: 9 May 2006
and results are to be placed in row7 down

Put in say, J7:
=IF(homepage!D3=$F$1,homepage!D3+ROW()/10^10,"")

Copy J7 down to say, J20?
to cover the max expected range of data in homepage
(Leave J1:J6 empty)

Then

Put in A7:
=IF(ISERROR(SMALL(J:J,ROW(A1))),"",
INDEX(homepage!D,MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4))

Put in B7:
=IF(ISERROR(SMALL(J:J,ROW(A1))),"",
INDEX(homepage!E:E,MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4))

Select A7:B7, copy down to B20

Put in D7:
=IF(ISERROR(SMALL(J:J,ROW(A1))),"",
INDEX(homepage!A:A,MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4))
Copy D7 down to D20

Formulas in A7, B7 and D7 are identical except for the indexed cols:
homepage!D, homepage!E:E and homepage!A:A

Cols A, B and D will auto-return only the required data from homepage's cols
D, E and A,
for which the dates in col D (in homepage) equals the date input in F1,
with all results neatly bunched at the top

If the date input in F1 is say: 9-May-2006,
then only the source lines (in homepage) with dates 9-May-2006 will be
extracted
Changing F1 to: 10-May-2006 will bring over only source lines with dates
10-May-2006
And so on.

---

 

[pboost1]

That helps a lot. I thank you. The only thing wrong now is, that ther
are other indexes for the same date, and it is not pulling all th
indexes needed.

for example, on May 13, 2006, I have index 4, 244, and 386. Th
formula that you provided is pulling index 4 on all three places. Ho
can I have index 4 be pulled only once

 

[Guest]

That helps a lot. I thank you. The only thing wrong now is, that there
are other indexes for the same date, and it is not pulling all the
indexes needed.

for example, on May 13, 2006, I have index 4, 244, and 386. The
formula that you provided is pulling index 4 on all three places. How
can I have index 4 be pulled only once?

I'm not sure why the above happened for you. The suggested set-up addresses
precisely the extract of multiple items having the same date in the source
sheet: homepage. This event was illustrated as well in the sample book (link
provided earlier), where there were 3 items: Text1, Text2, Text3 with the
same date: 9-May-2006 in homepage which were correctly pulled over in Sheet3
when the date (9-May-2006) was entered in F1. And if you enter 10-May-2006
into F1, you'd get 2 items: Text4 & Text5.

Maybe try checking the dates entered in hompage's col D
(ensure that these are all real dates)

Also, ensure that the cells above the starting cell in the criteria col
(i.e. cells above J7, viz. J1:J6 in Sheet3 in my sample) are left empty
in your adaptation

If you still can't get it to work, could you upload
and post a *link* to your sample book in response here ?

---

 

[pboost1]

Here is the sample page that I am working on. Also, the main
information that this will copied over to will have 705 rows of
information to sort through.

http://www.savefile.com/files.php?fid=8438349

Thanks again for the help.

 

[Guest]

Here's the implemented sample:
http://www.savefile.com/files/3196708
Automation_Extract Lines by Month.xls

In sheet: placing person,

All existing formulas were removed

Then ..

Placed in J7 (slightly revised formula):
=IF(MONTH(homepage!D3)=MONTH($F$1),homepage!D3+ROW()/10^10,"")
Copy J7 down as far as required to say J800?
to cover the expected extent of data in homepage

Then ..

Placed in A7:
=IF(ISERROR(SMALL(J:J,ROW(A1))),"",
INDEX(homepage!D,MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4))

Placed in B7:
=IF(ISERROR(SMALL(J:J,ROW(A1))),"",
INDEX(homepage!E:E,MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4))

Placed in D7:
=IF(ISERROR(SMALL(J:J,ROW(A1))),"",
INDEX(homepage!A:A,MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4))

Copy A7, B7 and D7 down the columns only as far as required
(copy down by the smallest possible extent
to extract all monthly items for any one particular month)

 

[Guest]

.. Would you be willing to explain the steps of this formula to me.

.. Also, I need to pull by month, and organize by the days of the month.

The formulas placed in sheet: placing person in the implemented sample (link
provided in earlier response) already yields the desired results.

Based on the month of the date which is input in F1 (in "placing person"),
only the lines in the source sheet: homepage which have dates in col D
corresponding to the month (in cell F1) are pulled over correctly, with all
lines neatly bunched at the top and sorted in chronologic (i.e. ascending)
order by the date of the month.

Here's some explanation to aid understanding ..

In sheet: placing person,
we placed in J7, and copied down:
=IF(MONTH(homepage!D3)=MONTH($F$1),homepage!D3+ROW()/10^10,"")

This col J is a helper criteria col with an arbitrary tie-breaker (i.e. the
part: +ROW()/10^10) which assigns different numbers drawing from the dates in
homepage's col D satisfying the basic criteria of:
MONTH(homepage!D3)=MONTH($F$1)
(dates are simply numbers in Excel)

The tie-breaker portion in the formula will ensure that different numbers
are assigned even though there may be multiple lines with the same dates in
homepage which satisfy the basic criteria.

With col J set-up, we then placed in A7, and copied down:
=IF(ISERROR(SMALL(J:J,ROW(A1))),"",
INDEX(homepage!D,MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4))

The formulas in A7, B7 and D7 are all similar
except for the indexed columns pointed at, viz. the part:

INDEX(homepage!D,
INDEX(homepage!E:E,
INDEX(homepage!A:A,

The indexed columns are the source sheet's (homepage's) cols
that we want to pull from

In the part: MATCH(SMALL(J:J,ROW(A1)),J:J,0)-4
MATCH(...) will return the row number which corresponds to the smallest
number within col J [ROW(A1) evaluates to 1]. The "-4" is just an arithmetic
adjustment to the MATCH(...) result to get the correct row number for the
indexed col

And as we copy down, it'll increment to: MATCH(SMALL(J:J,ROW(A2)),J:J,0)-4,
MATCH(...) now returns the row number corresponding to the 2nd smallest
number within col J [ROW(A2) evaluates to 2], and so on ..

The front error part: IF(ISERROR(SMALL(J:J,ROW(A1))),"",
simply ensures a clean display ("blanks"), instead of ugly #NUM! errors,
once all the source lines which satisfy the criteria are exhausted

P/s: Btw, you really should not edit your posts in excelforum (albeit this
seems to be permitted by excelforum), as your "edits" will not show up in the
interfaced excel newsgroups with its much wider readership / group of
responders. Your edits will only be visible to excelforum readers (or those
who happen by excelforum <g>). If you need to further clarify what you
posted, just do it as a normal reply to your own post. Then your
clarifications will be visible to all readers.

---

 

[pboost1]

Hello Max,

I thank you for all the help you have given me. I am adapting the
formula that you have provided to what I need to use it with and the
results are working the way the sample was. But now I have another
problem.

On the worksheet that I am adapting this to (the main sheet at work) it
works, but when I get the date formula down to look at row 434, the
response I am receiving is 1/0/1900. and this is only for January.
But since I am receiving this date, the other formulas then pull the
information based on the date. Did I do something wrong? I did copy
it the way you provided except with the name of the sheets changed to
fit into the excel book at work.

I am sorry for all the hassle I have given you. Thanks for all that
you have provided. If I can get your e-mail, it would be greatly
appreciated.

 

[Guest]

In sheet: placing person,

Place this slightly revised formula in J7:
=IF(OR($F$1="",homepage!D3=""),"",IF(MONTH(homepage!D3)=MONTH($F$1),homepage!D3+ROW()/10^10,""))
Copy down to J800

(Rest of the formulas unchanged)

Above will improves robustness via adding a front error trap:
=IF(OR($F$1="",homepage!D3=""),"", ...
to return "blanks" ie:"" for any empty cells in homepage's col D,
and/or should the input cell F1 be cleared.

My email: (e-mail address removed)
(but kindly post questions to the excel newsgroups, not via private email to
me)

 

[pboost1]

Ok, I will try that as soon as I can. Thanks again

 

[Guest]

Ok, I will try that as soon as I can. Thanks again.

You're welcome !
Let us know how it went for you ..
(so that the word: almost
can be "removed' from your subject line <g>)

---

 

[pboost1]

Dear Max,

I was able to adapt the formula you have had the patience to help me
with. Now with the latest formula you have given, no dates are showing
at all. Before the adding of the latest information, the formula worked
fine except as I said, down to when there was no dates on homepage
column 433. the "placing person" sheet would show the date as
1/0/1900. That would only happen when January was pulled.

Now the formula you have helped me with is hiding all the dates. Any
suggestions?

 

[Guest]

Now the formula you have helped me with is hiding all the dates.

Not sure why the above is happening ..

In sheet: placing person,
Pl ensure you have a valid date entered in F1

Then try this revised formula in J7
=IF(OR($F$1="",homepage!D3="",homepage!D3=0),"",IF(MONTH(homepage!D3)=MONTH($F$1),homepage!D3+ROW()/10^10,""))
Copy J7 down to J800

(Added another error trap for homepage's col D = 0)

"1/0/1900" is simply a zero in a cell formatted as date
(the zero either entered into the cell or returned by a formula)

Let me know how it worked out ..

 

[pboost1]

Dear Max,

Thank you for everything. THe formulas now work the way They are
suspose to. I appreciate it greatly. Again thank you for all the help
you have provided.

 

[Guest]

You're welcome !
Glad it worked out fine for you
Thanks for the feedback ..