Multiple Matched Lines
 

I need advice on retrieving all rows of data that match a specific criteria
or criteria range (ie date(s)) and have the data from these rows copied to a
preformatted worksheet and copied down as many times as the number of
selected rows. Vague enough? In my first sheet I have: Order #, Name, ID,
Date, etc.. My second sheet is a pre-formatted sheet with blanks for all
of the data contained in the first sheet. I want to enter a date or order #
range on the second sheet and have this pre-formatted sheet copied down the
sheet as many times as the no of matching rows I have. (similar to
mail-merging the matching rows to my excel form).

Multiple Matched Lines
 

One way ..

Source data assumed in sheet: X,
cols A to D, data from row2 down, viz:

Order# Name ID Date
1111 Nam1 ID1 Date1
1112 Nam2 ID2 Date2
1111 Nam3 ID3 Date3
1113 Nam4 ID4 Date4
1112 Nam5 ID5 Date5
etc

Then in another sheet: Y,
Labels placed in C1:E1 : Name, ID, Date

In A2 will be input the order# of interest, eg: 1112

Put in B2:
=IF($A$2="","",IF(X!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!B:B,SMALL($B:$ B,ROW(A1))))
Copy C2 to E2. Select B2:E2, copy down to cover the max expected extent of
data in X, eg to E100. Hide away col B. Format col E as dates. Cols C to E
will return all the lines for the order# in A2 from X, neatly bunched at the
top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mark Philley" wrote in message
...
I need advice on retrieving all rows of data that match a specific criteria
or criteria range (ie date(s)) and have the data from these rows copied to
a preformatted worksheet and copied down as many times as the number of
selected rows. Vague enough? In my first sheet I have: Order #, Name, ID,
Date, etc.. My second sheet is a pre-formatted sheet with blanks for all
of the data contained in the first sheet. I want to enter a date or order
# range on the second sheet and have this pre-formatted sheet copied down
the sheet as many times as the no of matching rows I have. (similar to
mail-merging the matching rows to my excel form).

Multiple Matched Lines
 

Thanks for the quick reply. I didn't explain enough, that my second sheet
(sheet Y) needs the data from Sheet X to be in the proper fields, that are
in multiple rows. Sheet Y is a Form of sorts, and I want to copy these rows
(as a whole form) as far down as I need to accomodate for all the rows
gathered in your formulas below.

"Max" wrote in message
...
One way ..

Source data assumed in sheet: X,
cols A to D, data from row2 down, viz:

Order# Name ID Date
1111 Nam1 ID1 Date1
1112 Nam2 ID2 Date2
1111 Nam3 ID3 Date3
1113 Nam4 ID4 Date4
1112 Nam5 ID5 Date5
etc

Then in another sheet: Y,
Labels placed in C1:E1 : Name, ID, Date

In A2 will be input the order# of interest, eg: 1112

Put in B2:
=IF($A$2="","",IF(X!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!B:B,SMALL($B:$ B,ROW(A1))))
Copy C2 to E2. Select B2:E2, copy down to cover the max expected extent of
data in X, eg to E100. Hide away col B. Format col E as dates. Cols C to E
will return all the lines for the order# in A2 from X, neatly bunched at
the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mark Philley" wrote in message
...
I need advice on retrieving all rows of data that match a specific
criteria or criteria range (ie date(s)) and have the data from these rows
copied to a preformatted worksheet and copied down as many times as the
number of selected rows. Vague enough? In my first sheet I have: Order
#, Name, ID, Date, etc.. My second sheet is a pre-formatted sheet with
blanks for all of the data contained in the first sheet. I want to enter
a date or order # range on the second sheet and have this pre-formatted
sheet copied down the sheet as many times as the no of matching rows I
have. (similar to mail-merging the matching rows to my excel form).

Multiple Matched Lines
 

I'm not sure what is still outstanding in sheet: Y.
Could you elaborate ?
Perhaps paste some sample data in plain text in reply here
to illustrate what you have / you are after
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MP" wrote in message
...
Thanks for the quick reply. I didn't explain enough, that my second sheet
(sheet Y) needs the data from Sheet X to be in the proper fields, that are
in multiple rows. Sheet Y is a Form of sorts, and I want to copy these
rows (as a whole form) as far down as I need to accomodate for all the
rows gathered in your formulas below.

Multiple Matched Lines
 

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!B:B,SMALL($B:$ B,ROW(A1))))
Copy C2 to E2...

