How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1260
Experience:  20 years experience providing remote computer support
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Can you tell me if this is possible before I would pay? TY: I

Resolved Question:

Can you tell me if this is possible before I would pay? TY:

I want to highlight certain values (numbers) in 2 columns in a spreadsheet.

I was given a spreadsheet with text in Column F that contains paragraphs with both words and numbers. The numbers I'm interested in are 5-digit healthcare codes. Most are numbers only, but some have an alpha character, e.g., 0017T. I am tasked with proofing this 4,800 line spreadsheet to make certain that all the 5-digit healthcare codes (not the explanatory text) in Column F cells also appear in the cells in Column I (they are separated by a comma.) If some are missing, I need to add them.

Both Columns are formatted as 'text.'

I must look at every line to compare because I also need to make certain the codes in Column F are valid. That's why I just want to see them all highlighted (including the codes in Column I). Then I can scroll down and do a visual comparison and fix or tag any codes in Column F that are wrong or questionable, and add any codes to Column I that are missing from the list.

Does this explanation make sense? TY
Submitted: 8 months ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 8 months ago.

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

Can you give me a couple of examples of what this text would look like

Customer:

Yes... I'll get some text copied and paste it here in a sec.

The-PC-Guy :

or I could have you send the sheet if that would be easier

Customer:

Here is an example of the text in Column F:

Customer:

nherent component of code 50200, <i>Renal biopsy; percutaneous, by trocar or needle</i>.</p><p>In the Urodynamics subsection (codes 51725-51798), the structure of this series has been changed. The following four codes were either revised or added:</p><p>?<b>51726</b> Complex cystometrogram (ie, calibrated electronic equipment);</p><p><b>51727 </b> with urethral pressure profile studies (ie, urethral closure pressure profile), any technique</p><p><b>51728</b> with voiding pressure studies (ie, bladder voiding profile), any technique</p><p><b>51729</b> with voiding pressure studies (ie, bladder voiding pressure) and urethral pressure profile studies (ie, urethral closure pressure profile), any technique</p><p>CPT code 51726 is used to report complex cystometrogram. Codes 51727-51729 are indented under the parent code 51726. To accommodate these additions, codes 51772 and 51795 were deleted. In code 51729, the service is a combination of the services described in codes 51727 and 51728. Since these procedures include the cystometrogram, it would not be appropriate to report either code 51727 or code 51728, in addition to code 51729.

Customer:

You can see the 5 digit codes. If they were all highlighted, they sure would be easier for me to spot and then go about my work to verify them.

The-PC-Guy :

so there are multiple codes within a single cell

Customer:

In column I, the same 5 digit codes are supposed to have been copied. But I have to proof that, in addition to verifying the codes in Column F

The-PC-Guy :

ok, is all of that text within a single cell?

Customer:

Yes, there are multiple codes within a single cell. Some are single codes, some are a range, e.g., 51727-51729. But if both the codes are highlighted, I can easily see the range and work with it.

The-PC-Guy :

well if they were all numbers, it certainly would be possible to to separate all alphanumberic from numbers in a cell

Customer:

I would like the codes that appear in Column I to be highlighted as well, so I can spot them more easily as I move through the spreadsheet.

The-PC-Guy :

but it is not possible to hilight text within a cell

The-PC-Guy :

so its either hilight the whole cell or not

The-PC-Guy :

what is possible is to search a cell for content

The-PC-Guy :

inotherwords we can ask excel if the number 12345

The-PC-Guy :

is somewhere in the cell

Customer:

Oh... do you mean it is not possible to highlight just numbers in a cell?

Customer:

I mean just highlight 5-digit numbers?

The-PC-Guy :

ok it is possible to change the font color

The-PC-Guy :

of all numbers in a cell with VBA

The-PC-Guy :

but not the background

The-PC-Guy :

so for example

The-PC-Guy :

you could have all numbers in red

The-PC-Guy :

and all letters in black

The-PC-Guy :

or something like that

