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

 

[Joe4]

Seems like this could be done pretty easily if you set a lookup table for all your substitutions, and then simply used a VLOOKUP formula.
See: VLOOKUP Function

 

[Johncobb]

Seems like this could be done pretty easily if you set a lookup table for all your substitutions, and then simply used a VLOOKUP formula.
See: VLOOKUP Function

Hi,
Thanks for your quick reply. I am not an expert in using Excel. I don't know about VLOOKUP formula. Can you give me instructions step by step how to do it or examples how to implement it?

 

[Joe4]

Hi,
Thanks for your quick reply. I am not an expert in using Excel. I don't know about VLOOKUP formula. Can you give me instructions step by step how to do it or examples how to implement it?

That is what I provided the link for. Have you looked at it?
Here is Microsoft's official article on the function, that describes it in detail and shows examples: VLOOKUP function - Microsoft Support

Try it yourself, and see how you do (that is the best way to learn, to try it yourself rather than have someone do it for you).
And if you run into any difficulty, post back here, and show us what you did, and we can help you fix it to get it working correctly.
Here is a little hint: You will only need two columns in your lookup table. The left column has the number your are looking for. The right column has the number you want to subsititute it with.

 

[Johncobb]

That is what I provided the link for. Have you looked at it?
Here is Microsoft's official article on the function, that describes it in detail and shows examples: VLOOKUP function - Microsoft Support

Try it yourself, and see how you do (that is the best way to learn, to try it yourself rather than have someone do it for you).
And if you run into any difficulty, post back here, and show us what you did, and we can help you fix it to get it working correctly.
Here is a little hint: You will only need two columns in your lookup table. The left column has the number your are looking for. The right column has the number you want to subsititute it with.

Thanks for quick reply again.
I looked at it, but I don't understand it. I don't know how to do it. Example table looks completely different to my table.
It is not easy to learn something new when a person is OLD
Can someone help me, please?

 

[Joe4]

Example table looks completely different to my table.

Don't tripped up by that. The concept is the same. And you only need two values in your lookup table.
You are going to create a two-column lookup table:
Column 1: the number you want to find
Column 2: the value you want to replace the value from column 1 with

So the lookup table you create will start like this (I am showing the first 5 rows):

So, then if you have some value in cell AP1, and you want to return the corresponding value in cell AQ1, you would put a formula like this in cell AQ1:
=VLOOKUP(AP1,lookup_range,2,0)
where is lookup_range is the full address of the lookup table you created.
If it was in the range A2:B6, like shown in my example, then you would use: A$2:B$6 as your lookup range.
So just put whatever the address of the actual lookup table you create in that spot, and surround the column and be sure to put the "$" in front of the row references, so it locks that reference down.
Then, just copy that formula form AQ1 to the rest of the cells in AQ where you need to return values.

Here is a video which demonstrates this:

Once again, don't get thrown off by the fact that their table has 4 columns. Only the first two matter. You can ignore the other two.
Just think of it as their "Order ID" column is like your "Number" column and "Product" is like your "Replacement" column.

 

[Johncobb]

Don't tripped up by that. The concept is the same. And you only need two values in your lookup table.
You are going to create a two-column lookup table:
Column 1: the number you want to find
Column 2: the value you want to replace the value from column 1 with

So the lookup table you create will start like this (I am showing the first 5 rows):
View attachment 84321

So, then if you have some value in cell AP1, and you want to return the corresponding value in cell AQ1, you would put a formula like this in cell AQ1:
=VLOOKUP(AP1,lookup_range,2,0)
where is lookup_range is the full address of the lookup table you created.
If it was in the range A2:B6, like shown in my example, then you would use: A$2:B$6 as your lookup range.
So just put whatever the address of the actual lookup table you create in that spot, and surround the column and be sure to put the "$" in front of the row references, so it locks that reference down.
Then, just copy that formula form AQ1 to the rest of the cells in AQ where you need to return values.

Here is a video which demonstrates this:

Once again, don't get thrown off by the fact that their table has 4 columns. Only the first two matter. You can ignore the other two.
Just think of it as their "Order ID" column is like your "Number" column and "Product" is like your "Replacement" column.

I tried your explanation. I does not work.
I have to clarify something again. I do not create the corresponding number in column AQ. Excel must do it automatically according to my table that I have provided in this post.
In cell AP1 is this formula: =SUM(COUNTIFS(A1:AN1,$AO$1:$AO$20)) therefore those numbers are generated by excel. They are not the same each time after they are populated (reshuffled) randomly.
Maybe for this a VB script must be implemented? I don't know. Please help. If you need more explanations from my side, please ask.
Thank you for your time and effort.

 

[Joe4]

Please post a sample of your data so we can see your data structure, formulas, and where everything exists on your sheet.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Johncobb]

Please post a sample of your data so we can see your data structure, formulas, and where everything exists on your sheet.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I am very sorry but your explanations are totally hopeless. I was using mrexcel before and I had received very good help, but now I am disappointed. Whenever I click on any link that you have provided I get nowhere. I don't understand anything and I think that it is irrelevant to what I want to achieve.
Is there anyone else who can help me???? Please don't be offended but I am not expert in excel therefore I am asking for help.
I am not willing to upload my samples of my data.

 

[Joe4]

I am very sorry but your explanations are totally hopeless. I was using mrexcel before and I had received very good help, but now I am disappointed. Whenever I click on any link that you have provided I get nowhere. I don't understand anything and I think that it is irrelevant to what I want to achieve.
Is there anyone else who can help me???? Please don't be offended but I am not expert in excel therefore I am asking for help.
I am not willing to upload my samples of my data.

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!