filling/replacing text 1

[ksbrace]

Hello,
I have excel sheets that I want to "mask" the data with the header column. However, there are blank spaces between the data. How can I just mask the existing data and not the blanks? Thanks in advance. I know how to use the Fill Down, but it fills in the blanks and I don't want that. Thanks in advance for any help or advice.

 

[SkipVought]

Hi,

"I have excel sheets that I want to "mask" the data with the header column"

Can you explain and illustrate exactly what you intend?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

And please explain why you have "blank spaces between the data."

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ksbrace]

so,
I have name, address, city, ssn, acct number, alt phone, ref name, etc..

I want to fill the ssn, acct number, phone, alt phone, ref name and some others. However, I do not want to fill in the blanks. For instance, not all have ssn, not all have alt phone or ref names. I want to leave the blanks BLANK, but fill in all data with AVAILABLE. I have 36 columns, so currently i'm sorting each column, then filling but i would like to think there is an easier way. Let me know

 

[SkipVought]

Fill from WHAT?

You have not explained ANYTHING about what you mean by "masking"???

Your post is very confusing!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ksbrace]

I want to fill any DATA cells with AVAILABLE and leave the blanks BLANK.

name ssn acctnumber phone altphone refname
TOM 12344 X3453s 713-3443 David
Jeff 44334 h435ss 713-1233
TOM 12344 X3453s 713-3443 713-3432 jack
Jeff 44334 h435ss 713-1233
TOM 12344 X3453s 713-3443 BILL
Jeff 44334 h435ss 713-1233

So, I want to "MASK" all DATA in ssn, phones, altphones, refnames with AVAILABLE but leave the blank cells in those columns blank. This shows the buyer how much data is actually avaialble and does not misrepresent.

name ssn acctnum phone altphone ref
TOM ssn X3453s phone ref
TOM ssn X3453s phone ref
TOM ssn X3453s altphone ref
Jeff ssn h435ss altphone
Jeff ssn h435ss phone
Jeff ssn h435ss phone


But i have 10,000+ rows and 50 columns and 20 need "masked" and I do this on a regular basis.

 

[SkipVought]

Are you referring to a lookup function that returns a value if there is one, or blank if none?

This only works if there is ONE ROW in your source lookup table for each key value. You seem to have multiple rows if I understand what you're trying to do.

Where is your lookup table?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ksbrace]

I'm just trying to hide the actual data and say it's AVAILABLE or replace/mask the data with the header column(example above). I just make a copy of the open/original file and then hide the actual data to show the potential buyer the portfolio. but I don't want the blank fields to get filled in with AVAILABLE if there isn't actually data there. Not referring to vlookup or match. Just looking to fill the actual data in certain columns and leave the blank cells blank.

 

[SkipVought]

You have to use a lookup formula. If it returns a value, the return the column heading, else ""... Something Like
[tt]
=if(index([return range],match([lookup value],[lookup range],0),1)<>0,[heading reference],"")
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ksbrace]

let me try and play around with that. Thanks!

 

[SkipVought]

You might try this alternatively
[tt]
=if(len(index([return range],match([lookup value],[lookup range],0),1))>0,[heading reference],"")
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]