If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
match help
Following is a sheet where I enter data:
1 2 3 4 5 6 7 Date Acct # Scheduled Actual 101 111 325 11/4/2008 6744-08 11/4/2008 11/4/2008 11/5/2008 6744-09 11/4/2008 11/4/2008 1 2 2 11/5/2008 6744-10 11/4/2008 11/4/2008 1 2 1 11/6/2008 6744-11 11/4/2008 11/4/2008 2 2 1 11/7/2008 6744-12 11/4/2008 11/4/2008 1 8 1 I will have a second sheet where I will change the date and would like to match and pull over data based on the date: For example if I put in 11/5/2008 I would like to match the entries. but the bigger question is what formula is needed to read from row to row? Date 11/5/2008 Account number 101 325 30001 Thanks for the help |
#2
|
|||
|
|||
match help
Hi Jim. Well this one of those where you have a good number of options. Good
for you. Depending on the goal you are persuing, I would probably change the option. So here are some suggestions. You can use: 1- Vlookup 2- Hlookup Although you can build some more complex functions to get the same result, these two are the easiest ones. Also, again depending on the purpose, you can use Pivot Tables. Best of luck. "Jim" wrote: Following is a sheet where I enter data: 1 2 3 4 5 6 7 Date Acct # Scheduled Actual 101 111 325 11/4/2008 6744-08 11/4/2008 11/4/2008 11/5/2008 6744-09 11/4/2008 11/4/2008 1 2 2 11/5/2008 6744-10 11/4/2008 11/4/2008 1 2 1 11/6/2008 6744-11 11/4/2008 11/4/2008 2 2 1 11/7/2008 6744-12 11/4/2008 11/4/2008 1 8 1 I will have a second sheet where I will change the date and would like to match and pull over data based on the date: For example if I put in 11/5/2008 I would like to match the entries. but the bigger question is what formula is needed to read from row to row? Date 11/5/2008 Account number 101 325 30001 Thanks for the help |
#3
|
|||
|
|||
match help
One way to set it up ...
Illustrated in this sample: http://freefilehosting.net/download/41icl Extract Multiple Results by Date.xls The Construct: Source data as posted assumed in cols A to G in Sheet1, data from row2 down, with the key col = col A (Date) In another sheet, Assume input for the date of interest will be made in B2, eg: 11-05-2008 In A4: =IF($B$2="","",IF(Sheet1!A2=$B$2,Sheet1!A2+ROW()/10^10,"")) Leave A1:A3 blank In B4: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!B:B,MA TCH(SMALL($A:$A,ROWS($1:1)),$A:$A,0)-2)) In C4: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!E:E,MA TCH(SMALL($A:$A,ROWS($1:1)),$A:$A,0)-2)) Copy C4 to E4. Select A4:E4, copy down to cover the max expected extent of source data in Sheet1. This returns all the source lines with dates equal to that input in B2, neatly packed at the top, as desired. Minimize/hide col A. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Jim" wrote: Following is a sheet where I enter data: 1 2 3 4 5 6 7 Date Acct # Scheduled Actual 101 111 325 11/4/2008 6744-08 11/4/2008 11/4/2008 11/5/2008 6744-09 11/4/2008 11/4/2008 1 2 2 11/5/2008 6744-10 11/4/2008 11/4/2008 1 2 1 11/6/2008 6744-11 11/4/2008 11/4/2008 2 2 1 11/7/2008 6744-12 11/4/2008 11/4/2008 1 8 1 I will have a second sheet where I will change the date and would like to match and pull over data based on the date: For example if I put in 11/5/2008 I would like to match the entries. but the bigger question is what formula is needed to read from row to row? Date 11/5/2008 Account number 101 325 30001 |
Thread Tools | |
Display Modes | |
|
|