Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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:________________ |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
putting multiple lines of text in one cell | Excel Discussion (Misc queries) | |||
multiple lines in a cell | Excel Worksheet Functions | |||
Deleting unused lines between used lines? | Setting up and Configuration of Excel | |||
multiple lines | Excel Discussion (Misc queries) | |||
how do I enter multiple lines in a cell in Excel | Excel Discussion (Misc queries) |