Compare each Value from a Row with Values in a Range

[YTA5]

Hi everybody , I'm so glad to be here

I need to compare each cell in myRange1 , let it say myRange1 = A1:O1 ,
with each value from another Range ,let it say myRange2 = A5:AA10 .

myCell1 is in myRange1 and myCell2 is in myRange2 ;

every cell from myRange1 have 10 match values in myRange2 ;

I need to compare every cel from myRange1 with every cell from myRange2 , and , when it find a MATCH ,
to COPY a Range in the same column with myCell2 , but OFFSET ( 5, 0 ) ( offset from myCell2,), and then to PASTE it .
Here , the RANGE to be from OFFSET ( 5, 0 ) to OFFSET ( 10, 0 ) .
To PASTE it below , let/s say OFFSET (20,0) (offset from myCell2 ) , with TRANSPOSE ,
row by row ( I mean Row under Row to be the paste ).

I need to do the copy and paste for each one from TEN values found in myRng2 , and then to ggo to the next myCell1 , and so on .

Thank you so much .

[p45cal]

like so? (click on pic to see full size):
2023-08-18_230219.jpg

Best attach a workbook with a few example results.

[YTA5]

Yes yes yes , this is the idea , but , let see it a little bit closer :


Talking with data from your good picture ( and I thank you for the first for your time ) , macro is finding in Y5 the MATCH ,
and the macro do the right thing , copy data from Y11 from Y16 ( my mistake , let it do a row below to not interfere with the Range2 ) ,
BUT ,
before Y5 address , macro will find the MATCH in A5 address ;
IN my task and need is no matter
if macro search row by row in my Range2 or column by column ,
BUT ,
for a good understanding , let's assume that macro search row by row in Range2 :
macro find first MATCH in A5 ,
SO ,
it copy address from A11:A16 and paste it
IN
column A , ( so I need ) ; with transpose , like in your picture ; paste about 25 rows below my Cell1 for the first ,
and then ,
row under row ; let's assume , it is row 31 the first paste ;
in row 32 I need to be paste C11:C16 ; why , because in C6 the macro find the "second" ( or third , doesn't matter ) MATCH ;
then , D6 , D7 , D8 , because it find 3 matches , macro will perform 3 copy and paste of range
D11 : D16 ;

Disregard from my first post , to ,, << To PASTE it below , let/s say OFFSET (20,0) (offset from myCell2 ) >> " ;
paste with the start in Column A .
and so on ..


What is known in my DATA : is that in Range1 , I will have values from 1 to x , in ascending order ,
and in Range 2 will find shurely no more or less BUT 10 matches ; is no matter the order of copying
and paste - with transpose - this matches range , but the need is to copy and paste all of them ,
row under row , with start in Column A , without empty rows between them .


My apologies for lack of example here , in my first post ,
and , of course ,
sincerely thanks for your time and kindly helping me ,
and accomodateing here .

[Aussiebear]

YTA5, please attach a workbook with some sample data. To do so, select "Go Advanced", then "Manage Attachments". Then select "Choose File", and "Upload File" and finally "Post Reply". This will take away most of the ambiguity of the task at hand.

[YTA5]

Here is the try of before and after example .
My first greetings to/for Moderators .

[Aussiebear]

So, if I'm reading this correctly you would like some vba code to;
1. Check each cell in Range 1 (Rng1) against all the values in Range 2 (Rng2), on a row by row basis.
2. Each time the value is found, to then copy the vertical values in the Range 3 (Rng3), to a new location Range 4 (Rng4), and when pasting transpose the values to a horizontal format.

For the sake of clarity, Rng1 = Range ("A1:O1"), Rng2 = Range ("A5:AA10"), Rng3 = Range ("A14:AC16"), and Rng4 Starts at A25:C25 and works downwards.

From the data you have provided, there are approximately 42 instances where the values in Rng1, are located in Rng2, so we should expect Rng4 to occupy Range ("A25:C66").

For each cell in Rng1, if found in Rng2 we need to copy the corresponding values ( located directly under the found value) in Rng3, to Rng4

Example; A1 value "1", can be found in A5, J5, T5, C6, E9. Therefore we should expect then to copy in order of the finds, cells A14:A16 to A25:C25, J14:J16 to A26:C26, T14:T16 to A27:C27 etc.

Am I on the right track here?

I foresee an issue here in so far as Rng3 (3 rows x 29 columns), is a bigger area than Rng2 (6 Rows x 27 Columns). The data in columns AB & AC will never be copied, if we use your suggested method.

[YTA5]

Yes , this is the right way , but , in point 1 ) , it doesnt matter if the search is in a row by row basis or
column or column .

[Aussiebear]

Yes , this is the right way , but , in point 1 ) , it doesnt matter if the search is in a row by row basis or
column or column .

I refer you to post #3 where you said [QUOTE]for a good understanding , let's assume that macro search row by row in Range2 :/QUOTE]

[YTA5]

Indeed , I wrote that trying to be more concise with my level of english , but please disregard
that statement , for my task is not relevant if code will do the search in Rng2 in row by row or
column by column bases .

[ QUOTE ] I foresee an issue here in so far as Rng3 (3 rows x 29 columns), is a bigger area than Rng2 (6 Rows x 27 Columns). The data in columns AB & AC will never be copied, if we use your suggested method. :/ QUOTE ] .
THIS is another mistake done by me , done in the data provided in workbook , my apologise .

A clarify : Rng1 in my real world will have 1 row and 7000 columns ,
Rng2 have 20 rows and 10660 columns , and Rng3 have 3 rows and 10660 columns .
So, Rng2 and Rng3 is a match in the number of columns .

Please very much for help .

[p45cal]

In the attached, 3 macros blah1, blah2, blah3 in order of speed.
blah1 copies formatting too, the others don't.
3 buttons to run the macros around cell O26.
You will notice the speed difference with your larger ranges, with blah1 taking too long; you might give up on waiting for it and find yourself forcing Excel to close!

If, with your larger ranges, you get an 'out of memory' error on the Redim line of blah3, come back.

[YTA5]

Your code perform all what I need.

Indeed , code3 (blah3) go to an out of range error with my all of data ,
but , please , let me work with them this night to come back here with fresh impressions.

I'm so amazed of what can do ,,, For Each ..... Next ... " , but , in second two ,
you must to be very skillful to can handle with ranges , addresses , sintax and properties ...


I have to thank you so much , that you provided me this very smart code ,
and to thank you all for assisting me . I will face some very difficult tasks ,
with no way accomplishing situation without your so precious help .

[YTA5]

GOOD EVENING ,


I worked with all what you provided me . With code blah2 , in about 1 hour , my pretty pc sistem
of 2 x 1,7 ghz speed ended the calculation . this is an acceptable time , because this task must to
be done for only once . . I was so amased of speed of code blah3 , but I saw that it work only when
I have in Range A1:A7000 , takeing from them only 100 , in Range A1:A100 . The experience of
this speed was amasing .


[QUOTE]If, with your larger ranges, you get an 'out of memory' error on the Redim line of blah3, come back. :/QUOTE]
I have accomplished my needs with code blah2 , only if you want to find a way to get work the code blah3
with bigger size of data , truly speaking it might be very interesting . I hope , in the future time , to understand
your 3 code , blah3 .


I got to thank you because I learn from you and your code to work with " Resize " range property


I wish you all a beautifull weekend .