1

I'm finding it really difficult to understand how to prefix or suffix a set of words based on a what I have chosen in a 'Data Validation List'.

CLICK HERE TO SEE/DOWNLOAD AN EXAMPLE SPREADSHEET

I have a list of words in column A and a list of words in column B.

In Cell C1 i have a data validation list and the options are 'Suffix' or 'Prefix'

What i would like to happen in (Column D), is if i choose Suffix to place all words in column B after of all words in column A. And if I choose Prefix to place all words in column B before all words in column A.

FYI - I'm using Excel 2013 and also there can be any number of words in column A and B.

I've tried using concatenate formulas but populating a list of words based on what is selected from a data validation list is way beyond my knowledge.

SCREENSHOT OF EXAMPLE SPREADSHEET

So, In column D, if i chose Suffix then id want all combinations to show like this:

  • orange yogurt
  • orange ice cream
  • orange milkshake
  • orange drink
  • apple yogurt
  • apple ice cream
  • apple milkshake
  • apple drink
  • banana yogurt
  • banana ice cream
  • banana milkshake
  • banana drink

Suffix Example

In column D, if i chose Prefix then id want all combinations to show like this:

  • yogurt orange
  • ice cream orange
  • milkshake orange
  • drink orange
  • yogurt apple
  • ice cream apple
  • milkshake apple
  • drink apple
  • yogurt banana
  • ice cream banana
  • milkshake banana
  • drink banana

Prefix Example

Thanks in advance

  • Please edit your question to include sample data as 1/ nobody should have to download a potentially dangerous file from an unknown source and 2/ if the link changes or the file is removed then the question will be missing vital information. – DavidPostill Oct 6 '15 at 8:05
  • you don't have to download the spread sheet, i gave it as an option as a screenshot will not show my formulas. The link will not change or get removed unless i do that myself. But i have done as requested. – Jez Vander Brown Oct 6 '15 at 8:32
  • Thanks for your response datatoo. I tried that formula before i posted and it worked to a certain extent, but it doesn't give every option I require, it only combines the cells in a row depending on the choice in C1. Maybe i didn't explain the result I'd like well enough. I've extended my original question to show how id like the results to show, with a screenshot. – Jez Vander Brown Oct 7 '15 at 6:12
  • Your response to datatoo should be a comment to his answer, then he will get notified. I'm not sure he will see one posted as a question comment. – DavidPostill Oct 7 '15 at 7:08
0

You can test for the value of the selected validation choice and in Column D have a formula such as the following.

=IF(C2="Prefix",A2&" "&B2,B2& " "&A2)

if you want to make the suffix, prefix choice in a single cell such as C1 alter the formula to something like

=IF(C$1="Prefix",A2&" "&B2,B2& " "&A2)
  • The OP has a comment to you under his question. I don't think you will have been notified about it ... – DavidPostill Oct 7 '15 at 7:09
  • I guess the question is other than I understood it. It appears that it would require multiple similar formulas depending upon the content of both A and B. My approach would change if this is the case. Probably using a macro doing the type of same string concatentation depending upon the validation cell would make sense if that is the case. Would that work for you? – datatoo Oct 7 '15 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.