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.
