Welcome Guest
You last visited December 7, 2016, 9:19 am
All times shown are
Eastern Time (GMT-5:00)

# Excel Help Again

Topic closed. 8 replies. Last post 6 years ago by Sage.

 Page 1 of 1
Dunwoody Georgia
United States
Member #747
September 29, 2002
659 Posts
Offline
 Posted: February 27, 2011, 5:19 pm - IP Logged

Good afternoon Lottery Post Family.  Last year I posted if someone could help me with an excel formula.  I wanted to take digits 0 through 9 and I want to put 5 digits in  cell A and have the formula show me the other 5 digits that was not in cell A show up in another cell like cell E or something?  I had a version with the macro and I would like a version without using macros if it's possible.  Thank you so much!

It's Never Too Late To Be The Person You Could Of Been!

The Ville, FL
United States
Member #95879
August 19, 2010
1708 Posts
Offline
 Posted: February 27, 2011, 6:10 pm - IP Logged

I need clarification.

Do you want for example cells A1-A10 to be :

A1: 0

A2: 1

A3: 2

A4: 3

A5: 4

A6: 5

A7: 6

A8: 7

And then in cell B1 show the #'s that are not  in cells A1-A10?  such as 8 & 9?

Dunwoody Georgia
United States
Member #747
September 29, 2002
659 Posts
Offline
 Posted: February 27, 2011, 6:13 pm - IP Logged

I need clarification.

Do you want for example cells A1-A10 to be :

A1: 0

A2: 1

A3: 2

A4: 3

A5: 4

A6: 5

A7: 6

A8: 7

And then in cell B1 show the #'s that are not  in cells A1-A10?  such as 8 & 9?

Thanks so much!  No I want all 5 digits to be in 1 cell 01234 in cell A or any cell and then show 56789 in another cell like cell E or something!  Thanks for a prompt response!

It's Never Too Late To Be The Person You Could Of Been!

The Ville, FL
United States
Member #95879
August 19, 2010
1708 Posts
Offline
 Posted: February 27, 2011, 6:51 pm - IP Logged

I know of one way it will work but you have to use "conditional formatting".

Also the numbers have to be entered into cells like this:

For example in Column A1 put this:

1-2-3-4-5

2-3-4-5-6

0-3-2-4-5

1-2-3-4-5

etc etc

Then in column B you would have the #'s listed like this:

0

1

2

3

4

5

6

7

8

9

If you use the forumula I can give you, it will hightlight all of the #s in Column B that are not in column A like this:

The result would look like this:

See how 7,8, & 9 are not in column A? They are highlighted because of the conditional formatting & formula I used.

Let me know if this will work. If so I will explain further

Dunwoody Georgia
United States
Member #747
September 29, 2002
659 Posts
Offline
 Posted: February 27, 2011, 7:20 pm - IP Logged

I know of one way it will work but you have to use "conditional formatting".

Also the numbers have to be entered into cells like this:

For example in Column A1 put this:

1-2-3-4-5

2-3-4-5-6

0-3-2-4-5

1-2-3-4-5

etc etc

Then in column B you would have the #'s listed like this:

0

1

2

3

4

5

6

7

8

9

If you use the forumula I can give you, it will hightlight all of the #s in Column B that are not in column A like this:

The result would look like this:

See how 7,8, & 9 are not in column A? They are highlighted because of the conditional formatting & formula I used.

Let me know if this will work. If so I will explain further

I appreciate your help.  Yes this is exactly what I need.  Now is there a way to take the hightlighted numbers and put them in their own cell like cell C?  Also can you add the formula that hightlights them like this.  That is so awesome and I appreciate all your help!   Also I need this done without a Macro.  I have a macro version that goes crazy when I try to compile it in Doneex.

It's Never Too Late To Be The Person You Could Of Been!

The Ville, FL
United States
Member #95879
August 19, 2010
1708 Posts
Offline
 Posted: February 27, 2011, 8:24 pm - IP Logged

Here is the formula:

=COUNT(SEARCH("-"&B1&"-","-"&A\$1:A\$10&"-"))=0

You have to set up the excel sheet first by adding the #'s in the specified column. In the example I used column A. Then in the next column you have to put the range of numbers you want. In the example I used 0-9.

Once you do that highlight the #s in column B and then click on "Format" in Excel, and then "Conditional Formatting". Once you are there, under "Condition 1" select "Formula is" and type that formula in. Once you do that, in the same box click on format and add the formatting you want. In the example I provided, I just made the selections with a yellow background. You can customize it anyway you want.

Also you may have to change the formula depending on which column/rows you are using. If you are using column A & B then the formula I provided will work

I hope I explained it clearly. If you have any questions let me know.

Not sure about having the results entered automatically into a different cell. Im sure its possible I just havent had a chance to work on it yet

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: February 27, 2011, 9:13 pm - IP Logged

Good afternoon Lottery Post Family.  Last year I posted if someone could help me with an excel formula.  I wanted to take digits 0 through 9 and I want to put 5 digits in  cell A and have the formula show me the other 5 digits that was not in cell A show up in another cell like cell E or something?  I had a version with the macro and I would like a version without using macros if it's possible.  Thank you so much!

This is a method using user defined cell ranges.

http://www.box.net/shared/pb82nb5lmr

Enter your 5 digit number in cell A1 (ascending order) and the missing 5 numbers will be in cell b1.

The Ville, FL
United States
Member #95879
August 19, 2010
1708 Posts
Offline
 Posted: February 27, 2011, 9:30 pm - IP Logged

This is a method using user defined cell ranges.

http://www.box.net/shared/pb82nb5lmr

Enter your 5 digit number in cell A1 (ascending order) and the missing 5 numbers will be in cell b1.

There ya go Sage, what winsum has posted is exactly what you are wanting.

Dunwoody Georgia
United States
Member #747
September 29, 2002
659 Posts
Offline
 Posted: February 28, 2011, 12:09 am - IP Logged

This is a method using user defined cell ranges.

http://www.box.net/shared/pb82nb5lmr

Enter your 5 digit number in cell A1 (ascending order) and the missing 5 numbers will be in cell b1.

Lol Thank you so much!  Both of you guys for helping me!  It's so appreciated!

It's Never Too Late To Be The Person You Could Of Been!

 Page 1 of 1