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

 

[Legacy 12630]

=IF(ISNA(VLOOKUP(Sheet1!A$1:A$100,C1,1,0)),"","X")

 

[Juan Pablo González]

On 2003-02-13 22:37, Ponsonby wrote:
=IF(ISNA(VLOOKUP(Sheet1!A$1:A$100,C1,1,0)),"","X")

I think that this formula is supposed to be:

=IF(ISNA(VLOOKUP(C1,Sheet1!A$1:A$100,1,0)),"","X")

another option is

=IF(COUNTIF(Sheet1!$A$1:$A$100,C1),"X","")

or

=IF(ISNUMBER(MATCH(C1,Sheet1!$A$1:$A$100,C1,0)),"X","")

or even just

=COUNTIF(Sheet1!$A$1:$A$100,C1)

with this custom formatting:

"X";;

 

[PaddyD]

On 2003-02-13 22:37, Ponsonby wrote:
=IF(ISNA(VLOOKUP(Sheet1!A$1:A$100,C1,1,0)),"","X")

Or, equivalently:

=IF(ISNUMBER(MATCH(C1,Sheet1!$A$1:$A$100,0)),"","X")

If anyone has fastexcel etc handy, I'd be interested to see the relative performance for match & vlookup for large ranges...

paddy

 

[Juan Pablo González]

Paddy, I think you got the "" and "X" mixed up... and I would like to see the COUNTIF formula in that test too...

 

[PaddyD]

End of the day, mind more focused on beer than truth conditions!

 

[matts]

Thankyou Ponsonby,Juan Pablo, and Paddy. All the options work great, I appreciate your time and effort.

Thanks Juan Pablo for formatting my original post so it was a bit clearer to read.

 

[Aladin Akyurek]

On 2003-02-13 23:01, PaddyD wrote:
End of the day, mind more focused on beer than truth conditions!

OK, now back to the truth conditions... What follows is a performance comparison of the formulas forwarded in the current thread.

The MainList consists of 20,000 (minus 1) unsorted numbers, while the CheckList counts 98 (Paddy did the workbook with data).

The following formulas, which check the existence of the Checklist numbers in the MainList, are compared:

In alphabetical order...

=IF(COUNTIF(MainList,B2),"On list","Not on list")

=IF(ISNUMBER(MATCH(B2,MainList,0)),"On list","Not on list")

=IF(ISNA(VLOOKUP(B2,MainList,1,0)),"Not on list","On list")

Here are the results of 5 runs for each formula, which are averaged...

all shoterversion.xls
	A	B	C	D	E	F	G	H
1	WorkSheetProfiles
2		CalcTime(Millisec)		MicroSecs
3	Sheetname	UsedRange	Sheet	perFormula
4	COUNTIF	1,482.97	1,461.14	14,909.55
5	MATCH	434.42	425.78	4,344.68
6	VLOOKUP	451.18	424.88	4,335.51
7
8
9	WorkBookSummary
10	Sheets	CalcTime(Millisec)		MicroSecs	Volatility	Book	Bytes
11	Count	ReCalc	FullCalc	perFormula	%	OvHead	perCell
12	COUNTIF	0.20	1,482.07	15,123.17	0.0%	20.93	23.88
13	MATCH	0.21	420.29	4,288.72	0.1%	3.51	23.47
14	VLOOKUP	0.23	442.62	4,516.54	0.1%	34.57	24.00
15
FastXL

 

[Juan Pablo González]

Ok, so lets stick with the ISNUMBER(MATCH()) combination...

Thanks for the tests Aladin.
_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2003-02-16 20:04

 

[PaddyD]

Thanks for that Aladin...

pretty close between vlookup & match, with match() just winning in terms of total book overheads, with countif() a poor third!

for those intersted in what the calculations are, see:

http://www.decisionmodels.com/fxluserf.htm