Show different value in next cell that is corresponding to the original value in previous cell (substituted value)

[Johncobb]

Hello Experts,

In my spreadsheet on the left I have 100 rows of numbers from A to AN (40 numbers in each row).
In column AO (AO1:AO20) are 20 numbers that are randomly generated.
If any of those 20 randomly numbers are matched in any row, they are highlighted.
In column AP is shown how many randomly generated numbers was matched in each row.
(you can view my previous post if you want to have an idea what was done so far)

Now I want to add something:
I want numbers that are in column AP to appear in column AQ but at the same time they must be substituted with a different value.
For example:
If in AP1 the number will come up as 9, I want this number to be represented in AQ1 as 0
If in AP2 the number will come up as 8, I want this number to be represented in AQ2 as 0
If in AP3 the number will come up as 7, I want this number to be represented in AQ3 as 1
If in AP4 the number will come up as 6, I want this number to be represented in AQ4 as 2
If in AP5 the number will come up as 5, I want this number to be represented in AQ5 as 7
If in AP6 the number will come up as 4, I want this number to be represented in AQ6 as 35
and so on for each row in this spreadsheet (200 rows)

Here are all substitutions:

Number 0 in column AP must correspond to value 250,000 in column AQ
Number 1 in column AP must correspond to value 25,000 in column AQ
Number 2 in column AP must correspond to value 2,200 in column AQ
Number 3 in column AP must correspond to value 200 in column AQ
Number 4 in column AP must correspond to value 35 in column AQ
Number 5 in column AP must correspond to value 7 in column AQ
Number 6 in column AP must correspond to value 2 in column AQ
Number 7 in column AP must correspond to value 1 in column AQ
Number 8 in column AP must correspond to value 0 in column AQ
Number 9 in column AP must correspond to value 0 in column AQ
Number 10 in column AP must correspond to value 0 in column AQ
Number 11 in column AP must correspond to value 0 in column AQ
Number 12 in column AP must correspond to value 0 in column AQ
Number 13 in column AP must correspond to value 1 in column AQ
Number 14 in column AP must correspond to value 2 in column AQ
Number 15 in column AP must correspond to value 7 in column AQ
Number 16 in column AP must correspond to value 35 in column AQ
Number 17 in column AP must correspond to value 200 in column AQ
Number 18 in column AP must correspond to value 2,200 in column AQ
Number 19 in column AP must correspond to value 25,000 in column AQ
Number 20 in column AP must correspond to value 250,000 in column AQ

If you need more explanations please contact me.
Waiting for your help Experts.
John

 

[Johncobb]

Do you wonder why no one else has responded? It is probably because you have made it very difficult for anyone to help you. You have not provided any sample data (and seem to be adamantly opposed to doing so). You should not expect someone to try to recreate your whole scenario. You want to make it easy for people to help you. You don't even have to post "real data". You can use dummy data. You can use the XL2BB tool to upload a sample of your data or you can upload a sample file to a file sharing site and provide a link to it here, if that is easier for you. The point is, you need to help us help you! There is a whole article on how to post good complete questions here: Guidelines

In post 6 I showed you EXACTLY how to set it up. It should not matter if your values in column AP are manually entered or if they are created by formulas. The formula I provided for column AQ only cares if the values being returned in column AP are numbers or not. I am not sure what you find confusing about the instructions in that reply - I tried to spell each part out for you in detail. You shouldn't need to be an expert in Excel to follow those instructions, just be willing to read it and follow the instructions.

I am getting the impression that maybe you really aren't interested in learning how to do this at all, but maybe you just want someone to do this for you (and you don't really care how it works). If that is the case, then fine, but you need to give us access to your data, in one of the two ways I described. We cannot build you a house if we don't have the materials!

Thanks for trying to help.
Someone from microsoft helped me.

 

[Joe4]

If you are posting the same question to multiple sites (known as "Cross Posting"), please note that most forums have a policy on that. Here is ours (found in the forum rules):

• Rule #13:​

  We prefer that members do not cross-post questions to other forums, but when this does occur members should do the following:
  • Post the details of your question on our forum. Do NOT simply post a link/re-direct to the question in another forum with no details posted here.
  • Make it clear that you have cross-posted and provide links to the cross-posts.
  • Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: Excelguru Help Site - A message to forum cross posters.

This rule/policy is really just about courtesy, so as to not waste anyone's time with duplicating efforts (suggesting things that may have been answered elsewhere) and answering questions that may have already been answered elsewhere. I once spent over an hour devising a complex solution for someone, and there reply was, "Oh, I already got an answer somewhere else!". When people are volunteering their time (none of us gets paid for this), hard feelings can ensue.

Just to be clear, we are not saying that you cannot post the same question in multiple places, just that you should mention you are doing so and provide links to those other questions so others can see what was already suggested and if the question has already been answered.

Thank you.