| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making selection and skipping rows which do not comply
Dear excel(lent) users,
When I make a comparison of two sets of information from two different files I compare date from columns. For instance in Column A I have data from file one (copy-c/Copy-v) and from the second file I make a selection by using an if statement. If a certain cell in column D in File 2 is filled (with a date), then I want the entry from the cell of column B in the same row. I use =ALS('[File2]Sheet1'!$D3<"";'[File2]Sheet1'!$B3;1) Where ALS is dutch for IF. When I perfom this I get the following sequence (for instance): 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 The problem is that this gives me empty lines. How can I skip these empty lines so it will only give me the entries that have a date in Column D. I want the sequence to be like this: 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 Can this be automated? Thanks for your help !! -- ** Fool on the hill ** |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Making selection and skipping rows which do not comply
One way ..
Assume source data in File2.xls, in Sheet1, with data expected within rows 3 to 102 (100 rows max, say). The key col is col D with dates running in D3 down, and the target col to return (into File1.xls) is col B (as posted) With File2 open simultaneously, In File1.xls, in say, Sheet1: Put in A2: =IF(ISNUMBER([File2.xls]Sheet1!D3),ROW(),"") Put in B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX([File2.xls]Sheet1!B:B,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)+1)) [Note: the "+1" at the end of the formula is just an arithmetic adjustment since source data starts in row3 down, while we are extracting it here from row2 down] Then just select A2:B2, fill down to B101 to cover the max expected extent of 100 rows of source data in File2.xls (Hide away the criteria col A if desired) Col B will return the required results all neatly bunched at the top as desired (Change the commas in the formulas to semicolons to suit your continental version) Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jaydubs" wrote: Dear excel(lent) users, When I make a comparison of two sets of information from two different files I compare date from columns. For instance in Column A I have data from file one (copy-c/Copy-v) and from the second file I make a selection by using an if statement. If a certain cell in column D in File 2 is filled (with a date), then I want the entry from the cell of column B in the same row. I use =ALS('[File2]Sheet1'!$D3<"";'[File2]Sheet1'!$B3;1) Where ALS is dutch for IF. When I perfom this I get the following sequence (for instance): 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 The problem is that this gives me empty lines. How can I skip these empty lines so it will only give me the entries that have a date in Column D. I want the sequence to be like this: 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 Can this be automated? Thanks for your help !! -- ** Fool on the hill ** |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Making selection and skipping rows which do not comply
Thanx Max,
But unfortunately the entries in B always give me the entry of B2 in File2, which is the header of that column. -- ** Fool on the hill ** "Max" wrote: One way .. Assume source data in File2.xls, in Sheet1, with data expected within rows 3 to 102 (100 rows max, say). The key col is col D with dates running in D3 down, and the target col to return (into File1.xls) is col B (as posted) With File2 open simultaneously, In File1.xls, in say, Sheet1: Put in A2: =IF(ISNUMBER([File2.xls]Sheet1!D3),ROW(),"") Put in B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX([File2.xls]Sheet1!B:B,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)+1)) [Note: the "+1" at the end of the formula is just an arithmetic adjustment since source data starts in row3 down, while we are extracting it here from row2 down] Then just select A2:B2, fill down to B101 to cover the max expected extent of 100 rows of source data in File2.xls (Hide away the criteria col A if desired) Col B will return the required results all neatly bunched at the top as desired (Change the commas in the formulas to semicolons to suit your continental version) Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jaydubs" wrote: Dear excel(lent) users, When I make a comparison of two sets of information from two different files I compare date from columns. For instance in Column A I have data from file one (copy-c/Copy-v) and from the second file I make a selection by using an if statement. If a certain cell in column D in File 2 is filled (with a date), then I want the entry from the cell of column B in the same row. I use =ALS('[File2]Sheet1'!$D3<"";'[File2]Sheet1'!$B3;1) Where ALS is dutch for IF. When I perfom this I get the following sequence (for instance): 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 The problem is that this gives me empty lines. How can I skip these empty lines so it will only give me the entries that have a date in Column D. I want the sequence to be like this: 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 Can this be automated? Thanks for your help !! -- ** Fool on the hill ** |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Making selection and skipping rows which do not comply
"Jaydubs" wrote:
Thanx Max, But unfortunately the entries in B always give me the entry of B2 in File2, which is the header of that column. It works fine when I tested it here under the stated assumption With File2 open simultaneously, Try pressing F9, does it now compute properly ? Calc mode may inadvertently be set to: Manual (Click Tools Options Calculation tab Check Auto OK) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Making selection and skipping rows which do not comply
Yes computing is set on automatically from the start.
With your formula I get : Column A Column B 1 TD# 2 TD# 3 TD# 4 TD# TD# TD# 7 TD# 8 TD# TD# 10 TD# If I change the +1 into +2 I will get the first right digit, but like in the example above I get it ten times. When I take the calculation of my formula (the one I copied of u and made my changes [$B2:$B$200]) I get all the numbers including those I don't want to see. -- ** Fool on the hill ** "Max" wrote: "Jaydubs" wrote: Thanx Max, But unfortunately the entries in B always give me the entry of B2 in File2, which is the header of that column. It works fine when I tested it here under the stated assumption With File2 open simultaneously, Try pressing F9, does it now compute properly ? Calc mode may inadvertently be set to: Manual (Click Tools Options Calculation tab Check Auto OK) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Making selection and skipping rows which do not comply
If I now change the formula to
=IF(ROW(A1)COUNT($A5:$A$65534);"";INDEX('[Tracking en Tracing Bevindingen Proces va 20060522.xls]Data entry'!$B6:$B$65534;MATCH(SMALL($A5:$A$65534;ROW(A 5));$A5:$A$65534;1)+1)) I get the following: 1 1356 2 1357 3 1358 4 1359 1362 1362 7 1362 8 1363 1365 10 1365 Where it should be: 1 1356 2 1357 3 1358 4 1359 7 1362 8 1363 10 1365 -- ** Fool on the hill ** "Jaydubs" wrote: Yes computing is set on automatically from the start. With your formula I get : Column A Column B 1 TD# 2 TD# 3 TD# 4 TD# TD# TD# 7 TD# 8 TD# TD# 10 TD# If I change the +1 into +2 I will get the first right digit, but like in the example above I get it ten times. When I take the calculation of my formula (the one I copied of u and made my changes [$B2:$B$200]) I get all the numbers including those I don't want to see. -- ** Fool on the hill ** "Max" wrote: "Jaydubs" wrote: Thanx Max, But unfortunately the entries in B always give me the entry of B2 in File2, which is the header of that column. It works fine when I tested it here under the stated assumption With File2 open simultaneously, Try pressing F9, does it now compute properly ? Calc mode may inadvertently be set to: Manual (Click Tools Options Calculation tab Check Auto OK) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Making selection and skipping rows which do not comply
Afraid I'm losing the track a little here <g
Perhaps try these instead in File1.xls (with File2.xls open): Put in A2: =IF([File2.xls]Sheet1!D3="","",ROW()) Put in B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX([File2.xls]Sheet1!B:B,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)+1)) (No change to B2's formula: Better to stick to consistent entire col references throughout than to introduce the inconsistent fixed range for the index, albeit you mentioned that it worked in your response in the other branch ..) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jaydubs" wrote: Yes computing is set on automatically from the start. With your formula I get : Column A Column B 1 TD# 2 TD# 3 TD# 4 TD# TD# TD# 7 TD# 8 TD# TD# 10 TD# If I change the +1 into +2 I will get the first right digit, but like in the example above I get it ten times. When I take the calculation of my formula (the one I copied of u and made my changes [$B2:$B$200]) I get all the numbers including those I don't want to see. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Making selection and skipping rows which do not comply
I changed it into
=IF(ROW(A1)COUNT($A:$A),"",INDEX([File2.xls]Sheet1!$B2:$B$200,MATCH(SMALL($A:$A,ROW(A1)),$A:$A ,0)+1)) And this seems to work, thanx for your help ! -- ** Fool on the hill ** "Max" wrote: One way .. Assume source data in File2.xls, in Sheet1, with data expected within rows 3 to 102 (100 rows max, say). The key col is col D with dates running in D3 down, and the target col to return (into File1.xls) is col B (as posted) With File2 open simultaneously, In File1.xls, in say, Sheet1: Put in A2: =IF(ISNUMBER([File2.xls]Sheet1!D3),ROW(),"") Put in B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX([File2.xls]Sheet1!B:B,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)+1)) [Note: the "+1" at the end of the formula is just an arithmetic adjustment since source data starts in row3 down, while we are extracting it here from row2 down] Then just select A2:B2, fill down to B101 to cover the max expected extent of 100 rows of source data in File2.xls (Hide away the criteria col A if desired) Col B will return the required results all neatly bunched at the top as desired (Change the commas in the formulas to semicolons to suit your continental version) Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jaydubs" wrote: Dear excel(lent) users, When I make a comparison of two sets of information from two different files I compare date from columns. For instance in Column A I have data from file one (copy-c/Copy-v) and from the second file I make a selection by using an if statement. If a certain cell in column D in File 2 is filled (with a date), then I want the entry from the cell of column B in the same row. I use =ALS('[File2]Sheet1'!$D3<"";'[File2]Sheet1'!$B3;1) Where ALS is dutch for IF. When I perfom this I get the following sequence (for instance): 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 The problem is that this gives me empty lines. How can I skip these empty lines so it will only give me the entries that have a date in Column D. I want the sequence to be like this: 1356 1357 1358 1359 1362 1363 1365 1367 1368 1369 1370 Can this be automated? Thanks for your help !! -- ** Fool on the hill ** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