The-PC-Guy :

a macro would have to be written to go through all rows and do this

Customer:

That would work if we could just have all 5-digit numbers in a different color text. There are too many other numbers like in dates and things that would muck it up.

The-PC-Guy :

it would be complicated

The-PC-Guy :

but it may be possible to count the number of numbers in a row

Customer:

Does a macro work on an entire spreadsheet at one time? Or would I have to apply the macro on each individual line?

The-PC-Guy :

the macro would run through all rows in the sheet

The-PC-Guy :

in specified column

Customer:

I'm not certain I understand "it may be possible to count the number of numbers in a row" .... do you mean apply the macro to change text color only for numbers with a count of 5 ?

The-PC-Guy :

something like that

The-PC-Guy :

so i would check is it

The-PC-Guy :

1234a

The-PC-Guy :

or 12345

The-PC-Guy :

or 123

The-PC-Guy :

or 1ab3---4

The-PC-Guy :

or whatever

The-PC-Guy :

obviously only the 12345 would be changed to red

Customer:

and when you show "12345" that doesn't mean I would have to run the macro for every code possible. It means that it would change the text color for any series of numbers that has 5 digits?

The-PC-Guy :

yes

The-PC-Guy :

something like that

Customer:

Oh... "1234a" would accommodate an alpha-numeric code 0017T, and "12345" would accommodate just a 5 number code? Would that run in the same macro?

The-PC-Guy :

the 4 digit and alphanumerics might casuse a problem

Customer:

How does this work? You would give me the macro and tell me how to run it in my spreadsheet?

The-PC-Guy :

can you send me your sheet?

The-PC-Guy :

it would be easier just to write and test it on your data

Customer:

Can I just send a limited number of lines rather than the entire spreadsheet? My client paid for the content and I don't want to distribute the whole thing.

The-PC-Guy :

i wouldn't distribute it

The-PC-Guy :

but yes

The-PC-Guy :

you can send me a few lines

The-PC-Guy :

then copy and paste

The-PC-Guy :

your data back in

The-PC-Guy :

you do realize it wont actually be higlighted, just the font color changed

Customer:

How do I send?

The-PC-Guy :

click the paperclip icon at the top of the chat window

The-PC-Guy :

where you type

The-PC-Guy :

says image

The-PC-Guy :

but does excel files

Customer:

Yes... I understand the font color only will be changed. I would like dark green if possible, rather than red. Because the ones I have to identify as not good codes I want to change text to red.

The-PC-Guy :

not following

The-PC-Guy :

but just put whatever color you want in your example file

Customer:

Ok... doing example file now.

The-PC-Guy :

ill wait

Customer:

Did this work? I didn't use red, I used the color to the left of red in the Standard Colors choices.

The-PC-Guy :

ok

The-PC-Guy :

let me check it

Customer:

In the sample, I only changed the text color for one of the codes in that cell just for illustration. But I would want all 5 digit codes to have text color changed. If we can't get the codes with the alphanumeric, so be it

Customer:

So text will change for both Columns F and I

The-PC-Guy :

so column I should be all that red color

The-PC-Guy :

yes/no?

Customer:

Yes. Each number in Col I is separated by a comma... don't need the comma in a different color.

The-PC-Guy :

well the comma doesn't matter

The-PC-Guy :

i mean you could just do column I yourself

The-PC-Guy :

let me get the macro to do the F column

Customer:

Good point! I guess I was originally thinking maybe there was a way to show any codes that got into Col I that were not in Col F but I don't think that happened.

Customer:

it's mostly that some of the codes in Col F did NOT get to Col I

The-PC-Guy :

well, maybe it would be easier to just populate column I with any of the 5 digit number sequences that are in coloumn F

Customer:

I didn't think the macro could actually also copy codes to Col F that weren't already there, right?

The-PC-Guy :

if I am chaning the colors, I could just as easily copy them to another column

The-PC-Guy :

at that point

The-PC-Guy :

the only problem with that might be the ranges

