Multiple Matched Lines

[Mark Philley]

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).

 

[Max]

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.

 

[MP]

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]

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]

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

---

 

[MP]

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]

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]

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

---