List Comparison
 

Three questions

1. I have a list of about 19,000 lines long, one column. The numbers all
start with a w or a c and are followed by 5 digits. I need to get the w or c
off of this column.

2. This coilumn has a lot of repeats in it. How do I get this doen to
unique values only?

3. I need to compare the items in this column to another column to make
sure the numbers are there. How do I verify, for instance, whether or not A1
is in B:B?

List Comparison
 

1. If your data is in column A, then in B1 enter:

=RIGHT(A1,5)

and copy down

You can later copy column B and paste/special/values back onto column A.


2 The easiest way to get a column of uniques is to use:

Data Filter Advanced Filter with the unique records only option

3. To test if A1 is in column B, use:

Format Condtional Formattiong... Formula is =COUNTIF(B:B,A1)0 with a
distinctive background color
Copy this format down the column
--
Gary''s Student - gsnu200817


"Jeremy" wrote:

Three questions

1. I have a list of about 19,000 lines long, one column. The numbers all
start with a w or a c and are followed by 5 digits. I need to get the w or c
off of this column.

2. This coilumn has a lot of repeats in it. How do I get this doen to
unique values only?

3. I need to compare the items in this column to another column to make
sure the numbers are there. How do I verify, for instance, whether or not A1
is in B:B?

List Comparison
 

1. I have a list of about 19,000 lines long, one column. The numbers all
start with a w or a c and are followed by 5 digits. I need to get the w or c
off of this column.

Assuming data in A2 down,
Place this in B2:
=IF(A2="","",SUBSTITUTE(SUBSTITUTE(A2,"w",""),"c", "")+0)
Copy down. The "digits" results will be returned as real numbers. If you
want it retained as text numbers, remove the "+0" bit from the expression.

You could then freeze the values in col B with an "in-place" copy n paste
special as values. Then delete col A.

P/s: Stick to 1* query per posting. Make it attractive for responders to
respond.
*but you can always put in as many postings as you have queries
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---