To adapt which cols and/or the order of the cols in X as they should appear
in Y
just adjust the indexed col part, eg: INDEX(X!B:B, ...
within the expressions in C2:E2 to suit before copying down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Multiple Matched Lines
 

The information below is a layout similar to what I have in Sheet Y (these
lines cover 6 rows). I need to print this "form" as many times as the
number of matching rows I have in sheet X with the selected sheet X data in
the proper "blanks" of the form. I would like to enter the date range (or
maybe another criteria) and then have the data placed on the blank forms.
If I need to use what you have below to get my needed rows extracted, I
will. Then I need to replicate the form to allow for each row to be a new
form. If this calls for a macro, a suggestion of where to begin may be what
I need. Thanks again for your help.


Row 1 Date Selection:_______ to_______

Row 2 Form Title

Row 3 Order No:__________

Row 4 Order Date:_________

Row 5 Name:_____________

Row 6 ID:________________



"Max" wrote in message
...
I'm not sure what is still outstanding in sheet: Y.
Could you elaborate ?
Perhaps paste some sample data in plain text in reply here
to illustrate what you have / you are after
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MP" wrote in message
...
Thanks for the quick reply. I didn't explain enough, that my second
sheet (sheet Y) needs the data from Sheet X to be in the proper fields,
that are in multiple rows. Sheet Y is a Form of sorts, and I want to
copy these rows (as a whole form) as far down as I need to accomodate for
all the rows gathered in your formulas below.

Multiple Matched Lines
 

Here's a revised formulas play which delivers results very close to what
you're after ..

A sample construct is available at:
http://cjoint.com/?lvjda3x5nn
Extract MultiLines by Date range n Refashion in Ticket Format.xls

Source data assumed in sheet: X, cols A to D, data from row2 down, viz:

Order# Order Date Name ID
1111 19-Nov-06 Nam1 ID1
1112 19-Nov-06 Nam2 ID2
1113 20-Nov-06 Nam3 ID3
1114 20-Nov-06 Nam4 ID4
etc

In sheet: Y,

The Start date & End date will be input in B1:B2
(Enter the same date in B1:B2 if it's for a single date)

Labels placed in D1:I1 :

Date Selection:
FormTitle:
Order#:
Order Date:
Name:
ID:

In C2:
=IF(OR($B$1="",$B$2=""),"",IF(AND(X!B2=$B$1,X!B2< =$B$2),ROW(),""))
(Leave C1 blank)

In D2:
=IF(ROW(A1)COUNT($C:$C),"",TEXT($B$1,"dd-mmm-yy")&" to
"&TEXT($B$2,"dd-mmm-yy"))

In E2:
=IF(ROW(A1)COUNT($C:$C),"","FormTitle")

In F2:
=IF(ROW(A1)COUNT($C:$C),"",INDEX(X!A:A,SMALL($C:$ C,ROW(A1))))

In G2:
=IF(ROW(A1)COUNT($C:$C),"",TEXT(INDEX(X!B:B,SMALL ($C:$C,ROW(A1))),"dd-mmm-yy"))

In H2:
=IF(ROW(A1)COUNT($C:$C),"",INDEX(X!C:C,SMALL($C:$ C,ROW(A1))))
Copy H2 to I2

Select C2:I2, copy down to cover the max expected extent of data in X. Hide
away col C. Cols F to I will return all the lines for the date range
(inclusive) specified in B1:B2 from X, neatly bunched at the top. Cols D to
E are additional cols required for the re-fashioning into the desired end
results that we're going to do in cols K & L.

In K2:
=IF(L2="","",OFFSET($D$1,,MOD(ROW(A1)-1,6),))

In L2:
=OFFSET($D$2,INT((ROW(A1)-1)/6),MOD(ROW(A1)-1,6))

Select K2:L2, copy down to cover the max expected no of lines within any
date range specified. Eg if you expect a max of 10 lines returned in cols D
to I, copy down by: 10 lines x 6 rows per line = 60 rows thereabouts. Cols K
& L will return the desired end results. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MP" wrote in message
...
The information below is a layout similar to what I have in Sheet Y (these
lines cover 6 rows). I need to print this "form" as many times as the
number of matching rows I have in sheet X with the selected sheet X data
in the proper "blanks" of the form. I would like to enter the date range
(or maybe another criteria) and then have the data placed on the blank
forms. If I need to use what you have below to get my needed rows
extracted, I will. Then I need to replicate the form to allow for each
row to be a new form. If this calls for a macro, a suggestion of where to
begin may be what I need. Thanks again for your help.


Row 1 Date Selection:_______ to_______

Row 2 Form Title

Row 3 Order No:__________

Row 4 Order Date:_________

Row 5 Name:_____________

Row 6 ID:________________

Multiple Matched Lines
 

Here's an alternative, more enduring link to the sample construct:
http://www.savefile.com/files/280057
Extract MultiLines by Date range n Refashion in Ticket Format.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---