Need VBA TO LIST VALUES THAT CORRESPOND VALUES IN GHIJK

[estatefinds]

I need a VBA macro to place data to the right of the data in columns GHIJK, Please! For example as seen in attachment. the data in column s GHIJK are associated with values in column W. So i need a macro to look at the data in columns GHIJK and place the value that is found in column W. for example, the value 1 is found in column G11 and when i look to the right I see this value in column X within the thick box border; now to the left outside the thick black box border I see the number 1 this number will be placed to the right of the GHIJK into column M11 and so on, as you see in the example it will do this until there is no more data in the GHIJK.
Thank you!

p.s. I need the VBA macro to be able to do this as the data will change in the columns W to AB.

[Bob Phillips]

You can do it with an array formula

=MIN(IF(G11=$X$11:$AB$46,$W$11:$W$46))

[estatefinds]

Where on the Worksheet would I place this formula?

[estatefinds]

I placed it in M 11 and i got a value error

if at all possible can I have it in VBA?
Thank you

[estatefinds]

I need a VBA macro to place data to the right of the data in columns GHIJK, Please! For example as seen in attachment. the data in column s GHIJK are associated with values in column W. So i need a macro to look at the data in columns GHIJK and place the value that is found in column W. for example, the value 1 is found in column G11 and when i look to the right I see this value in column X within the thick box border; now to the left outside the thick black box border I see the number 1 this number will be placed to the right of the GHIJK into column M11 and so on in the MNOPQ, as you see in the example it will do this until there is no more data in the GHIJK.
Thank you!

p.s. I need the VBA macro to be able to do this as the data will change in the columns W to AB.

[PAB]

You can do it with an array formula

=MIN(IF(G11=$X$11:$AB$46,$W$11:$W$46))

Because it is an array formula, you need to enter it not with just pressing enter but by pressing Ctrl-Shift and enter.
Then copy the formula along to Q11 and then down.

I hope this helps!

[estatefinds]

ok so there might be a little confusion i did try itt but the results werent what i expected.
no problem here is a better description of what I am looking for so I have data in columns GHIJK the numbers that make up these combinations are found in X Y Z AA, AB. the numbers in the column W are where the these numbers are found or categorized. so the numbers 1,2,9,24,33 that will be found in the combinations will be associated with the number 1 as this is where these numbers are found. ( dont let the name of the file confuse what im looking for as this happends to be the name of the file I am using.
so what ever the combinations is the numbers have numbers associated with them which these are in column W.
So looking at the data in columns GHIJK for example 1 5 8 14 18 so the number 1 is found on column X11 and this is associated with the number in column W11 so this number from W11 will be placed in the column M11 which will be the number 1.
Then for the next number 5 in column H11 if found in the column X13 and is associated with the number 3 which is in the column W.
The number 8 which is found in column I11 is found in column X12 and is associated with the number 2.
then the 14 is assocaited with the number 4.
the number 18 is associated with the number 5.
so in a sense it is translating the data in
GHIJK the to the out put data of the associated number in column W.
If at all possible a VBA to run this as data will change often. Thank you!

[PAB]

Hi Dennis,

I am confused now, I have just used xld's formula and entered it as I described previously. Then I copied it along and then down.
The results it produces are indeed the results that you have stated in your last post.

When entering an array formula, the formula will have curly brackets at each end, like so...


{=MIN(IF(G11=$X$11:$AB$46,$W$11:$W$46))}

You need to enter it using Ctrl-Shift and enter as opposed to just enter. Does your formula have these?

[estatefinds]

ok so clicked into cell entered the formula with brackets and hit shift ctrl enter and coped it down and the formula was pasted all the way down and i could see the formula on the worksheet
I feel like I am doing something wrong

[estatefinds]

so the results need to go in the columns MNOPQ

[PAB]

ok so clicked into cell entered the formula with brackets and hit shift ctrl enter and coped it down and the formula was pasted all the way down and i could see the formula on the worksheet
I feel like I am doing something wrong

You don't include the brackets. Just copy xld's formula to the clipboard, click cell M11, then paste the formula in the address bar (next to the fx above the column labels), then press Ctrl-Shift and enter. Because it is an array formula, the brackets will automatically be inserted. Then copy this across and down as far as needed.


so the results need to go in the columns MNOPQ

That is correct.

I hope this helps!

[estatefinds]

ok this is what i get


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

[estatefinds]

ok i got it!!!
Thank you very much!!!!

[PAB]

You're welcome, I'm glad it works.
Thanks for the feedback.

[estatefinds]

No problem! Thanks again!