Customer:

Oh, but the issue is that I have to report back ONLY the rows that DID NOT have all the codes in Col I.

The-PC-Guy :

well I can count how many comas are in coloumn I

The-PC-Guy :

and how many of these 5 digit sets are in F

Customer:

oh right.. the ranges. If a range didn't make it to Col I, then I have to go elsewhere and verify all the codes in that range and add them in one at a time

Customer:

You mean ranges?

The-PC-Guy :

lets just stick with the coloring for now then

Customer:

Ok

The-PC-Guy :

we can apply other macros later once you get your data straitgthened out

Customer:

Ok

The-PC-Guy :

so the way this site works, is that once I provide this solution

The-PC-Guy :

and we complete this transaction

The-PC-Guy :

then you will have to ask me additional questions for additional functionality

The-PC-Guy :

inotherwords

The-PC-Guy :

i give you the basic macro

The-PC-Guy :

once that works

The-PC-Guy :

you can come back to me for an addtional question and ask to add other functionality

The-PC-Guy :

or do a different macro

Customer:

Ok... so will you also tell me how to "insert" the macro? Is that part of this first round? I think I've done them before, but if it's straightforward, I can figure it out.

The-PC-Guy :

yes

The-PC-Guy :

i will help you insert it

Customer:

So I understand that you will give me a macro for this first functionalty - to change font color of 5 digit codes. Then I I want to do anything else, that starts a new ticket so to speak...

The-PC-Guy :

my thought would be that you would use this sheet, then just copy your data from your other sheet and paste it in

The-PC-Guy :

i think that would make it easier for you

The-PC-Guy :

and yes to the above

Customer:

Ahhhh... good thinking!

Customer:

I meant "Then if I want to do anything else." I typed "Then I I want to do anything else..."

Customer:

But we are on same page.

The-PC-Guy :

i think so

Customer:

I pay for this first macro and other requests for additional functionality are extra.

The-PC-Guy :

BINGO

Customer:

:]

The-PC-Guy :

ok, give me a little while to work on this one

Customer:

ok... I have to make a phone call, but I will leave this page on my screen.

The-PC-Guy :

ok

The-PC-Guy :

Briliant- worked for the first cell

The-PC-Guy :

now lets see if I can apply it to all the rows

Customer:

Holding breath.

Customer:

Have to make 2nd phone call.

Customer:

I'm back.

Customer:

Just so you know I didn't go anywhere.

The-PC-Guy :

okay squashing a few bugs

Customer:

Squash away.

The-PC-Guy :

for some reason I am getting false positives and I can't firgure out why

Customer:

are false positives text that changed color but are not numbers?

The-PC-Guy :

yea

The-PC-Guy :

im just trying to correct that

Customer:

a lot of it? Because if just a little, I can still see the numbers easier.

Customer:

It's just been so hard to proof trying to find the numbers amid all that html code stuff.

The-PC-Guy :

give me a minute I have an idea

Customer:

ok.

The-PC-Guy :

looks good not

The-PC-Guy :

now

The-PC-Guy :

just my stupidity

The-PC-Guy :

you see I was resetting the counter after it reached 5

The-PC-Guy :

by was not just counting consecutive numbers

The-PC-Guy :

but something like

The-PC-Guy :

1,5

The-PC-Guy :

was increasing the counter by 2

Customer:

no stupidity if you figured that out.

The-PC-Guy :

do you know how to run a macro

The-PC-Guy :

or do I need to put a button somewhere for you to run it?

Customer:

I did one once a few months ago.

The-PC-Guy :

ok

The-PC-Guy :

so this macro will determine how many rows you have

Customer:

Is it a matter of me launching the macro thingy and pasting your code?

The-PC-Guy :

you dont need to paste anything

The-PC-Guy :

just enable the macro

The-PC-Guy :

then copy and paste your data in

The-PC-Guy :

to this file

Customer:

oh... ok

The-PC-Guy :

its easier i think

Customer:

why do I care that it will determine how many rows I have?

