Today I had a spreadsheet that had a field populated by letters to represent missing fields (no telephone number? put a “T” in the column etc).
I could have ended up with up to 21 letters to represent the missing fields.
I needed to have a better representation so I could fill in the missing fields.
To accomplish this I had to do it in stages but I could do it all in one line:
=CONCATENATE(
IF(SUBSTITUTE(Sheet2.F2,"A","title_________ ",1)=Sheet2.F2,"","title_________ "),
IF(SUBSTITUTE(Sheet2.F2,"B","firstname_________ ",1)=Sheet2.F2,"","firstname_________ "),
IF(SUBSTITUTE(Sheet2.F2,"D","surname_________ ",1)=Sheet2.F2,"","surname_________ "),
IF(SUBSTITUTE(Sheet2.F2,"G","address2_________ ",1)=Sheet2.F2,"","address2_________ "),
IF(SUBSTITUTE(Sheet2.F2,"H","address3_________ ",1)=Sheet2.F2,"","address3_________ "),
IF(SUBSTITUTE(Sheet2.F2,"J","town_________ ",1)=Sheet2.F2,"","town_________ "),
IF(SUBSTITUTE(Sheet2.F2,"K","county_________ ",1)=Sheet2.F2,"","county_________ "),
IF(SUBSTITUTE(Sheet2.F2,"L","postcode_________ ",1)=Sheet2.F2,"","postcode_________ "),
IF(SUBSTITUTE(Sheet2.F2,"M","sex_________ ",1)=Sheet2.F2,"","sex_________ "),
IF(SUBSTITUTE(Sheet2.F2,"O","dob_________ ",1)=Sheet2.F2,"","dob_________ "),
IF(SUBSTITUTE(Sheet2.F2,"P","telephone_________ ",1)=Sheet2.F2,"","telephone_________ "),
IF(SUBSTITUTE(Sheet2.F2,"Q","mobile_________ ",1)=Sheet2.F2,"","mobile_________ "),
IF(SUBSTITUTE(Sheet2.F2,"R","email_________ ",1)=Sheet2.F2,"","email_________ "),
IF(SUBSTITUTE(Sheet2.F2,"S","service_________ ",1)=Sheet2.F2,"","service_________ "),
IF(SUBSTITUTE(Sheet2.F2,"T","RBL#_________ ",1)=Sheet2.F2,"","RBL#_________ "),
IF(SUBSTITUTE(Sheet2.F2,"U","join date_________ ",1)=Sheet2.F2,"","joindate_________ ")
)
Basically I need to check if the letter exists in the column and if so print the matching text with a line to write the missing information in.
To paste it into openoffice calc you need it all in one line:
=CONCATENATE(IF(SUBSTITUTE(Sheet2.F2,"A","title_________ ",1)=Sheet2.F2,"","title_________ "),IF(SUBSTITUTE(Sheet2.F2,"B","firstname_________ ",1)=Sheet2.F2,"","firstname_________ "),IF(SUBSTITUTE(Sheet2.F2,"D","surname_________ ",1)=Sheet2.F2,"","surname_________ "),IF(SUBSTITUTE(Sheet2.F2,"G","address2_________ ",1)=Sheet2.F2,"","address2_________ "),IF(SUBSTITUTE(Sheet2.F2,"H","address3_________ ",1)=Sheet2.F2,"","address3_________ "),IF(SUBSTITUTE(Sheet2.F2,"J","town_________ ",1)=Sheet2.F2,"","town_________ "),IF(SUBSTITUTE(Sheet2.F2,"K","county_________ ",1)=Sheet2.F2,"","county_________ "),IF(SUBSTITUTE(Sheet2.F2,"L","postcode_________ ",1)=Sheet2.F2,"","postcode_________ "),IF(SUBSTITUTE(Sheet2.F2,"M","sex_________ ",1)=Sheet2.F2,"","sex_________ "),IF(SUBSTITUTE(Sheet2.F2,"O","dob_________ ",1)=Sheet2.F2,"","dob_________ "),IF(SUBSTITUTE(Sheet2.F2,"P","telephone_________ ",1)=Sheet2.F2,"","telephone_________ "),IF(SUBSTITUTE(Sheet2.F2,"Q","mobile_________ ",1)=Sheet2.F2,"","mobile_________ "),IF(SUBSTITUTE(Sheet2.F2,"R","email_________ ",1)=Sheet2.F2,"","email_________ "),IF(SUBSTITUTE(Sheet2.F2,"S","service_________ ",1)=Sheet2.F2,"","service_________ "),IF(SUBSTITUTE(Sheet2.F2,"T","RBL#_________ ",1)=Sheet2.F2,"","RBL#_________ "),IF(SUBSTITUTE(Sheet2.F2,"U","join date_________ ",1)=Sheet2.F2,"","joindate_________ "))
I hope this helps somebody with the same general problem.
| Original content here is published under these license terms: | X |
|
| License Type: | Commercial | |
|
| License Summary: | You may read the original content in the context in which it is published (at this web address). You may make other uses of the content only with the written permission of the author on payment of a fee. |