Auto Flag from a list

[matts]

On the first sheet (1) I want to type a list of ID numbers into a sheet down the page. i.e.

12345
35678
67832
...

On a second sheet (2) I already have a mailing list, where there are thousands of people listed down the page i.e<pre>Column A Column B Column C
john Smith 12345 12 Long St
Stan Black 22345 1 Short way</pre>

I would like to run something on sheet (1) to locate all those ID numbers listed on this sheet and actually flag them in some way on sheet (2). ie in the case above the result may be<pre>Col A Column B Column C Column D
X john Smith 12345 12 Long St
Stan Black 22345 1 Short way</pre>Where a new column is inserted and an X shown beside those that match the list on Sheet(1).

I understand this is probably very difficult but I would appreciate any help. I fiddled with Loops but got totally confused in how to move between the two pages and increment the row I am searching from. Also, if the numbers on sheet (1) end then the loop should end.


Matt
This message was edited by Juan Pablo G. on 2003-02-13 22:43

 

[cathy01]

Thanks for such a wonderful forum.

I would like to extend this formula little more:
Original formula:
=IF(ISNUMBER(MATCH(C1,Sheet1!$A$1:$A$100,0)),"","X")

What I need in the new formula is instead of empty space "" when there is a match, I want to place the contents of column B of Sheet1.

For instance, if the match(C1,Sheet1!$A$1:$A$100,0) returns 4, I want B4 from sheet 1 instead of "".

Thank you.

 

[Aladin Akyurek]

Thanks for such a wonderful forum.

I would like to extend this formula little more:
Original formula:
=IF(ISNUMBER(MATCH(C1,Sheet1!$A$1:$A$100,0)),"","X")

What I need in the new formula is instead of empty space "" when there is a match, I want to place the contents of column B of Sheet1.

For instance, if the match(C1,Sheet1!$A$1:$A$100,0) returns 4, I want B4 from sheet 1 instead of "".

Thank you.

Would you provide a small sample along with the desired results?

 

[cathy01]

Sheet1:
ColumnA Column B
1 10-Oct
3 20-Oct
10 30-Oct

Sheet2:
ColumnA Column B
10-Oct 1
Not present 2
20-Oct 3
Not present 5
Not present 8
30-Oct 10

I would like to extend this formula
=IF(ISNUMBER(MATCH(B1,Sheet1!$A$1:$A$100,0)),"","Not present")
so that "" is replaced by the matching row in Column B of Sheet1.

Thanks.

 

[Aladin Akyurek]

Sheet1:
ColumnA Column B
1 10-Oct
3 20-Oct
10 30-Oct

Sheet2:
ColumnA Column B
10-Oct 1
Not present 2
20-Oct 3
Not present 5
Not present 8
30-Oct 10

I would like to extend this formula
=IF(ISNUMBER(MATCH(B1,Sheet1!$A$1:$A$100,0)),"","Not present")
so that "" is replaced by the matching row in Column B of Sheet1.

Thanks.

Sheet1

Book31
	A	B	C	D
1
2	1	10-Oct
3	3	20-Oct
4	10	30-Oct
5
6
Sheet1

Sheet2

Book31
	A	B	C	D
1
2	10/10/2004	1
3	NotPresent	2
4	10/20/2004	3
5	NotPresent	5
6	NotPresent	8
7	10/30/2004	10
8
Sheet2

A2, copied down:

=IF(ISNUMBER(MATCH(B2,Sheet1!A:A,0)),INDEX(Sheet1!B:B,MATCH(B2,Sheet1!A:A,0)),"Not Present")

Is this what you meant to have?

 

[cathy01]

Yes, it works. Thank you very much.