Customer:

Oh... it will go out and look to see how many rows and then run for all of them?

The-PC-Guy :

yes

The-PC-Guy :

whehter you have 70

The-PC-Guy :

or 7000

The-PC-Guy :

it doesnt care

Customer:

ok

The-PC-Guy :

here is the file

The-PC-Guy :

let me know if you have any problems questions or concerns

The-PC-Guy :

To pay, you would rate with the smileys beneath this chat

The-PC-Guy :

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Customer:

So, I go to Developer tab, click Macros and a list of one pops up, then I click run?

The-PC-Guy :

yes

The-PC-Guy :

that works

The-PC-Guy :

its called

The-PC-Guy :

find_nums

The-PC-Guy :

should only be 1 in the file

Customer:

ok.

Customer:

Do I test it before I choose the smiley?

The-PC-Guy :

remember you have to press the yellow enable macros button when you first open the file

The-PC-Guy :

yes

The-PC-Guy :

test it first please

The-PC-Guy :

so I can correct any issue

Customer:

I tried to download it and a box popped up that says

Customer:

"Excel cannot complete this task with available resources. Choose less data or close other applications. Continue without Undo?"

Customer:

So do I have to close a bunch of stuff or restart my PC?

The-PC-Guy :

thats wierd

The-PC-Guy :

di you actually open the file

The-PC-Guy :

and you got that message?

The-PC-Guy :

or did that happen when you tried to run the macro

Customer:

When I clicked on your link, it led me to a web page and an option to "download" and that's what I clicked.

Customer:

Then I got the box.

The-PC-Guy :

try saving the file to your hard drive first

The-PC-Guy :

then run it from there

The-PC-Guy :

your computer may be trying to run it off line for some reason

The-PC-Guy :

you should have the option to save

The-PC-Guy :

and not open

The-PC-Guy :

when you click the download link

Customer:

I did save file and then went to downloads and clicked on it, but got the same box.

The-PC-Guy :

you may have to reboot.

The-PC-Guy :

bookmark this page

The-PC-Guy :

so you can get back to me

Customer:

ok. Gosh, what if it doesn't go back to you?

The-PC-Guy :

did you give your email?

The-PC-Guy :

when you asked the question

The-PC-Guy :

if so this site sent out some emails

Customer:

yes

The-PC-Guy :

with links

The-PC-Guy :

to the question

The-PC-Guy :

but you can bookmark this url too

The-PC-Guy :

http://www.justanswer.com/microsoft-office/84ei5-tell-possible-pay-ty-i.htm

Customer:

I see it... I can go to email and click on View Your Answer and I'll get back to you if bookmark doesn't work?

The-PC-Guy :

yes, I would assume that would work

The-PC-Guy :

I will be around

The-PC-Guy :

if you need me further

The-PC-Guy :

You would have to come back to rate and pay me anyway

Customer:

I just closed a bunch of stuff and will try to open before I reboot

The-PC-Guy :

okay

Customer:

That's why I want to make sure I can get back to you

The-PC-Guy :

same message?

Customer:

yes... but I had word and acrobat pro and other apps open so I just got everything saved and closed out and now trying again

Customer:

Going to have to reboot. I've got the link



http://www.justanswer.com/microsoft-office/84ei5-tell-possible-pay-ty-i.htm


Customer:

Be right back

The-PC-Guy :

okay

Customer:

I rebooted... and got this back up first. Now I'm going to try and open file.

The-PC-Guy :

well let me know when your ready

Customer:

SLICK.

Customer:

It worked.

Customer:

I'm smiley clicking now.

The-PC-Guy :

okay

The-PC-Guy :

you know how to get back to me if you need me again

Customer:

Thank you so so much. Yes. I've got the contact info. It's great to know who to go to!

The-PC-Guy :

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Customer:

PC Guy only it is.

The-PC-Guy :

thats me

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1260
Experience: 20 years experience providing remote computer support
The-PC-Guy and 5 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional