Making selection and skipping rows which do not comply

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 !!
 
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 ..
 
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 said:
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 ..

---
Jaydubs said:
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 !!
Click to expand...
Click to expand...
 
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 said:
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 ..

---
Jaydubs said:
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 !!
Click to expand...
Click to expand...
 
Jaydubs said:
Thanx Max,
But unfortunately the entries in B always give me the entry of B2 in File2,
which is the header of that column.
Click to expand...

It works fine when I tested it here under the stated assumption
With File2 open simultaneously,
Click to expand...

Try pressing F9, does it now compute properly ?
Calc mode may inadvertently be set to: Manual
(Click Tools > Options > Calculation tab > Check Auto > OK)

---
 
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.
 
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(A5));$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 said:
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 said:
It works fine when I tested it here under the stated assumption

Try pressing F9, does it now compute properly ?
Calc mode may inadvertently be set to: Manual
(Click Tools > Options > Calculation tab > Check Auto > OK)

---
Click to expand...
Click to expand...
 
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 ..)
 
I can imagine you are loosing track here !

I am sorry for any inconvenience and do appreciate your help a lot.

What I am looking for a some sort of formula which does the following:
=IF([File2.xls]Sheet1!D3="",{SKIP A ROW},ROW())

Where {SKIP A ROW} is some kind of function. So each time Column D has a
blank entry, it should disregard that row. If this works out I would get the
following output of the formula:
1 1356
2 1357
3 1358
4 1359
7 1362
8 1363
10 1365

Rows 5 and 6 and 9 are blank, hence disregarded.

Does this make sense?

--
** Fool on the hill **


Max said:
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 ..)

---
Jaydubs said:
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.
Click to expand...
Click to expand...
 
Jaydubs said:
I can imagine you are loosing track here !
I am sorry for any inconvenience and do appreciate your help a lot.
What I am looking for...
Click to expand...

Believe I understood your intent earlier from your original post. Pl try the
revised set of formulas, and let me know how it went in reply here. I've
gotta go offline now for at least the next 6 hours, but I'll be back .. <g>

---
 
What I get when I copy your last suggestion is the following:

1 1356
2 1356
3 1356
4 1356
1356
1356
7 1356
8 1356
1356
10 1356
 
Ok, I got it now.... I used ROWS instead of ROW (in the dutch version that is).

Thankx for your help and extreme patience.

Great job !!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query within a Query - SQL in VBA 7
adding from one year to the next 1
Select rows equal to filter and highlight row 4
Copy rows to new sheet based cell reference, not value 2
Help with this challenging VBA coding 3
Application.Quit Run-Time Error 1004 6
Workbook_Open vs Auto_Open 4

Back
Top