Help Please!!!!

[rosariocristina]

i KNOW NOTHING ABOUT EXCEL I SPEND SO MUCH TIME TRYING TO FIGURE IT OUT
BY MYSELF BUT I HAVE NO IDEA WHERE TO START, I JUST WANTED TO KNOW HOW
TO DO THIS: IF ITS POSSIBLE TO DO THROUGH EXCEL... I HAVE A BUNCH OF
NUMBERS FROM 0000 TO 9999 AND I WANT TO KNOW WHICH NUMBERS ARE NOT IN
THE LIST OF NUMBERS I HAVE.

 

[Guest]

You can use the VLook up function for this.

First list all the numbers from 0000 to 9999 in one sheet (you can do this
by entering '0000 in one cell and '0001 in the next cell below.
thenhightlight both and drag from the right bottom corner untill you fill
upto 9999)

Then in another sheet enter all the numbers in your list and sort the list
into ascending order (highlight data, menu - Data - Sort - Ascending)

Enter the formula =VLOOKUP(A1,Sheet1!$A$1:$A$9999,1,0) besides the whole
list of numbers (Assuming that your list of numbers are entered in sheet 1
and the whole list of numbers are in another sheet starting at cell A1). You
can enter this into one cell and copy and paste to the rest at once. The
cells that give a result of #N/A are the numbers that are not in your list.
Then you can sort the whole list to see them together as we did earlier.

All the best.

Imran.

 

[broro183]

hi Rosariocristina,

Yes, it is possible.

To make this easier for me to explain, I'm going to assume your "bunc
of numbers" are listed down the page in column A & both columns B &
are empty. Also, please excuse my shorthand for keyboard combinations
I hope you can follow it.

*Create a list of numbers from 1 to 10000:
1) click in the white box in the top left of your screen that contain
the cell name (eg "A1", "B5" etc), type in B10000 and [enter].
2) Select all the cells in col B upto B10000, by pressing [ctrl + shif
+ uparrow].
3) type "=row()" (without the quotation marks), [ctrl + enter] (no
just enter, & this will fill all the selected cells).
*Visually identify which of these are in your data (in column A):
1)Select cell B1, [alt+o+d], change left box to "formula is", [tab]
type in "=COUNTIF($A:$A,$B1)>0" (without the quotation marks).
2) click format - patterns & choose a colour (eg green), [ok], [ok].
3) select cell B1, [ctrl+c],[ctrl+shift+downarrow], and [enter]

*Identify which of these are in your data (in column A) with words:
1) select cell C10000 by [ctrl + downarrow] and [rightarrow].
2) Select all the cells in col C upto C10000, by pressing [ctrl + shif
+ uparrow].
3) type "=if(COUNTIF($A:$A,$B10000)>0,"IN data list","NOT in dat
list")" (without the start & finish quotation marks), [ctrl + enter
(not just enter, & this will fill all the selected cells).

*Now you can quickly show all the numbers that are either in the lis
or not in the list by filtering column C.
1) [ctrl + home], [alt + d + f], click on the drop down box of colum
C & choose the option you want to see.


ps: please take off caps lock when writing any futur
questions/responses as it is harder to read.


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[Puppet_Sock]

i KNOW NOTHING ABOUT EXCEL I SPEND SO MUCH TIME TRYING TO FIGURE IT OUT
BY MYSELF BUT I HAVE NO IDEA WHERE TO START, I JUST WANTED TO KNOW HOW
TO DO THIS: IF ITS POSSIBLE TO DO THROUGH EXCEL... I HAVE A BUNCH OF
NUMBERS FROM 0000 TO 9999 AND I WANT TO KNOW WHICH NUMBERS ARE NOT IN
THE LIST OF NUMBERS I HAVE.

In addition to the direct hints people have given you, have you put
any thought into buying a helpful book or two on Excel? There are
many on the bookshelves, and you ought to be able to find one
that meets your level of computer experience.
Socks

 

[rosariocristina]

Thank you so much for the help, I was able to do it, and I'm deeply
sorry about the CAPS, oops, caps I mean, Im kiddin, but really, thank
you so much.

 

[broro183]

Lol

Thanks for the feedback, Pleased we could help.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...